Automator add to Excel file overwrite tab issue - xlsm

Altair Forum User
Altair Forum User
Altair Employee
edited August 2018 in Community Q&A

Is there anything special about an xlsm version of Excel that keeps the Automator from overwriting a tab?  The setup below has worked for me with an xlsx version, but with the macro enabled file version, instead of overwriting the FacList as noted below, it creates a new tab called FacList1.  I've looked through the Excel options and I don't see any protection issues within that application.  I don't see any prior comments about this in this venue either.  This next week, I am scheduled to upgrade from 13.5 to 15.1, so that may possibly take care of the issue.  Again, I've done this successfully in another process with an xlsx version without a problem.

 

I tried saving the Excel file as an xlsx version and changing the Automator process to test my theory of the issue being the Excel file type.  However, strangely, the Automator keeps reverting back to xlsm after I press Apply and click Ok, the change to xlsx....

 

image

Tagged:

Answers

  • Altair Forum User
    Altair Forum User
    Altair Employee
    edited August 2018

    Hi Brian,

     

    I have seen issues with corrupted named ranges in various Excel versions.  Could you try temporarily changing the name of the xlsm file in the export so that it creates a new one the first time it runs.  Then try and run it again to see if it can append to the new version.

     

    If everything is good with the new file, then please try to delete and recreate the named ranges.

     

    Incidentally, to change the Excel file type from xlsx to xlsm.  I believe you would need to change the file type drop down AND the file extension in the destination line.

     

    cheers,

    Steve.

  • Altair Forum User
    Altair Forum User
    Altair Employee
    edited August 2018

    I deleted the tab within the destination Excel file and allowed the Automator to create it.  Afterwards, Automator worked perfectly by replacing the tab versus creating a new tab has it had been doing.

    I believe that is what I'll do going forward....  If I want the Automator to append to a file, but replace a tab, I'll let it initially create the tab versus having it write-over a tab I've created.  Automator sets up the table range and does the replace going forward perfectly.

  • Altair Forum User
    Altair Forum User
    Altair Employee
    edited August 2018
    Altair Forum User said:

    I deleted the tab within the destination Excel file and allowed the Automator to create it.  Afterwards, Automator worked perfectly by replacing the tab versus creating a new tab has it had been doing.

    I believe that is what I'll do going forward....  If I want the Automator to append to a file, but replace a tab, I'll let it initially create the tab versus having it write-over a tab I've created.  Automator sets up the table range and does the replace going forward perfectly.

    Hi Brian,

    I tested Export to Excel (Table: add data, Sheet: Overwrite) in v14 and 15, and it worked just fine.  You can try it once you upgrade to v15.0.3.

    Regards

    Mo

Categories