Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / AX to excel export
Finance | Project Operations, Human Resources, ...
Answered

AX to excel export

(0) ShareShare
ReportReport
Posted on by @rp@n 5,901

static void generateExcel()
{
SysExcelApplication sysExcelApplication;
SysExcelWorkbooks excelWorkbookCollection;
SysExcelWorkbook excelWorkbook;
SysExcelWorksheets excelWorksheetCollection;
SysExcelWorksheet excelWorksheet;
int i =1;
str filename;
Vendtable vendtable;
TransDate currentdate;
str saveFileName;
str showDate;

Query qr;

currentdate = systemdateget();
ShowDate = date2str(currentdate,123,1,0,2,0,4);

saveFileName = "POReport"+" "+ShowDate;
//info(showDate);

// give the file name and save it to d drive
filename = "d:\\saveFileName.xls";     ???

// initlize the SysExcelApplication instance
sysExcelApplication = SysExcelApplication::construct();

//create excel workbook and excel worksheet
excelWorkbookCollection = sysExcelApplication.workbooks();
excelWorkbook = excelWorkbookCollection.add();
excelWorksheetCollection = excelWorkbook.worksheets();
excelWorksheet = excelWorksheetCollection.itemFromNum(1);

//Excel columns captions

// for header

excelWorksheet.cells().item(i,1).value("Order Date");

excelWorksheet.cells().item(i,3).value("Order Number");
excelWorksheet.cells().item(i,4).value("Order Number Line");
excelWorksheet.cells().item(i,5).value("Vendor Code");
excelWorksheet.cells().item(i,6).value("Item: Description");

excelWorksheet.cells().item(i,7).value("Order Q'ty");
excelWorksheet.cells().item(i,8).value("Currency type");

i++;

// fill the excel with TABLE Fields
 // TODO


//check the fileName is already exist


if(WinAPI::fileExists(filename))
WinAPI::deleteFile(filename);

// save excel file
excelWorkbook.saveAs(filename);
}

@@ I wrote above code for AX to Excel export. It's working fine.

*** Few things are not working

1. When i run the report 1st time , then it's saved perfectly in above mentioned path. if i again run the report then got the below error

excError.jpg

2. I wants to save report with the current system date. that i tried but not working.

Please give me more shed on this.

Thanks!

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,089 on at
    RE: AX to excel export

    Hi @rp@n,

    One more suggestion is to use also try-catch statement in this case, to avoid situations when report generation failed but Excel instance stays not closed.

    try
{
   ... do you code here
   excelWorkbook.close();
}
catch
{
    if (excelWorkbook)
    {
        excelWorkbook.close();
    }
}

  • Jay Barot Profile Picture
    Jay Barot 1,502 on at
    RE: AX to excel export

    Hi @rp@n,

    It's good to hear that your problem got resolved. Stay connected.

  • @rp@n Profile Picture
    @rp@n 5,901 on at
    RE: AX to excel export

    Thanks all,

    it's working fine.

  • @rp@n Profile Picture
    @rp@n 5,901 on at
    RE: AX to excel export

    Thanks Sergei,

    Will do and let you know

  • Suggested answer
    Mav Profile Picture
    Mav on at
    RE: AX to excel export

    Hi @rp@n

    Already answered both your questions in my previous post. Did you read it ?

    For your

    1st Point - Like mentioned in previous community link I shared  & reiterated by Sergei Minozhenko you need to ensure excel is closed.

    2nd Point - Working code shared in my previous post ,it creates filename with datetime format, you just need to customize this code to date format to address your need.

    Please mark verify if the responses address your questions.

    Thanks

    Mav

  • Verified answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,089 on at
    RE: AX to excel export

    Hi @rp@n,

    1. When you use SysExcel* for export separate instance of Excel is created in the background. When you run saveAs it just saves a file to a needed location, but the file is still locked because the instance of Excel created by SysExcel* is still running.  To close it, you need to run excelWorkbook.close() at the end of your operation.

    2. Use strfmt function to combine string filename = strfmt("d:\\%1.xls", saveFileName);

  • @rp@n Profile Picture
    @rp@n 5,901 on at
    RE: AX to excel export

    Hi Sergei,

    1. The error is coming because in behind excel is running. That you mean??

    But I closed the excel file manually. And again run the report.

    Now required to add the code excelWorkbook.close() ??

    Here is my confusion. How its work

    See user execute the code then its saved in the path. Then user open the excel file and closed. And again  run the report.

    excelWorkbook.close() - how this code will work?? I not understand

    2. As I mentioned above

    saveFileName , please refer

    As my expectation is it should return the file name like PO04062020

    But its returned the file name

    saveFileName.xls.

    I wants to return file name = PO04062020

    Please give me more shed on this

  • Jay Barot Profile Picture
    Jay Barot 1,502 on at
    RE: AX to excel export

    Hi @rp@n,

    Sergei and Mav both are right. Also this error only occurs when the excel is open or in use. And you trying to generate excel. excel must be closed.

    Also as Mav has mentioned try to convert your date using DateTimeUtil and check.

  • Verified answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,089 on at
    RE: AX to excel export

    Hi @rp@n,

    1. You need to close excel after you finish the operation, otherwise Excel process keeps running and locked the file. (check task manager, you should have a lot of Excel processes if you already run it several times)

    excelWorkbook.close();

    2. I wants to save report with the current system date. that i tried but not working.

    What do you mean it's not working?

  • Suggested answer
    Mav Profile Picture
    Mav on at
    RE: AX to excel export

    Hi @rp@n

    Generally if we can see the data in Ax we press CTRL+T to export it to excel.

    Coming to your questions.

    1. For this a quick search on community landed me on this forum.

    community.dynamics.com/.../excel-cannot-access-the-file

    2. For this you can use the code below, customize the datetime format to date format if you want to save current system date.

    FileName =  'Yourfilename' + strRem(DateTimeUtil::toStr(DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::utcNow(), DateTimeUtil::getUserPreferredTimeZone())),'T-:') ;

    Above code would generate a file name like  Yourfilename202006040834 change to dateformat & you should get    Yourfilename20200604

    Please mark verified if it addresses your query.

    Thanks

    Mav

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,235 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