Automating TM1 in VBA

Post Reply
Wim Gielis
MVP
Posts: 2041
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Automating TM1 in VBA

Post by Wim Gielis » Tue Jul 24, 2018 1:33 pm

Hello all,

Strange case I am encountering.

I have a macro in an xlsm file in Excel that would:
- open TM1p.xla
- log on to TM1 with an admin user
- open a report from the Applications folder
- change the report data at the top of the report
- refresh (= rebuild Active form)
- save the report as PDF
- log out from TM1

Opening the xlsm file is done with a simple vbscript file.

If the vbscript file is opened manually, with different Windows users, it always works. The PDF is created and contains the correct data.
If I use ExecuteCommand in TI to open up the VBS file, it starts, the xlsm file is opened, a bunch of other stuff is done but it will not allow me to use:

Code: Select all

Application.Run "N_CONNECT", "TM1_MODEL_NAME", "admin", ""
("TM1_MODEL_NAME" is replaced here, it contains the customer name.)

It somehow stops. But, executing this line of code in Excel with the Windows user under which the TM1 service runs, it works fine.

The error is 1004 which indicates that the macro is not available in the workbook. TM1p.xla is loaded fine at that time.

Is there a change in automating TM1 Perspectives from outside Excel perhaps ?
Excel 2016, recent TM1 version.

A Windows scheduled task that executes the vbscript file has the same issue ! It does not allow me to log on to TM1 using N_Connect.
Security mode 1 is used, native TM1 security.

I recreated the 2 Desktop folders in a Windows subdirectory, this is not the problem.

Thanks a lot !

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

User avatar
paulsimon
MVP
Posts: 685
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Automating TM1 in VBA

Post by paulsimon » Tue Jul 24, 2018 7:38 pm

Hi Wim

Is it possible that Excel Macros are being disabled when Excel is started from TI or the Task Scheduler? Have you looked at the Macro Security settings in Excel?

Another way of achieving this might be to use the timer facility in Excel to run the export at a certain time of day.

This will wait for 10 seconds

Application.Wait (Now + TimeValue("0:00:10"))

This will wait until 10pm

Application.Wait ( TimeValue("22:00:00"))

Regards

Paul Simon

Wim Gielis
MVP
Posts: 2041
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Automating TM1 in VBA

Post by Wim Gielis » Tue Jul 24, 2018 9:14 pm

Hi Paul

Thanks for replying.

I turned off/disabled about any setting I find in the Excel Options. Macros run with no restrictions at all. Trusting access to the VBA project object model, no yellow bars at the top, etc.
If I run the vbs manually, it opens Excel, does all the stuff (including N_Connect to log on to TM1), and closes correctly.
Executing the vbscript from Task Scheduler, or TI, or PowerShell does not allow me to log on to TM1.

I'm not a fan of Application.Wait, as then Excel will run the whole time. Correct me if I'm wrong.
The desired outcome is a tool (I hope it's TI) launches vbs, then VBA, which creates a PDF, and closes everything.
Manually, this is all done. Now automating the whole thing ;-)

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

User avatar
Steve Rowe
Site Admin
Posts: 1897
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Automating TM1 in VBA

Post by Steve Rowe » Tue Jul 24, 2018 9:28 pm

A few things to check but no dramatic insights.

Is trust programmatic access to VBA checked for the user concerned? (Cross posted)
Which mode of security, assuming mode 1?
Which version exactly of perspectives, I'm pretty sure some "stuff" changed in the PA build.
Did you have this working before / do you think its version related? The approach you outline is not super unusual...
Maybe excel version related, are able to test on an older version of Excel before everything got so complicated?
ohhh, maybe, I seem to remember that there are restrictions on admin / blank password but I'm assuming your script sample has been changed?
Try a different user.

that's it from me....

Wim Gielis
MVP
Posts: 2041
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Automating TM1 in VBA

Post by Wim Gielis » Tue Jul 24, 2018 9:46 pm

Steve Rowe wrote:
Tue Jul 24, 2018 9:28 pm
A few things to check but no dramatic insights.

Is trust programmatic access to VBA checked for the user concerned? (Cross posted)
Which mode of security, assuming mode 1?
Which version exactly of perspectives, I'm pretty sure some "stuff" changed in the PA build.
Did you have this working before / do you think its version related? The approach you outline is not super unusual...
Maybe excel version related, are able to test on an older version of Excel before everything got so complicated?
ohhh, maybe, I seem to remember that there are restrictions on admin / blank password but I'm assuming your script sample has been changed?
Try a different user.

that's it from me....
Thanks Steve, that's a couple of ideas I did not explore yet.

Programmatic access is trusted, security mode 1.
It's an early PA version (11.0.0.xxx of late December 2016)
I have a similar setup at other customers and it works, this is the first attempt at this customer. At other customers with older Excel and TM1 versions it works fine.
I set up a new TM1 instance with no password for Admin, I will definitely try when a password is used.
I used several Windows users to test manually, also several users under which the TM1 service runs. Also, a separate TM1 instance.

Office 365 is used, the account for that is not the same as the service account. IT told me that I cannot use the service account in Excel, nor vice versa.

Thanks, to be continued.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

jrizk
Posts: 48
Joined: Thu Nov 19, 2009 10:38 pm
OLAP Product: Tm1
Version: 10.2.2
Excel Version: 2010

Re: Automating TM1 in VBA

Post by jrizk » Wed Jul 25, 2018 12:23 am

I had the same issue trying to automate Excel/Tm1 to produce excel based reports, particularly when logged off the server, and I didn't want to keep changing the DCOM settings for Excel to be able to use it every session. The 2 Desktop folders in a Windows didn't work either so I'm not sure that had any effect.

I'm not sure that the issue is specifically related to N_CONNECT. Seems that Office assumes that the applications run under under an interactive desktop, which is why it works when the script is run manually but not through automation or TI.

A couple of things to consider for a workaround which might help your situation. Try creating the schedule that points to the vbscript in Task Scheduler under the group Administrators, if you have the rights to do so (though this is not generally accepted as best practice due to security/exposure the Administrator group has). You can also try with other service accounts.

You can test this by:

1. running the schedule manually in task schduler

2. command prompt (admin):

schtasks /Run /TN "Your Task Name Here"

3. TI. Running ExecuteCommand that points to the vbscript won't work but pointing it to the scheduled task should:

sCmd = 'cmd /c schtasks /Run /TN "Your Task Name Here"';
ExecuteCommand (sCmd, 1);

4. Schedule 1 and 3 to run when logged off.

As an aside i also needed to run TI's before running the Excel/Tm1 updates/reports - so the script had to run after the TI's completed. This became a bit more involved so I may post something on this later.

Hope this helps
J.Rizk
Tm1 for everyone

Wim Gielis
MVP
Posts: 2041
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Automating TM1 in VBA

Post by Wim Gielis » Thu Jul 26, 2018 3:13 pm

I got it working !
Which is not necessarily the same as ‘I understand why and what I’m doing’.

I’ll post up when I get the chance and after implementing with the customer.

Thanks

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

Wim Gielis
MVP
Posts: 2041
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Automating TM1 in VBA

Post by Wim Gielis » Sat Mar 16, 2019 1:59 pm

Hello,

I still cannot get it to work. See for an updated topic:
https://www.tm1forum.com/viewtopic.php?f=3&t=14157

There is also a Word document including things to check and try.

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

User avatar
paulsimon
MVP
Posts: 685
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Automating TM1 in VBA

Post by paulsimon » Sat Mar 16, 2019 5:03 pm

Hi Wim

When running as TI it will be running under the Service Account that the TM1 Service runs under. If this is still the Local System Account then it probably won't work. If it is running under a domain account, then have you checked that this has access to the file location?

If you just want this to run regularly, then setting up a Scheduled Task might work better than a TI process run from a Chore

Regards

Paul

Wim Gielis
MVP
Posts: 2041
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Automating TM1 in VBA

Post by Wim Gielis » Sat Mar 16, 2019 5:21 pm

Hello Paul,

I am not using a TI for now, but a scheduled task.

The testing that I am currently doing in my newest topic:
- If I schedule the task to run at a certain point in time, so scheduled, and I am logged on: it works fine.
- If I schedule the task and I log off (and everyone else is logged off): it does not work
- I simplified the VBA coding in the Excel file to just create a text file on the server itself on a disk there. No TM1 stuff whatsoever
- The task runs with an account that is part of the 'Administrators' group

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

User avatar
paulsimon
MVP
Posts: 685
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Automating TM1 in VBA

Post by paulsimon » Sat Mar 16, 2019 8:59 pm

Hi Wim

We do a similar thing with a Scheduled Task. Have you ticked the run with Highest Privileges flag? That is equivalent to saying Run as Administrator, which might cure your problem.

Regards

Paul Simon

Wim Gielis
MVP
Posts: 2041
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Automating TM1 in VBA

Post by Wim Gielis » Sat Mar 16, 2019 10:23 pm

Hi Paul,

I think that I tested about each permutation of all the options:
- users
- highest privileges
- 'Run whether user is logged on or not'
- Task compatibility 2008/2000-2003...
- Excel without vbscript before
- Logged on or not
- UNC paths
- ...

Still no joy, at now 2 different customers.

But based on the replies I derive that it should somehow be possible. What I read on the internet is that, to automate Excel, someone needs to be logged on to create a context in which the interactive automation can take place. And that checking 'Run whether user is logged on or not' implies that the interactive mode cannot take place ==> hence, someone needs to be logged on.

Environment: Windows Server 2012 R2 Standard, 64 bit. 64 GB RAM
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

Post Reply