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.

Answer 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.

Answer 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.