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, adoConnection.open("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' retur...