Page 1 of 1
TM1 Reports
Posted: Wed May 06, 2015 4:25 am
by Abinaya
Hi,
I am running about 100 Excel based TM1 reports using a macro and it crashes every time after it runs 3 reports.
I get the following message "Microsoft Excel has stopped Workings"
How can I improve the performance? Is it RAM is the issue or problem with the template?
Any suggestions welcome.
regards,
Abi
Re: TM1 Reports
Posted: Wed May 06, 2015 4:33 am
by RJ!
I'm assuming your using "Print Report" function?
Have you tried to run the report for the 3rd & 4th report manually?
Re: TM1 Reports
Posted: Wed May 06, 2015 7:45 am
by Abinaya
No not the print report function. There is no issue with running the template manually.
Through a macro a TM1 based Excel report is opened and refreshed and then hard coded and closed.
This is run on a loop.
I think it crashed because the Clipboard is full or TM1 retrieving data to RAM and RAM is full?
Re: TM1 Reports
Posted: Wed May 06, 2015 7:57 am
by declanr
Have you monitored the RAM as it is running?
What is the VBA Code?
Have you tried stepping through the code step by step to see what line is killing it?
Re: TM1 Reports
Posted: Wed May 06, 2015 12:06 pm
by Abinaya
Not monitored RAM. But I don't see any thing wrong with the code since it's working for first three reports.
Code: Select all
Dim i As Integer
i = 4
Do While (Range("B" & i).Value <> "")
Range("B" & i).Select ' selects the report name and pastes into the open template
Selection.Copy
Workbooks.Open Filename:= _
"L:\File.xlsm"
Sheets("Cover Page").Select
Range("G6:M6").Select
ActiveSheet.Paste
Application.Run "TM1RECALC"
Application.Run "'File.xlsm'!Refresh" ' another macro the refreshes and hard codes the report
ActiveWorkbook.SaveAs Filename:= _
"L:\Report - " & Range("E15").Value & ".xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Windows("Report - " & Range("E15").Value & ".xlsm").Close
Windows("File Macro.xlsm").Activate
i = i + 1
Loop
it would not crash Excel. I think the issue is with TM1.
Re: TM1 Reports
Posted: Wed May 06, 2015 12:15 pm
by declanr
Crashing does seem unlikely but with the filename being a dynamic part of the code it isn't just the case that it's trying to use special characters etc in the name and therefore can't save it?
Re: TM1 Reports
Posted: Wed May 06, 2015 10:28 pm
by RJ!
Just confirming that you have been able to walk through the macro step by step and it works? (i.e. Hitting F8 though the complete sub?)
We had issues with users changing our XLSM & XLSX files back to XLS which would crash our VBA, not sure if that is your issue?
Re: TM1 Reports
Posted: Wed May 06, 2015 11:36 pm
by Abinaya
declanr wrote:Crashing does seem unlikely but with the filename being a dynamic part of the code it isn't just the case that it's trying to use special characters etc in the name and therefore can't save it?
No problem in saving for the first three reports. There are no special characters in saving file name. All the names are listed in a range and it is just copying from there.
Re: TM1 Reports
Posted: Wed May 06, 2015 11:39 pm
by Abinaya
RJ! wrote:Just confirming that you have been able to walk through the macro step by step and it works? (i.e. Hitting F8 though the complete sub?)
We had issues with users changing our XLSM & XLSX files back to XLS which would crash our VBA, not sure if that is your issue?
It's working perfectly for the first three report. Only issue is it can't handle the volume of reports.
Re: TM1 Reports
Posted: Thu May 07, 2015 10:41 pm
by paulsimon
Hi
I used to run 5000 agency reports a month from TM1 using a VBA Macro so I don't think the limitation is in TM1.
Your VBA has a comment mentioning calling a routine which is presumably doing a Paste Special Values (PSV)?
If so you may need some code to clear the clipboard after each copy and PSV. I use a little routine with the Windows API to do this. However, a simpler way can be to just copy a single cell range after the main copy, so that the set of copied cells is small.
I would also put some DoEvents into your code to give Excel a breather.
Just to make it easier for us, try indenting your code too.
Another thing to try would be processing the elements in a different order to see if that helps. It might be something in the third one that causes an error that causes the problem on the 4th. I have seen odd issues when people have used illegal characters in Element Names, Sheet Names and Worksheet Names.
Regards
Paul Simon
Re: TM1 Reports
Posted: Sat May 09, 2015 6:36 am
by babytiger
Is your TM1 base report an ActiveForm report?
And on the base report, do you have any or lots of conditional formatting rules?
Base on experience, with the newer version of Excel, TM1 activeforms does not handle conditional formatting well.
Re: TM1 Reports
Posted: Tue May 12, 2015 4:36 am
by Abinaya
babytiger wrote:Is your TM1 base report an ActiveForm report?
And on the base report, do you have any or lots of conditional formatting rules?
Base on experience, with the newer version of Excel, TM1 activeforms does not handle conditional formatting well.
I am using Excel 2010 and TM19.5.2. It has a combination of Active Form and normal TM1 sheets. It does have very small conditional format in one sheet.