Home > Ax Technical > Using ADO to read from Excel in Microsoft Dynamics Ax

Using ADO to read from Excel in Microsoft Dynamics Ax

 
 
        Sometime back i had a posting referring to Gloomies blog (click to see the old posting) on using ADO in Ax. Gloomie had  used COM based ADO control. Ax also has  predefined ADO Com classes which  you can use….
 
  Here’s a small Job that will help you in working with the Inbuild ADO classes
 
static void JobADOtoAccessExcelInAxapta(Args _args)
{
    CCADOConnection  adoexcel     = new CCADOConnection();
    CCAdoxCatalog    adoCatalog   = new CCAdoxCatalog();
    COM              adoxCatalog  = new COM(‘ADOX.Catalog’);
    CCADORecordSet   adoRecordSet = new CCADORecordSet();
    CCADOFields      adoFields;
    CCADOField       adoField;
    int               i;
    ;
    adoexcel.connectionString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=  
    C:\\TestAdo.xls  ;Extended Properties=’Excel 8.0;HDR=Yes;IMEX=1’");
    adoexcel.open();
    adoxCatalog.activeConnection(adoexcel.connection());
    adoCatalog.adoxCatalog(adoxCatalog);
    print adoCatalog.tableName(1);
    pause;
    adoRecordSet.open(@"SELECT * FROM [SHEET1$]", adoexcel);
    adoFields = adoRecordSet.fields();
    for (i = 0; i < adoFields.count(); i++)
    {
        adoField = adoFields.itemIdx(i);
        print adoField.name();
        pause;
    }
    while (! adoRecordSet.EOF())
    {
        for (i = 0; i < adoFields.count(); i++)
        {
            adoField = adoFields.itemIdx(i);
            print adoField.value();
            pause;
        }
        adoRecordSet.moveNext();
    }
}
 
 
Here is a snap shot of the excel that i used for the job….
 
 
 
……………… Keep watching while I post more on this …
Advertisements
  1. No name
    May 14, 2007 at 12:46 pm

    Hi Kamal !
     
    Nice trick 😉
     
    Only want to add that you can use this for add data to Excel datasheet too. I mean you can use it for read/write data 🙂
     
    Sample here : http://www.trucosax.com/phpnuke/modules.php?name=Forums&file=viewtopic&t=665
    (As always … the problem is that the explanation is in Spanish but the code is X++ :P)
     
    Kind Regards,
     
    Mkz.
    ————————–
    http://www.trucosax.com
    A Dynamics Ax tricks site in Spanish language 🙂

  2. Kamal
    May 14, 2007 at 12:46 pm

     
    Exactly right Manel,
     
       I would soon post on a wizard through which you can read data from any excel sheet and insert in to any table….
     

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: