Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

Duplicate an Excel workbook from x++ AX2012

Posted on by 529

Hi All,

I am just starting to work with excel files through x . As you may guess I know very little.

I have a template already made in Excel where I want to fill it with the data from the a selected production. this template is called routecard (RK).

since I don't want to modify the existing template I prefer to Open a copy of the template from X and modify from there.

Since I dont know exacly how to do that I am starting by creating a new file (RK_new), and copy each sheet from the template RK excel (RK)

here is what I'got so far. The problem is in the two last lines where I want to add a new sheet infolog shows: "COM Object, method add called with invalid parameters"

you help is greatly appreciated , I am quite sure there should be an easier solution just by somehow clone the workbooks

 //******** template WorkBook
    SysExcelApplication appRK;
    SysExcelWorkbooks wbsRK;
    SysExcelWorkbook wbRK;
    SysExcelWorksheets wssRK;
    SysExcelWorksheet wsRK;
    SysExcelCells cellsRK;
    COMVariantType typeRK;
    FileName filename;
    
    //******** New WorkBook
    SysExcelApplication appRK_new;
    SysExcelWorkbooks wbsRK_new;
    SysExcelWorkbook wbRK_new;
    SysExcelWorksheets wssRK_new;
    SysExcelWorksheet wsRK_new;
    SysExcelCells cellsRK_new;
    COMVariantType typeRK_new;
    int rowRK_new;
    ItemId itemidRK_new;
    


    //******* Open the Original RK Template
    appRK = SysExcelApplication::construct();
    wbsRK = appRK.workbooks();
    filename = "W:\\Rutekort\\Poland\\RK_PL.xlsm";
    try
    {
        wbsRK.open(filename,0,true);
        appRK.visible(true);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }
    
    wbRK  = wbsRK.item(1);
    wssRK = wbRK.worksheets();
    
    //******* Open New Excel for RK
    appRK_new = SysExcelApplication::construct();
    wbsRK_new = appRK_new.workbooks();
    wbRK_new = wbsRK_new.add(); // Creates new excel File
    wssRK_new = wbRK_new.worksheets(); //point to the its worksheets
    appRK_new.visible(true);
    
    //***** copying the first ws into the new workbook RK_new * ERROR
    wsRK_new = wssRK.itemFromNum(1);
    wssRK_new.add(wsRK_new);
pastedimage1613653090850v1.png

  • Suggested answer
    CBNestor Profile Picture
    CBNestor 529 on at
    RE: Duplicate an Excel workbook from x++ AX2012

    I apologize guys , this was a silly question from my side,

    Actually I have never made use of templates .xlts in Excel. What I needed to do was just save my excel as a template .xltm (enable for macros) this way when the user needs to open it from AX it will just make automatically a new one (with no track of the original path of the file which is what I like) .

    other way is opening the excel from AX in the code as a Read-Only here the code for that:

    that way I can make sure that the user wont modify the original file from where is beign opened.

    Note: as far as I have been reading there is not predefault method in AX to copy or duplicates workbooks

     pastedimage1613816246637v2.png

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!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 229,963 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans