How to use EXCEL 2007 in ACE OLEDB
In this article I am going to explain about ACE OLE DB 12.0.
Introduction
ACE OLE DB It's a product which is developed by Microsoft, It is Released With Office 2007. This one is Used for connecting to Excel 2007 files with the Xlsx file extension.
Coding
Include "Provider=Microsoft.ACE.OLEDB.12.0" in the connection string to use this provider.
Xlsx files
It's for connecting to Excel 2007 files with the Xlsm file extension.
Example of Office Open XML format
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\sam007\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";
|
"HDR=Yes;" indicates that the first row contains column names, not data. "HDR=No;" indicates the opposite.
Treating data as text
It is used when you want to treat all data in the file as text, overriding Excels column type "General" to guess what type of data is in the column.
Example of data as text
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\sam007\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";
|
Here use IMEX=1 to avoid crash.
Ask Your Question
Got a programming related question? You may want to post your question here
Programming Answers here