Opening ADO connection to Excel Spreadsheet in VBA

Question

How would I go about opening an ADO connection to an Excel 2007 spreadsheet?

I am doing this in order to import the data into Access 2007. Rather annoyingly, the data needs to be fltered and pre-processed before being imported, hence why I want to open an ADO connection to read it.

Solution 1

Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyExcel2007File.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
oConn.Open

Note that you need to use the ACE driver instead of JET. See also Connection strings for Excel 2007.

Solution 2

If you're going to be running the import more than once (i.e.: some type of daily reporting service), you might want to try a different approach than ADO.

I ended up creating a module in Access that pre-processes Excel sheets (since the sheet that gets imported every day changes) and then sets the sheet as the source of a linked table. Then, I query the linked table with a "INSERT INTO" DoCmd.RunSQL call to get the data out of Excel and into the DB.

If you'd like, I can go more into specifics.

Solution 3

 Set cnn = New ADODB.Connection
    'cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFullFileName & ";Extended Properties = Excel 12.0 Macro; HDR=No;"
    'cnn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; data source=" & sFullFileName & ";Extended Properties=Excel 8.0"
    cnn.ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & sFullFileName
    cnn.Open