Trouble Connecting to Excel w ADO

[rokuk]

Hi there!

I am new to VBA and trying to query an Excel spreadsheet to enter all the data into a temporary Access table. I am taking one step at a time though, and after reading this microsoft article on how to query a spreadsheet from Access (http://www.microsoft.com/technet/scriptcenter/resources/officetips/jun05/tips0607.mspx) I thought I was well on my way. I liked how they kind of broke it all down for you.

Well, I actually can't get the code from the article working! The only things I've changed are the file's directory path and names (file, worksheet, and Field names (headers)). I get a "compile error: variable not defined" on

Set objConnection

and I'm pretty sure it'll be the same with the following

Set objRecordset

What I take from that error is that I'd need to predefine those objects before being able to use them... but isn't that what those statements are doing?! Maybe I'm just using it wrong... I have the script in the code for a Form I made as the Click event for a cmdButton. I'm using Access and Excel 2003. Here's the code in case it's easier than loading the article:

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

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

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

Also, I've been looking to try to find a good tutorial for learning ADO commands, but if anyone has any suggestions that would be great to hear too! Thank you for any advice!

 

[pbaldy]

Try adding these at the top

Dim objRecordset As ADODB.Recordset
Dim objConnection As ADODB.Connection

 

[rokuk]

ahh!

doh! that does make sense, and it does indeed solve those errors.

I am now getting the same error for Wscript.Echo

I've tried just declaring

Dim Wscript

and although that resolves the error, the program isn't executing correctly, so I'm pretty sure it needs to also be "Set" to something... but the tutorial isn't very clear on that part and I'm not sure what it should be Set to.

This is a lot harder than I thought


EDIT:

I've realized I don't really need to figure out the above error, as I am going to want to be importing the data instead of displaying it, anyway. So I will look into that now instead - but thank you for your help!!