web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Creating Excel File an...
Microsoft Dynamics AX (Archived)

Creating Excel File and then appending data to it

(1) ShareShare
ReportReport
Posted on by Community Member

Hi Everyone,

                        I am new to Dynamics Ax, i want to create an Excel file and then check if the file already exists, if it exists then append data to it. I tried writting data to Excel file and it worked but how to do "append" data to Excel file using Dynamics ax X++(through code).

Can anyone please  tell me how to do it. Thanks.

Best Regards

Shah

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    You have already written data to an excel file, then you just have to open an existing file and write to it like before.

    There is no append to excel file function like with text files.

  • Community Member Profile Picture
    Community Member on at

    Hi Rudi,

                 Thaks for your reply, What i am doing is, i create a Excel file with heading and save it to disk. then i get a record and i want to insert it in to that Excel file, if i save data as using "SaveAs" method it replaces the current Excel file and the headings are removed , what i want is the data from file should not be removed and new data is inserted after the last record in the file.

    Best Regards

    Shah

  • Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    Are you able to share the code you use for this?

    The bacis process for doing this would be to open the existing Excel sheet, then append the new data to it and save it again.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Here is the Code, Function "createExcelFile" creates the Excel file with headings , then "exportDataToExcel" reopens the Excel file and tries to append new record to the Excel file and saves it. " this.findLastRow();" fucntion return the last row from the existing Excel document to write the new record in row.

    Best Regards

    Shah

    public void createExcelFile()

    {

    SysExcelApplication xlsApplication;

    SysExcelWorkBooks xlsWorkBookCollection;

    SysExcelWorkBook xlsWorkBook;

    SysExcelWorkSheets xlsWorkSheetCollection;

    SysExcelWorkSheet xlsWorkSheet;

    SysExcelRange xlsRange;

    InventTable inventTable;

    int row = 1;

    str fileName;

    System.Exception  ex;

    try

    {

    // Name of the Excel document.

    fileName = "C:\\test.xslx";

    // Excel open and initialize.

    xlsApplication = SysExcelApplication:: construct ();

    // xlsApplication.visible (true);

    // Create an Excel Worksheet produce.

    xlsWorkBookCollection = xlsApplication.workbooks();

    xlsWorkBook = xlsWorkBookCollection.add();

    xlsWorkSheetCollection = xlsWorkBook.worksheets();

    xlsWorkSheet = xlsWorkSheetCollection.itemFromNum (1);

    // Write to the worksheet cells headings.

    xlsWorkSheet.cells (). item (row, 1). value ('Depot PostCode');

    xlsWorkSheet.cells (). item (row,2). value ('To PostCode');

    xlsWorkSheet.cells (). item (row,3). value ('Distance in Meters');

    row ++;

    // Save Excel document.

    xlsWorkbook.saveAs(fileName);

    // Close Excel.

    xlsApplication.quit ();

    xlsApplication.finalize ();

    }

       catch(Exception::Error)

       {

        info(strFmt("%1", Exception::Error));

       }

    }

    public void exportDataToExcel()

    {

    SysExcelApplication xlsApplication;

    SysExcelWorkBooks xlsWorkBookCollection;

    SysExcelWorkBook xlsWorkBook;

    SysExcelWorkSheets xlsWorkSheetCollection;

    SysExcelWorkSheet xlsWorkSheet;

    SysExcelRange xlsRange;

    InventTable inventTable;

    int row;

    str fileName;

    System.Exception ex;

    if (WINAPI:: fileExists ("C:\\test.xslx"))

    {

       row = this.findLastRow();

       try

       {

        // Name of the Excel document.

        xlsWorkBookCollection.open("C:\\test.xslx");

        // Excel open and initialize.

        xlsApplication = SysExcelApplication:: construct ();

       // xlsApplication.visible (true);

        // Create an Excel Worksheet produce.

        xlsWorkBookCollection = xlsApplication.workbooks();

        xlsWorkBook = xlsWorkBookCollection.add();

        xlsWorkSheetCollection = xlsWorkBook.worksheets();

        xlsWorkSheet = xlsWorkSheetCollection.itemFromNum (1);

        row ++;

        // Excel Worksheet with data fill / (Excel cells fill).

        xlsWorkSheet.cells (). item (row, 1). value ("Test Actual");

        xlsWorkSheet.cells (). item (row, 2). value ("Test VIrtual");

        xlsWorkSheet.cells (). item (row, 3). value ("Test VIrtual 3");

       // row ++;

        // Check whether the document already exists.

        // Save Excel document.

        xlsWorkbook.saveAs("C:\\test.xslx");

        // Close Excel.

        xlsApplication.quit ();

        xlsApplication.finalize ();

        }

       catch(Exception::Error)

       {

        info(strFmt("%1", Exception::Error));

       }

       }

    }

  • Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    I think your problem is with the exportDataToExcel method.

    First of all when i try this code it fails on opening the Excel document.

    But the main problem i think is in the line:

    xlsWorkBook = xlsWorkBookCollection.add();

    I think you are adding an new WorkBook here, and then you have a blank workbook you are writing your data in.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Hi Rudi,

                 Yes,  there was Problem with file opening but it was just a variable mistake. Can you plesae provide some Example code for appending data to Excel file. if you have it. or if you have tried it. Thanks

    Best Regards

    Shah

  • Suggested answer
    Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    Keeping it as simple as possible, this Job will write to an existing Excel sheet.

    Filename in variable fileName
    Row to write data to in variable excelRow

    static void RSH003_AppendExcel(Args _args)
{
    // Variables for Dialog
    FileName                fileName    = @'C:\Test.xlsx';

    // Variabler til Excel
    SysExcelApplication                 sysExcelApplication;
    SysExcelWorksheet                   sysExcelWorksheet;
    SysExcelWorksheets                  sysExcelWorksheets;
    SysExcelWorkbooks                   sysExcelWorkbooks;
    SysExcelWorkbook                    sysExcelWorkbook;
    SysExcelCell                        sysExcelCell;
    counter                             excelRow;
    
    
    // Open the Excel document
    sysExcelApplication = SysExcelApplication::construct();
    sysExcelWorkbooks   = sysExcelApplication.workbooks();

    //specify the file path that you want to read
    try
    {
        sysExcelWorkbooks.open(filename,0,false);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }

    sysExcelWorkbook    = sysExcelWorkbooks.item(1);
    sysExcelWorksheets  = sysExcelWorkbook.worksheets();
    sysExcelWorksheet   = sysExcelWorksheets.itemFromNum(1);

    excelRow                            = 2;
    
    SysExcelCell = SysExcelWorksheet.cells().item(excelRow,1);
    SysExcelCell.value("Col1");

    SysExcelCell = SysExcelWorksheet.cells().item(excelRow,2);
    SysExcelCell.value("Col2");
    
    SysExcelCell = SysExcelWorksheet.cells().item(excelRow,3);
    SysExcelCell.value("Col3");
    
    // Save the Excel file
    sysExcelApplication.displayAlerts(false);
    sysExcelWorkbook.saveAs(fileName);
    sysExcelWorkBook.saved(true);
    sysExcelApplication.quit();
}


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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Ali Zaidi Profile Picture

Ali Zaidi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans