Greetings. I am currently using a DAO recordset in Excel 2007 to import a table from another Excel workbook. The only way I can get it to work is by saving the source workbook in 2003 format, and using the below code.
Dim DB As DAO.database
Dim RS As DAO.Recordset
FilePath = "\\ojrfscdata1\ojcsfs01\share\Billing Services Planning and Analysis\Greg\"
Obj = "2011 Spec Limits Template.xls"
Set DB = OpenDatabase(FilePath & Obj, False, True, "Excel 8.0;HDR=Yes")
Set RS = DB.OpenRecordset("SELECT * FROM [VA$]")
This works like a charm, as long as I have "Excel 8.0" in the extended properties argument. However, I would like to be able to do this using Excel 2007 as the source, since my company has transitioned all pc's to Office 2007. From what I can gather, I would need to use "Excel 12.0". This, predictably, does not work. Does it have anything to do with the ACE OLEDB provider? Is there a way I could change my OpenDatabase statement to accomodate an Excel 2007 file a the data source? Thank you.
Greg
Dim DB As DAO.database
Dim RS As DAO.Recordset
FilePath = "\\ojrfscdata1\ojcsfs01\share\Billing Services Planning and Analysis\Greg\"
Obj = "2011 Spec Limits Template.xls"
Set DB = OpenDatabase(FilePath & Obj, False, True, "Excel 8.0;HDR=Yes")
Set RS = DB.OpenRecordset("SELECT * FROM [VA$]")
This works like a charm, as long as I have "Excel 8.0" in the extended properties argument. However, I would like to be able to do this using Excel 2007 as the source, since my company has transitioned all pc's to Office 2007. From what I can gather, I would need to use "Excel 12.0". This, predictably, does not work. Does it have anything to do with the ACE OLEDB provider? Is there a way I could change my OpenDatabase statement to accomodate an Excel 2007 file a the data source? Thank you.
Greg