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

    Comments

    Post a Comment

    Popular posts from this blog

    How to add empty ranges in query

    -
    In Dynamics AX programming, a common mistake I have seen when it comes to query ranges is passing an empty string value to the range. The developer here is expecting it to show all the records where the range field is empty. Instead, what we get is all records without the range being applied. As baffling as it may seems, it is easily explainable. The query engine in Dynamics AX, will not add a range if you don’t specify a value or if you specify an empty string because an empty string is treated as no value. So the following code will result in this SQL statement, query = new Query(); qbds = query.addDataSource(tableNum(ProjTable)); qbr = qbds.addRange(fieldNum(ProjTable, Name)); qbr.value(''); Select first fast * from ProjTable; But what was expected was something like, Select first fast * from ProjTable where Name == ''; So to work around this issue, use the SysQuery::valueEmptyString() static method which parses the empty strings correctly so that it
    Read more

    Get selected records in Dynamics AX 2012

    -
    Image
    In my earlier post, we discussed how we can retrieve all selected records on a list page. You can read that post here . Retrieving all selected records from a caller form is very easy. The code for this can be found in a number of places in standard Dynamics AX codebase. The general approach is to use the dataSource.getFirst(1) statement to get the first selected record and loop through the remaining using dataSource.getNext() . In Dynamics AX 2009, a new class was introduced for this purpose. This was the MultiSelectionHelper class. In Dynamics AX 2012, another new class has been introduced to retrieve the selected records. This is the MultiSelectionContext class. The MultiSelectionHelper class is an application class. It presents two constrcutors. The standard constructor method just creates a new instance of the MultiSelectionHelper class. When this construct is used, you will have to explicitly set the dataSource to be used for fethcing selected records. This can be done
    Read more

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

    -
    Image
    Hello readers, In Dynamics AX coding, you should have come across this error sometime - The field with ID '0' does not exist in table 'SomeTable' So what causes this error and what is the possible resolution to it? Well, the most likely cause for this error is a missing mapping in a table map. Let us look at an example. I've a simple Map named CustVendAccount with one Account field. I've created two mappings - one with CustTable's AccountNum field and for the otehr mapping, I just specify VendTable but no field. This is how my map looks like, Now, I've a simple job which just passes either CustTable or VendTable as an argument and then we print the Account field in the map. static void CustVendAccountMap(Args _args) { CustTable custTable; VendTable vendTable; void readMap(CustVendAccount _custVendAccount) { info(strFmt("Value from table %1 is %2",tableId2name(_custVendAccount.TableId), _custV
    Read more