How to combine excel 2 files through X++ ?

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hello everyone !

I exportd 2 multi sheet Excel files from ssrs report by code. And now, I want to combine 2 files to 1 with sheets are added in specific possition on new excel file using X++.

I tried to copy 2 sheets from 1 workbookto a new workbook as below

    SysExcelApplication application;
    SysExcelWorkbooks   workbooksCollectionInv;
    SysExcelWorkbooks   workbooksCollectionNew;
    SysExcelWorkbook    workbookInv;
    SysExcelWorkbook    workbookNew;  
    SysExcelWorksheets  worksheetsCollectionInv;
    SysExcelWorksheets  worksheetsCollectionNew;
    SysExcelWorksheet   worksheetInv;
    SysExcelWorksheet   worksheetNew;
    Filename                fileName = strFmt("C:\\Invoice_%1\\PKL_TB181093-TB181096.xlsx",curUserId());
    
    
    application = SysExcelApplication::construct();
    workbooksCollectionInv = application.workbooks();
    //specify the file path that you want to read
    workbooksCollectionInv.open(filename);
    workbookInv                 = workbooksCollectionInv.item(1);
    worksheetsCollectionInv     = workbookInv.worksheets();
    worksheetInv        = worksheetsCollectionInv.itemFromNum(1);
    info(worksheetInv.name());
    
    workbooksCollectionNew    = application.workbooks();
    workbookNew         = workbooksCollectionNew.add();
    worksheetsCollectionNew   = workbookNew.worksheets();
    
    worksheetNew = worksheetsCollectionNew.add(worksheetsCollectionInv.itemFromNum(1), worksheetsCollectionInv.itemFromNum(2));
    
    application.quit();

But get an error

Error executing code: COM (object), method add called with invalid parameters.
Stack trace
(C)\Classes\COM\add
(C)\Classes\SysExcelWorksheets\add - line 16
(C)\Jobs\JobCombineSheets - line 28

I couldn't fix that any help Please?

*This post is locked for comments

All responses (4)

Answers (1)

Blue Wang on at

Like (0)

Report

RE: How to combine excel 2 files through X++ ?

Hi Vianney,

I'm glad to hear that you've solved it.

please mark it as verified. This will help other users with the same theme.

Thanks.
Community Member Microsoft Employee on at

Like (0)

Report
RE: How to combine excel 2 files through X++ ?

Hi Wang,

I've tried the 2nd way as your suggestion, change from copy range to copy sheet as below. And then, it works.

public SysExcelWorksheet copy(SysExcelWorksheet _sheet) { SysExcelWorksheet ret; ; ret = SysExcelWorksheet::construct(version, worksheet.copy(_sheet.comObject())); return ret; }

Many thanks !
Community Member Microsoft Employee on at

Like (0)

Report

RE: How to combine excel 2 files through X++ ?

Hi Blue Wang,

Thanks for your reply. I will try and give the feedback later
Verified answer

Blue Wang on at

Like (1)

Report

RE: How to combine excel 2 files through X++ ?

Hi Vianney,

Try to delete object cache = .auc files in %userprofile%\AppData\Local folder. and then restart AOS, incremental or full cil compile.

You can try another way to copy data from another sheet.
Here's a similar example. https://community.dynamics.com/ax/f/33/t/103813

If you have the latest error information or solutions, please share them in time.Thanks.

How to combine excel 2 files through X++ ?

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates

How to combine excel 2 files through X++ ?

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Subscribe to

Leaderboard

Featured topics

Product updates