CODEWITHSUNDEEP
sqlexcelvba
djblois
djblois

Reputation: 815

Use SQL in Excel VBA to access other Excel workbook

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

Upvotes: 2

Views: 7788

Answers (1)

Parfait
Parfait

Reputation: 107652

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.

Upvotes: 4

Related Questions