Need Help with VBA reading Excel using ADO
Folks,
With the help of this newsgroup I hve succesfuly created a connection to my
excel file. The problem I have is when I try to execute
Set objRs = objCmd.Execute
At that point the error message back is
ODBC Driver Error Too Few Paramaters Expected 1
I am probably missing something obvious.
Thanks in advance.
The code...
Sub f1()
' ODBC Provider Using a DSN-Less Connection String
Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRs As New ADODB.Recordset
' SQL Command
objCmd.CommandText = "SELECT Project_ID, SalesPerson " & _
"FROM `Sheet1$` " & _
"WHERE Salesperson = 'Rob' " & _
"ORDER BY Project_ID"
objCmd.CommandType = adCmdText
' Connect to the data source.
Set objConn = GetNewConnection
objCmd.ActiveConnection = objConn
' Execute
Set objRs = objCmd.Execute
Set rs1 = cn.OpenSchema(adSchemaTables)
End Sub
---
'BeginNewConnection
Private Function GetNewConnection() As ADODB.Connection
Dim oCn As New ADODB.Connection
Dim sCnStr As String
sCnStr = "Provider=MSDASQL;" & _
"Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=C:\My Documents\Peter\MailMerge\ProjectData.xls"
oCn.Open sCnStr
If oCn.State = adStateOpen Then
Set GetNewConnection = oCn
Debug.Print "Connection is open."
End If
End Function
'EndNewConnection
