Use SQL in Excel VBA to access other Excel workbook

Question

I am trying to write a connection string and SQL script to run a query in Excel to pull data from another Excel workbook. This is what I have currently:

Sub Test()

   Dim conn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim sConnString As String
   Dim sql As String

   ' Create the connection string.
   sConnString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" & _
              "C:\Users\dblois\Desktop\Shareenas Report.xlsx" + ";Extended Properties=Excel 8.0;"

   ' Create the Connection and Recordset objects.
   Set conn = New ADODB.Connection
   Set rs = New ADODB.Recordset

   sql = "SELECT * FROM [Data$A1:AC73333]"

   ' Open the connection and execute.
   conn.Open sConnString
   Set rs = conn.Execute(sql)

   ' Check we have data.
   If Not rs.EOF Then
       ' Transfer result.
       Sheets(1).Range("A1").CopyFromRecordset rs
       ' Close the recordset
       rs.Close
   Else
       MsgBox "Error: No records returned.", vbCritical
   End If

   ' Clean up
   If CBool(conn.State And adStateOpen) Then conn.Close
   Set conn = Nothing
   Set rs = Nothing

End Sub

I keep getting the following when I try to open the connection. What is wrong with my connection string?

External table is not in the expected format

I found the Following and changed my code to be:

sConnString = "provider=Microsoft.Jet.OLEDB.12.0;data source=" & _
                  "C:\Users\dblois\Desktop\Shareenas Report.xlsx" + ";Extended Properties=Excel 12.0;"

Then I am getting the following error:

Provider cannot be found. It may not be property installed

@Comintern, no. I updated the Original question with some more information. — djblois, Mar 10 '17 at 20:19
You probably want to use [the ACE drivers](https://www.connectionstrings.com/ace-oledb-12-0/xlsx-files/) instead. — Comintern, Mar 10 '17 at 20:25
Yeah, it's Microsoft.ACE.OLEDB.12.0 - I don't think I've ever had to install it though, it should install with any reasonably modern version of Office. — Comintern, Mar 10 '17 at 20:28
@Bookeater - Feel free, I doubt that it will have value to anyone beyond the OP though - I'm just too lazy to find the all the duplicates. — Comintern, Mar 10 '17 at 20:59
@Comintern you might be surprised about the value, these connection strings are picky to work with for the unwary. — Bookeater, Mar 10 '17 at 21:13

score 3 · Answer 1 · answered Mar 10 '17 at 22:00

In first attempt your OLEDB driver is not appropriate for Excel file type. In second attempt, you have an incorrect OLEDB Driver as there is no 12.0 version for Jet. As @Comintern comments and answers in the very link you posted, use the ACE driver version. But do note for both types, drivers' 32/64-bit versions must match your MS Office bit version or any other program even languages (i.e., PHP, Python, Java) that you attempt to connect to Excel data source.

For older Excel .xls files, you would use Jet as this engine does not yet know of the .xlsx format and so does not recognize that file type:

strConnection = "Provider=Microsoft.JET.OLEDB.4.0;" _
                   & "Data Source='C:\Path\To\Excel.xls';" _
                   & "Extended Properties=""Excel 8.0;HDR=YES;"";"

For more recent Excel files (.xlsx, .xlsm, .xlsb), you would use ACE which is also backwards compatible for .xls types:

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                   & "Data Source='C:\Path\To\Excel.xlsx';" _
                   & "Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"

Alternatively, use ODBC which is the industry application connection layer used by many programs even non-Windows systems to connect to external backend sources. Even open source programming languages maintain ODBC APIs including PHP's PDO, Python's pyodbc, R's RODBC.

For older source formats:

strConnection = "DRIVER={Microsoft Excel Driver (*.xls)};" _
                  & "DBQ=C:\Path\To\Excel.xlsx;"

For newer source formats:

strConnection = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
                  & "DBQ=C:\Path\To\Excel.xlsx;"

And the same principles of drivers and bit verisons apply to MS Access .mdb vs .accdb versions.

Use SQL in Excel VBA to access other Excel workbook

1 Answers1