Getting Data from Excel

[kirkm]

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Dim objConnection
Dim objRecordSet
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Scripts\Test.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";" 

objRecordset.Open "Select * FROM [Sheet1$]", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

stop

Do Until objRecordset.EOF
    Debug.Print objRecordset.Fields.Item("Name"), _
        objRecordset.Fields.Item("Number")
    objRecordset.MoveNext
Loop

Using the code above (as far as the Stop). Have tried with and without HDR
and column names but I get errors

No value given for one or more required parameters.
or
Too many fields defined.

How do I name and define just a few fields in the sql please?

 

[JHB]

Try the below, put in the column names you want instead of [NameOfXTheColumn]:

objRecordSet.Open "Select [NameOf1TheColumn], [NameOf2TheColumn], [NameOf3TheColumn] FROM ..."

 

[kirkm]

Thanks JHB, I thought I'd tried that but must have done something wrong as it's working fine now.

 

[JHB]

You're welcome, good you got it working now.