TM1 Reports

[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

[RJ!]

I'm assuming your using "Print Report" function?

Have you tried to run the report for the 3rd & 4th report manually?

[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?

[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?

[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.

[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?

[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?

[Abinaya]

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.

[Abinaya]

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.

[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

[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.

[Abinaya]

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.