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