Database Connection Strings in Excel Add-in

Database Connection Strings in Excel Add-in

Excel As Database

Since excel is considered as database which can be connected by other applications. To configure and connect a Excel file as database a connection string is required. In this article we will see different ways to connect a excel file:

Microsoft ACE OLEDB 12.0

Following string will connect a XLSX file if file contains any macro the it would be disabled.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Folder\TestExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

XLSB File

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Folder\TestBinaryExcel2007file.xlsb;Extended Properties="Excel 12.0;HDR=YES";

XLSM file

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Folder\TestExcel2007file.xlsm;Extended Properties="Excel 12.0 Macro;HDR=YES";

XLS files

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Folder\TestExcelFile.xls;Extended Properties="Excel 8.0;HDR=YES";

Microsoft Jet OLE DB 4.0

XLS file

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Standard

OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Note: If data in the sheet contains first row as header then “HDR=Yes;

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.