Scheduled Reports

[art83]

Hello All,

I am using VBA in Excel 2010 (32-bit) to log into TM1 (10.2.2), refresh a predefined Perspectives report, print to PDF and then email that PDF to designated users. The macro is initiated upon opening the workbook. All of the code is working exactly as intended without any issues. The problem comes when attempting to schedule this through either a TI process and/or Windows Task Scheduler.

If the Service Account is not logged in to the server, the Excel instance will hang, even when Task Scheduler has been configured to "Run whether user is logged on or not". If I leave the Service Account logged in (even if locked/disconnected), I can successfully run the report via TI. This is only an issue when invoking the TM1 add-in; running a macro file that does not use the add-in will run successfully whether or not the Service Account is logged in.

I spent the better part of last night researching this without finding any concrete answers. There does not appear to be any security issues (at one point I desperately gave unrestricted access to all Excel macros and every other security possibility I could think of). Once I unequivocally determined that it was the TM1 add-in, I tried unloading/loading the add-in in my VBA code and running small pieces of code at a time. I was never able to get past the N_Connect call because it wasn't recognizing it as valid (again, because it is not loading the add-in). Just to be clear, if I log in as the Service Account (or my own account) and run the file manually, it works exactly as intended. If I remove the TM1-specific references from my code (i.e. TM1 Add-in not needed) and run the file via TI without the Service Account logged in, it works without issue.

Is this the expected behavior? Must the Service Account be logged in in order to run an Excel file that utilizes the TM1 add-in? Can I change some settings or code around this? I realize the answer might be just leave the Service Account logged in, but that seems fraught with peril given that several people have access to the server/service account. Just looking for suggestions from those that have been there. Also I am running Windows Server 2012 R2.

Thanks in Advance.

P.S. Long time reader (thanks for all knowledge you share), first post (please let me know if I've left anything out).

[wissew]

Sounds like you are hitting everything correctly. When you launch Excel do you have Perspective launch without any macro messages and see the IBM splash screen?
I am assuming you have perspectives added as a addin to Excel and macro security is se to low. Have you gone into the Excel trust center settings? You need to go to the macro settings and disable all macro notifications. Also, select trust all VBA project module.

[art83]

wissew,

Thanks for the reply. Yes, my trust center settings allow for the macro to run without any messages. When I run the file manually it behaves as expected without any prompts. I did try adding the trust all VBA project module, per your suggestion, but to no avail.

Update: This morning I was able to replicate this scenario (using the same macro file) on another server and it works without issue. The only difference I am finding is that on the server where I'm experiencing the issue, we are using Excel 2010. On the replicated server we have Excel 2013. I was wondering if this would have anything to do with the expired certificate issue http://www-01.ibm.com/support/docview.w ... wg21672487. Although I AM using the workaround with Trusted Locations. Thoughts?

[wissew]

Workarounds:

There are several different possible workarounds:

Remember, these are possible workarounds. The best solution is to use Excel 2013. I had used Method #1 in a prior role and that resolved it in Excel 2007. If you can't upgrade the existing server, Run it from the replicated environment