How to read Excel 2010 worksheets using the CCADOConnection class?

In my previous post, I showed how we can read Excel worksheets in Dynamics AX using ADO wrapper classes. You can read that post here. Well, the connection string used in the example will work only with Excel 97-2003 worksheets. If you try to open a Excel 2010 worksheet using the connection string in that you will get an error.

The reason for this is that the Excel 2010 format is the Office Open XML format and hence requires a different connection provider to connect to. This provider is the Access Database Engine component.

So we should modify our code slightly to make this work.

Now the connection string will be,"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFile.text() + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'");

This should let you open Excel 2010 worksheets as well.

While running this code, you may encounter any one of the following errors

  • Method 'open' in COM object of class 'ADODB.Connection' returned error code 0x800A0E7A () which means: Provider cannot be found. It may not be properly installed.

  • The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

  • To solve this, you will have to install the Microsoft Access Database Engine 2010 Redistributable components from here.

    Hope this helps.


    Popular posts from this blog

    How to add empty ranges in query

    Get selected records in Dynamics AX 2012

    The field with ID '0' does not exist in table - Cause and resolution.