How to Connect XLSX file (Excel Workbook) through ADO

Submitted by Support on Thu, 08/05/2010 - 02:26

Using Excel (Xlsx) file as a database using VBA (ActiveX Data Objects - ADO)

In the past we have already seen how to Connect to an Excel file using ADO and query its contents. That was using Microsoft Excel 2003 or earlier. With Office 2007 the file formats haver changed to XLSX, which might create the following problems

External table is not in the expected format - Excel ADO VBA

or
Could not find installable ISAM.
Excel ADO VBA

to solve that use the following Connection string:

cN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\comp\documents\visual studio 2010\Projects\ExcelWorkbookDocLevel\ExcelWorkbookDocLevel\ExcelWorkbook1.xlsx;Extended Properties=Excel 12.0;Persist Security Info=False"