How do I set up a connection to query data inside an Excel .xls file using Excel 2002?

Question

I am writing a small application in Excel 2002 and I need to store numbers in some format, it can be a string. The tables I have a 1:1 relationship and other table is just a table of one column so using access is not necesary and having to have another file is something I'd like to avoid. So, I want to store it in separate sheets.

However, I like the benefits of SQL for querying and I need it. I tried using some ADODB connection strings to reach this but I cannot achieve it.

I used the following code:

 Dim cn As Object, rs As Object, output As String, sql As String
    
    '---Connecting to the Data Source---
    Set cn = CreateObject("ADODB.Connection")
    With cn
        .Provider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;"
        .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & "Excel 8.0;HDR=Yes;IMEX=1"
        .Open
    End With

Also, do I have to use ODBC or should I use OLE DB? I don't know if OLE DB could be used to query in excel files.

Also, is it possible to do inserts with SQL using this ODBC or OlE DB? I tried different providers in the connection string, and I checked the ADO references to be available.

Also, I get this error: "Error 3706. The specified provider could not be found. It may not be installed properly."

Answer 1

Connection issue

First, there was an error in your Provider string, it should not contain the part with Data Source=C:\MyExcel.xls; since this is part of the connection string. So it should look like this:

    .Provider = "Provider=Microsoft.Jet.OLEDB.4.0;"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & "Excel 8.0;HDR=Yes;IMEX=1"

ODBC vs OLEDB

I've never used ODBC, but based on this answer, you can't use it to query an Excel file, so OLEDB is the way to go.

---

Insert Statement

Once you have a working ADODB connection, insert query should work as hoped. I'm providing an example below that worked for me, but there is a few caveats:

• I'm using the ACE.OLEDB.12.0 instead of JET.OLEDB.4.0 with Excel for Microsoft 365 MSO (Version 2112 Build 16.0.14706.20000) 64-bit on Windows 10.

• I'd suggest to set Mode=ReadWrite in your connection string to avoid potential writting permission issues (but it might work even without it.).

• Regarding the IMEX setting, I was having errors when it was set to IMEX=1, so I switched to IMEX=0 (see related question.

The example

With a workbook named Data.xls with the first sheet named Data and the following data :

^{Data for copy-paste}

I can run the following:

Dim wb As Workbook
Set wb = Workbooks("Data.xls")
Dim ws As Worksheet
Set ws = wb.Worksheets("Data")

'Create connection
Dim conn As Object
Set conn = CreateObject("ADODB.Connection")
With conn
    .Provider = "Microsoft.ACE.OLEDB.12.0;"
    .ConnectionString = "Data Source=" & wb.FullName & ";" & "Excel 8.0;HDR=Yes;IMEX=0;Mode=ReadWrite;"
    .Open
End With

'Compose the INSERT statement.
Dim query As String
Const sep = ", "
query = "INSERT INTO [" & ws.Name & "$] " & _
    "(Id, Name, Age) " & _
    " VALUES (" & _
    4 & sep & _
    "'" & "Joe" & "'" & sep & _
    40 & _
    ")"

'Execute the statement.
conn.Execute query, adCmdText

'Close the connection
conn.Close

And it should insert the data as follow:

---

Should you use ACE or JET?

If JET works for you, you might as well use it. Based on this article , you should also have the 32-bit version of ACE available with Windows 7 to work with Excel 2002 (32-bit), but based on your comment it seems like it's causing some problems.

See also some interesting answer about JET vs ACE.