Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Split values on export...
Finance | Project Operations, Human Resources, ...
Suggested answer

Split values on export to Excel

(0) ShareShare
ReportReport
Posted on by NDingankar22 487

Hi.

Greetings

I have  a form where the details are as follow.

The item details are retrieved from Movemet JOurnal (posted) in ax 2012

Hence diff movement journals can have one or many multiple items. The details are saved in that table in a field concatenate by + sign

I want to export the item details into one single column. How can i acheive that when i export to excel(by code lets say as standard export to excel will not work.

How do i split the item into different column each of excel .Can anyone assist.

Dynsmics_2D00_Community.png

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Split values on export to Excel

    Hi NDingankar22,

    Wouldn't it be easier to export the data as they are and then use an Excel macro to format the columns and rows as you need it?

    Best regards,

    Ludwig

  • Suggested answer
    ergun sahin Profile Picture
    ergun sahin 8,812 Super User 2024 Season 1 on at
    RE: Split values on export to Excel

    I haven't tested but something like this will fix your problem. Maybe minor changes may be required.

    static void TheaxaptaCreateExcel(Args _args)
{
    SysExcelApplication  xlsApplication;
    SysExcelWorkBooks    xlsWorkBookCollection;
    SysExcelWorkBook     xlsWorkBook;
    SysExcelWorkSheets   xlsWorkSheetCollection;
    SysExcelWorkSheet    xlsWorkSheet;
    SysExcelRange        xlsRange;
    CustomTable          customTable;
    int                  row = 1;
    str                  fileName;
    container            paramAsCon;
    int i;
    
    //Filename
    fileName = "C:\\Test.xlsx";
    //Initialize Excel instance
    xlsApplication           = SysExcelApplication::construct();
    //Open Excel document
    //xlsApplication.visible(true);
    //Create Excel WorkBook and WorkSheet
    xlsWorkBookCollection    = xlsApplication.workbooks();
    xlsWorkBook              = xlsWorkBookCollection.add();
    xlsWorkSheetCollection   = xlsWorkBook.worksheets();
    xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
    //Excel columns captions
    xlsWorkSheet.cells().item(row,1).value("Journal Num");
    xlsWorkSheet.cells().item(row,2).value("Item1");
    xlsWorkSheet.cells().item(row,3).value("Item2");
    xlsWorkSheet.cells().item(row,4).value("Item3");
    xlsWorkSheet.cells().item(row,5).value("Item4");
    //just caption not that important
    row  ;
    //Fill Excel with customTable JournalNum and item fields (only 20 records)
    while select customTable
    {
        if(row == 20)
        break;
        xlsWorkSheet.cells().item(row,1).value(customTable.JournalNum);
        

        paramAsCon = str2con(paramAsStr, ",");
    
        for (i=1;i<=conLen(paramAsCon);i  )
        {
            //info(conPeek(paramAsCon, i));    
            xlsWorkSheet.cells().item(row,1 i).value(conPeek(paramAsCon, i));
        }
        row  ;
    }
    //Check whether the document already exists
    if(WinApi::fileExists(fileName))
        WinApi::deleteFile(fileName);
    //Save Excel document
    xlsWorkbook.saveAs(fileName);
    //Open Excel document
    xlsApplication.visible(true);
    //Close Excel
    //xlsApplication.quit();
    //xlsApplication.finalize();
}

  • NDingankar22 Profile Picture
    NDingankar22 487 on at
    RE: Export to Excel-Ax 21012

    ok.but how to generate columns in excel .. I will split it in comtainer but excel column generation is i m stuck at

  • Suggested answer
    ergun sahin Profile Picture
    ergun sahin 8,812 Super User 2024 Season 1 on at
    RE: Export to Excel-Ax 21012

    You can use str2con or strSplit

    www.schweda.net/blog_ax.php

    then you can export to excel

    www.theaxapta.com/.../exporting-data-to-excel-from-axapta-x.html

  • NDingankar22 Profile Picture
    NDingankar22 487 on at
    RE: Export to Excel-Ax 21012

    Thnk you for reply. The result should be as follows in excel. Like every item in each excel column(separate excel column)

    Jounrlnum  item1                      item2                                      item3                                                  item4

    GJ1             itema(apple)           itemb(badmonton)                        

    GJ2             itemc(choloclate)    itemD(dettol)                          

    GJ2                                                                                      

    G3              itema(apple)           itemb(badminton)                 item (cholocalte)                                itemD(detto))

  • ergun sahin Profile Picture
    ergun sahin 8,812 Super User 2024 Season 1 on at
    RE: Export to Excel-Ax 21012

    Can you show us what u want as a result.  Or the table you showed us are  the result you are trying to achieve?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans