I am experiencing a strange behaviour with Active Forms. Maybe someone ever had the same problem and can give me advice?
I am using Excel 2010 and TM1 10.1.1.
So I use a workbook with 7 worksheets. Sheet 1 contains a list of our departments. The other 6 Sheets are Active Forms which load data from the TM1 Cubes depending on the selected department. So what I am doing: In the list I click some departments and start a macro:
for each department clicked: add a new workbook, copy the 6 active forms into 6 new worksheets and set the department name on them. then run over the sheets and call Application.run "tm1refresh". it works well for like 5 workbooks/departments. if I choose more, some of the Active Forms are just not loaded. Which ones, this is kinda random.
So i thought it is maybe some ram/storage problem. So what I tried: open only 1 new workbook. calculate all active forms by application.run "tm1.refresh", then save a value copy of the workbook and close it. Then create the next workbook and so on.
This works fine, as long as I dont really save the workbooks by using Workbook.saveas , but only workbook.exportasfixedformat as PDF. This way i can create endless PDFs without any of the Active Forms stopping to load.
As soon as I use workbook.saveas (doesnt matter if xls, xlsx, xlsm.. whatever) only the first workbook gets to load correctly. Then the script saves it via .saveas and closes it. As soon as it creates the next workbook, no active form will be loaded. The script just runs over the sheets calling application.run "tm1refresh" but the Active Form wont load. It normally takes 10-20 seconds to load one, and in this case it just hops over it in a wink. However, when I manually stop the script and just rightclick in the window and go for Active Form > Reload this , it works fine and loads.
To me it looks that the .saveas or too many open workbooks somehow corrupt the tm1-excel-add-in. Any help would be appreciated. If you need any other info, please tell me.
Is there any logfile or something where I could see what happens when I call the Application.run "tm1refresh"?