VB to Excel using ADO to run a subroutine in Excel??

[WilliamMathias]

Hi.

The following code will not work?

When I execute the command I get run time error

-2147467259 (80004005): Cannot execute a select query


Can I run an excel subroutine through ADO?

I've previously used OLE automation to do this but I've found it very troublesome.

Thaks in advance

Public Sub main()

Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command

If OpenConnection() Then

Set objCmd = New ADODB.Command

With objCmd

.ActiveConnection = objConn
.CommandType = adCmdStoredProc
.CommandText = "DeleteRates"
.Execute 'This is where it falls over

End With

Set objCmd = Nothing
objConn.Close
Set objConn = Nothing

End If

End Sub




DeleteRates is within the Excel File and looks like this:

Public Sub DeleteRates()

Dim objWorksheet As Worksheet

For Each objWorksheet In Me.Worksheets
objWorksheet.Range("A2360&quot.Clear
Next

Set objWorksheet = Nothing

End Sub


This is how I open the connection:

Public Function OpenConnection() As Boolean

On Error GoTo OpenConnectionError:

Set objConn = New ADODB.Connection

With objConn
.Provider = conExcelOLEDBProvider
.Mode = adModeWrite
.ConnectionString = "Data Source=" & "C:\Will Working Folder\Will Rates.xls" & ";" & conExcelOLEDBProviderExtendedProperties
.Open
End With

OpenConnection = True

Exit Function

OpenConnectionError:

OpenConnection = False

End Function

Will