I wrote this code a long time ago and it works great for 2003 and earlier workbooks. It extracts data from a closed excel workbook given a named range in the closed workbook. However, it does not work with 2007 files (xlsx). I get an error on the cnn.open line. I understand the file format completely changed from 2003 to 2007. I have looked an looked and I can't find any examples using ADO with later versions of Excel.
There are three files attached. The first contains the code below. The next two are the same except for one is a xls and the other is a xlsx. Both contain a range named "rngFrom" on Sheet1. This is the data that gets extracted.
Can someone please help me get this working. Thanks
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim WBCopyFrom As String, rngCopyFrom As String
Dim rngCopyTo As String
Dim PathName As String
Dim strSQL1 As String
'WBCopyFrom = "TestingTesting.xls" '<--This works
WBCopyFrom = "TestingTesting.xlsx" '<--This does not work
rngCopyFrom = "rngFrom"
rngCopyTo = "rngTo"
'Create connection to closed workbook
PathName = ThisWorkbook.Path & "\" & WBCopyFrom
Set cnn = New ADODB.Connection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & PathName & ";Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open '<-- Error: "External table is not in the expected format."
'Create SQL string
strSQL1 = "SELECT * FROM [" & rngCopyFrom & "]"
Set rs1 = New ADODB.Recordset
rs1.Open strSQL1, cnn, adOpenStatic, adLockOptimistic
'Copy to worksheet