Reading In Excel Data into a Recordset from xlsx file

[CarlRostron]

I am trying to use a Connection to an xlsx spredsheet to read in a table of information (the data is not a table, just laid out in a table, see attached, the one I am trying to load is xlsx not xls but I cannot for some reson upload the xlsx one on here)

I have the following declared:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

my connection string is:

      strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & cstrFolder & Chr(92) & tempSymbol & ".xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';"

i open the connection which works fine:

      cn.Open strConnectionString

Now here I hit the problem, I have the following SQL string:

      strSelect = "SELECT * from [table$]"

The spreadsheet contains just 1 tab, named table so I think I am referring to the data correctly in my SQL string?

When I open the recordet for variable rs using:

rs.Open strSelect, cn, adOpenStatic, adLockOptimistic, adCmdText

I get the error dialogue box:
The Microsoft Access database engine could not find the object 'table$'. make sure the object exists and that you spell its name and the path name correctly.....

Any ideas on where this could be going wrong?

I did a Debug.print to check the strConnectionString was correct and the Source is exactly correct and refers to the name of the file I am trying to access exactly.

Please help.

 

[Frothingslosh]

How about just using DoCmd.Transferspreadsheet?

http://msdn.microsoft.com/en-us/library/office/ff844793(v=office.15).aspx

 

[CarlRostron]

My initial post said that I had checked the path and it was correct... I was in fact incorrect, how embarrassing. However, without your post of the alternative method and trying it, I wouldn't have found this as I know this method would have definitely worked. When I tried your Docmd.TransferSpreadsheet method, I got the same error message. This concluded me to realize the path to the file must have been in fact wrong all along... this was certainly the case when i dug a bit deeper.

I appreciate your post, and although I am still using my existing method, you helped lead me to the solution.

Thanks