Forum Discussion

Shetal_P's avatar
Shetal_P
New Contributor II
3 years ago

Excel Macro Enabled Workbooks (.xlsm) not working within OneStream Spreadsheet

Hi all,

Currently we are trying to automate some offline reporting processes via excel macro enabled workbook (.xlsm). Basically these files contain both XFGetCell and regular excel formulas and then there is a macro in place which combines the numbers from all different tabs into one, removes all unnecessary lines and then generates a OneStream load file.  These excel files are very large in size and they take forever to refresh from the desktop. The same files are refreshing much faster if we run them directly into OneStream Spreadsheet (Application>>Spreadsheet). So no issue with the refresh within OneStream application but as soon as I save the *.xlsm file within OneStream, the file got corrupted, and I'm not able to open the same file again from my desktop. See the error screenshot below. Has anyone seen this issue before and know how to fix it? Please let us know if you know the answer.  Thank you in advance!!!

 

Shetal Patel

Shetal.Patel@xylem.com

OneStream Admin

Excel Add In
  • tomdarnall's avatar
    tomdarnall
    New Contributor III
    3 years ago

    Shetal,

    I had tried something similar a long time ago and got the same results.  It will involve a little more effort, but perhaps move your macro in a separate file.  You could then refresh the main .xlsx workbook in OS, save it to your desktop, then have the .xlsm file look to the saved file to do the formatting work.  I do something similar where I have a book generating an excel file, then use a separate .xlsm file to do heavy formatting on that OS generated book.

    Good luck!

    Tom