Need Help with VBA reading Excel using ADO 
Author Message
 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



Wed, 07 Dec 2005 06:43:32 GMT
 Need Help with VBA reading Excel using ADO

Hi,

Try to use SELECT * instead of specifying field names.

--
Val Mazur
Microsoft MVP


Quote:
> 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



Fri, 09 Dec 2005 20:43:18 GMT
 Need Help with VBA reading Excel using ADO
Val - Thanks for the tip. Unfortunately I still revieve the same error.
Other suggestions are welcome.


Quote:
> Hi,

> Try to use SELECT * instead of specifying field names.

> --
> Val Mazur
> Microsoft MVP



> > 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



Sat, 10 Dec 2005 07:48:04 GMT
 Need Help with VBA reading Excel using ADO
Hi,

Looks like all my Tuesday relies disappeared. Try to use next code to open
recordset

for connection string

MyConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=c:\somepath\mySpreadsheet.xls;" & _
           "Extended Properties=""Excel 8.0;HDR=Yes"""

for SELECT statement
objCmd.CommandText = "SELECT * FROM [Sheet1$]"

--
Val Mazur
Microsoft MVP


Quote:
> Val - Thanks for the tip. Unfortunately I still revieve the same error.
> Other suggestions are welcome.



> > Hi,

> > Try to use SELECT * instead of specifying field names.

> > --
> > Val Mazur
> > Microsoft MVP



> > > 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



Mon, 12 Dec 2005 20:31:12 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Need Help Manipulating Excel 2000 from Access 2000 using VBA

2. Unable to reuse ADO.Recordset connection using VBA script in Excel

3. Excel VBA using ADO to extract data from SQL Server

4. HELP: Need to read a formatted text file using ADO

5. How to read Excel file using ADO?

6. Q : Need help reading an Excel spreadsheet

7. I need Help about OO4O, samples how to read Excel Files

8. Problem reusing ADO.Recordset object in VBA script in Excel

9. Strange ADO Behavior with Excel VBA

10. VBA, ADO, Excel

11. ADO in Excel VBA

12. VBA, ADO and Excel 97


 
Powered by phpBB® Forum Software