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