Pulling Excel Data to Access with ADO

[chazz1]

Hello,

I am trying to use a macro to pull data from Excel 2007 and append it to a table in Access 2007. My code looks like this:

Sub RetrieveInspection()
Dim MyConnect As String
Dim MyRecordSet As ADODB.Recordset
Dim MyTable As ADODB.Recordset
Dim MySQL As String
 
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"DataSource=N:\My Documents\File\Sample.xlsx;" & _
"Extended Properties=Excel 12.0"
 
MySQL = "SELECT FROM [Sheet1]"
 
Set MyRecordSet = New ADODB.Recordset
MyRecordSet.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

When it gets to the last line, it returns an error message: "Could not find installable ISAM". I'm fairly sure it's a problem with the connection string, but I've tried a lot of variations and can't get it working.

Thanks.

 

[SydneyGeek]

Try...

MySQL = "SELECT FROM [Sheet1$]"

Denis

 

[chazz1]

Running smoothly. Thanks!