Microsoft Dynamics AX blog by Zubair

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...

Recently I came across Bojan's post on performance improvements using CCADOConnection, CCADOCommand & CCADORecordSet classes for reading Excel sheets. You can read the original post here . Well, there is a minor change in the CCADORecordSet class in Dynamics AX 2009 compared to Dynamics AX 4.0 which most users should be aware of. But I thought of still documenting it here. The CCADORecordSet.moveNext() method isn't present in AX 2009 anymore. To move to the next recordSet, we should use adoRecordSet.recordSet().moveNext(); void readExcelSheet() { CCADOConnection adoConnection; CCADOCommand adoCommand; CCADORecordSet adoRecordSet; CCADOFields adoFields; CCADOField adoField; int i,j; ; adoConnection = new CCADOConnection(); adoRecordSet = new CCADORecordSet(); adoConnection.open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFile.text() + ";Extended Properties='Excel 8.0;HDR=No;...