• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 655
  • Last Modified:

Extract Data from Closed XLSX Workbook Using ADO

All,
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

Kyle
Sub ExtractData()
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

'Initiailze variables
'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
With cnn
    .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."
End With

'Create SQL string
strSQL1 = "SELECT * FROM [" & rngCopyFrom & "]"

'Create recordset
Set rs1 = New ADODB.Recordset

'Open recordset
rs1.Open strSQL1, cnn, adOpenStatic, adLockOptimistic

'Copy to worksheet
Sheet1.Range(rngCopyTo).CopyFromRecordset rs1

'Clean up
rs1.Close
cnn.Close
End Sub

Open in new window

ExtractFromClosedWB.xlsm
TestingTesting.xls
TestingTesting.xlsx
0
kgerb
Asked:
kgerb
  • 3
  • 3
1 Solution
 
Arno KosterCommented:
.XLSX documents are not excel 8.0 but should be excel 12.0 instead.
0
 
kgerbChief EngineerAuthor Commented:
Now I get an error "Could not find installable ISAM"???

Kyle
0
 
andrewssd3Commented:
If you are also using 2007 or later try this instead
   With mobjConn

        .Provider = "Microsoft.ACE.OLEDB.12.0"

        .ConnectionString = "Data Source=" & wbCopyFrom & ";" & _

                "Extended Properties=Excel 12.0;"

        .Open

    End With

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
andrewssd3Commented:
Got some extra blank lines in there - sorry you'll need to delete them
0
 
kgerbChief EngineerAuthor Commented:
andrewssd3,
Thanks, it's working.  Can you explain a little bit.  What's the difference between the Jet provider and the Ace provider?  Why does one work and not the other?

Thanks again,
Kyle
0
 
andrewssd3Commented:
The ACE.OLEDB one is the more recent one and works with Office 2007 and 2010 - I'm not sure of the exact details I'm afraid.  It should also be backward compatible
0
 
kgerbChief EngineerAuthor Commented:
No problem.  Thanks so much.  I appreciate your help.

Kyle
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now