Microsoft OLEDB Adapter for Microsoft Excel 2007

Introduction

This article is for developers who want to play with different Office files (*.csv, *.xlsx, *.xls, *.doc) without Office being installed on the machine, by using Microsoft OLEDB Jet drivers. I am writing this article to just help you out with Microsoft Jet OLEDB problems with Microsoft Office 2007.

Background

Recently, I was working on my application for dynamic data to get data from Excel sheets. I was using Microsoft Jet OLEDB drivers for Excel 8.0 ISAM. It was working fine with *.xls extensions. But, I came across the problem of Microsoft Excel 2007 *.xlsx not working with Microsoft Jet OLEDB for Excel 8.0. One of the reason might be ISAM missing for Microsoft Excel 2007.

Using the code

I will not be explaining the whole phenomenon of getting Excel sheets from an Excel file and getting data from these sheets. You can find an article on working with Microsoft Jet OLEDB with Excel from CodeProject. However, I will guide you through the problem of connecting Excel 2007 with Microsoft Jet OLEDB.

What you have to do is change the connection string as follows. Remember to test this. You should have Microsoft Office 2007 (or Microsoft.Ace.Oledb components installed that you can find at: http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en) installed.

private string mExcelCon = @"Provider=Microsoft.ACE.OLEDB.12.0;";

private string mConnectionString; 
mConnectionString = mExcelCon + "Data Source=" + fileName + 
                    ";Extended Properties=" + Convert.ToChar(34).ToString() + 
                    "Excel 12.0;HDR=Yes" + Convert.ToChar(34).ToString();

Microsoft.Ace.Oledb 12.0 supports all Microsoft Excel versions up till 2007.