How to use EXCEL 2007 in ACE OLEDB

In this article I am going to explain about ACE OLE DB 12.0.
  • 2177

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

Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.