Automating TM1 in VBA

Wim Gielis
MVP
Posts: 2472
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
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
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

User avatar
paulsimon
MVP
Posts: 756
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: 2472
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
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
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

User avatar
Steve Rowe
Site Admin
Posts: 2057
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: 2472
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
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
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

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: 2472
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
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
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

Wim Gielis
MVP
Posts: 2472
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
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
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

User avatar
paulsimon
MVP
Posts: 756
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: 2472
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
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
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

User avatar
paulsimon
MVP
Posts: 756
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: 2472
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
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
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

ofintm1
Posts: 14
Joined: Tue Jun 21, 2016 4:26 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: Automating TM1 in VBA

Post by ofintm1 » Tue Jun 30, 2020 9:28 am

Hi Wim and All,

I believe I'm having the same issue automating TM1 in VBA, did you ever find a solution to this or are there any other options for automating TM1 reports to run on a schedule without any human interaction?

The approach we have so far is:

1) VBA that connects to TM1 as an admin account, refreshes and saves down report and then disconnects from TM1.
2) VBS that opens Excel, calls above macro and then closes. Note I got this working with n_connect only when setting the Excel app to be visible otherwise it fails to connect to TM1.

Everything works when the VBS is run manually but fails when the script is called via a TI process. I don't have access to the service account or Windows task scheduler to troubleshoot but can instruct IT on what to do.

Thanks
Oli

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

Re: Automating TM1 in VBA

Post by Wim Gielis » Tue Jun 30, 2020 10:52 am

Hi Oli,

I moved away from this approach if the reports should be generated without manual intervention.
Only when a user can generate the report, I still allow it. If we cannot be sure that it works reliably, it's better to not use it.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

ofintm1
Posts: 14
Joined: Tue Jun 21, 2016 4:26 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: Automating TM1 in VBA

Post by ofintm1 » Tue Jun 30, 2020 2:44 pm

Thanks Wim. Just to confirm my understanding, you're saying there is no known reliable approach to full report automation from TM1? :cry:

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

Re: Automating TM1 in VBA

Post by Wim Gielis » Tue Jun 30, 2020 3:44 pm

ofintm1 wrote:
Tue Jun 30, 2020 2:44 pm
Thanks Wim. Just to confirm my understanding, you're saying there is no known reliable approach to full report automation from TM1? :cry:
I wouldn’t know if it is definitively a no-go but AFAIK you cannot get it working reliably and consistently if it is unwatched and without user “context”. I only investigated VBA and VBScript. Maybe other more advanced programming languages allow you to do so.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

User avatar
paulsimon
MVP
Posts: 756
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 Jun 30, 2020 10:26 pm

Hi Wim

I will have a look tomorrow. We have the following working as part of our system

a) TI Process uses ExecuteCommand to run a DOS Batch
b) The DOS Batch runs a VB Script
c) The VB Script opens an Excel .xlsm and runs a macro, passing parameters

In our case, the macro only merges a CSV file output by the TI into another xlsx workbook and a later TI copies this across to our web server. However, I can't see any reason why the macro couldn't connect to TM1 and retrieve data then paste special value it which is presumably what you want to do.

I remember that there were certain tricks that I had to go through to get a-c to work, so if I can work out what I did I will post them.

Regards

Paul

User avatar
ykud
Posts: 103
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: Automating TM1 in VBA

Post by ykud » Wed Jul 01, 2020 12:14 am

Wim Gielis wrote:
Tue Jun 30, 2020 3:44 pm
ofintm1 wrote:
Tue Jun 30, 2020 2:44 pm
Thanks Wim. Just to confirm my understanding, you're saying there is no known reliable approach to full report automation from TM1? :cry:
I wouldn’t know if it is definitively a no-go but AFAIK you cannot get it working reliably and consistently if it is unwatched and without user “context”. I only investigated VBA and VBScript. Maybe other more advanced programming languages allow you to do so.
Just to add another data point: I have 4 projects where VBS is used to generate reports (connect to TM1, refresh the report, convert to values, the usual song and dance) that have been live for a at least a couple of years (5 years for the longest-running to date). The largest one generates ~500 reports daily. Most went through a couple of versions of Windows / TM1 / Excel without a lot of drama. So it's possible, although not the most interesting thing in the world, Excel is notoriously bad 'server-side' execution tool.

The overall logic is something like this:
Screen Shot 2020-07-01 at 10.11.15 am.jpg
Screen Shot 2020-07-01 at 10.11.15 am.jpg (204.72 KiB) Viewed 498 times
Cheers,
Yuri

User avatar
paulsimon
MVP
Posts: 756
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 » Wed Jul 01, 2020 8:54 am

Hi Wim

Looking at my code the key thing is that for the VBS to work you need to have the following folder

C:\Windows\SysWOW64\config\systemprofile\desktop

Below is some DOS Batch that creates this if it doesn't exist. We found that it worked better to get the TI to run a DOS Batch (.BAT) file which in turn runs the VBS, rather than running the VBS directly.

Code: Select all

REM Run a VBScript
REM %1 is the full path to the VBScript .vbs file
REM %2 is the first string parameter to the VBS
REM %3 is the second string parameter to the VBS
REM %4 is the third string parameter to the VBS
REM %5 is the fourth string parameter to the VBS
REM %6 is the fifth string parameter to the VBS


REM NB !!!
REM For a TI process to run a VBA that runs a VBA
REM which saves an Excel Workbook, you need to 
REM create the folder desktop under
REM C:\Windows\SysWOW64\config\systemprofile\
REM ie
REM C:\Windows\SysWOW64\config\systemprofile\desktop
REM Otherwise it will open Excel but just get stuck
REM and never finish

SET SPECIALPATH=C:\Windows\SysWOW64\config\systemprofile\desktop
SET WOWPATH=C:\Windows\SysWOW64\config\systemprofile

IF NOT EXIST %SPECIALPATH% GOTO CREATESPECIALPATH
GOTO RUNVBS

:CREATESPECIALPATH

IF NOT EXIST %WOWPATH% GOTO CANNOTPROCEED

C:
CD %WOWPATH%
MD desktop
GOTO RUNVBS

:CANNOTPROCEED

REM Get the date in YYYY-MM-DD Format

@echo off

  echo. | date | FIND "(mm" > NUL
  If errorlevel 1,(call :Parsedate DD MM) Else,(call :Parsedate MM DD)
  goto :EOF
 :Parsedate ----------------------------------------------------------
  For /F "tokens=1-4 delims=/.- " %%A in ('date /T') do if %%D!==! (
     set %1=%%A&set %2=%%B&set YYYY=%%C
   ) else (
     set DOW=%%A&set %1=%%B&set %2=%%C&set YYYY=%%D)

(Set THIS_DATE=%YYYY%-%MM%-%DD%)

@echo on

REM Make the Log File Name

SET LOG=.\zTD_RunVBS_%THIS_DATE%.log

ECHO Cannot run %1 %2 %3 %4 %5 %6 as Path %WOWPATH% does not exist so cannot create desktop folder under it >> %LOG% 

:RUNVBS
CMD /C C:\Windows\system32\wscript.exe %1 %2 %3 %4 %5 %6
--------------------------------------------------------------------------------------

Other factors might be

Our TM1 Server runs under a Service Account that has Local Admin Rights this therefore gives it rights to the folder where the VBS and XLSM are stored.

I logged on as this Service Account, opened Excel and set the folder where the VBS and VBA are stored as a Trusted Location to avoid issues with Macro Security.

If you want to use Macros like N_Connect you will need to ensure that the TM1P.XLA is set to load in Excel when running under this account.

Check that the location of the WSCRIPT.EXE is on the Path.

---------------------------------------------------------------------------------

The code fragment used to call the DOS Batch from TI is

Code: Select all

vCmd = 'CMD /C "E:\Data\XXX\Batch\zTD_RunVBS.bat" ' | 
                           'E:\Data\XXX\VBSVBA\zTD_OpenXLSMRunMacroWithParams.vbs ' | 
                           'E:\Data\XXX\VBSVBA\XXXXX.xlsm ' | 
                           'XXXXMacroToRunXXXX ' | 
                           'XXXXParamToMacroXXXX;

ExecuteCommand( vCmd , 1 ) ;
The code in the vbs file is

Code: Select all

'Open the given Macro Enabled Workbook
'and run the given Macro in it
'passing in the given parameter(s) (up to 3)
'which will be treated as being of type string
 
'Parameter to a VBS are stored in an array
'with starting index at 0

'The first parameter must be the full path to the
'Excel Workbook
'The second parameter must be the name of the macro
'to be run.
'The third and subsequent parameters are optional
'arguments to the macro
'There is no checking that the Macro exists or has
'the required parameters


' NB !!!
'For a TI process to run a VBA that runs a VBA
'which saves an Excel Workbook, you need to 
'create the folder desktop under
'C:\Windows\SysWOW64\config\systemprofile\
'ie
'C:\Windows\SysWOW64\config\systemprofile\desktop
'Otherwise it will open Excel but just get stuck
'and never finish.

Function TwoPad(N)
	IF N < 10 Then
		TwoPad = "0" & CSTR(N)
	Else
		TwoPad = CSTR(N)
	END IF
End Function

Function sFileTimeStamp(DT)
	sFileTimeStamp = _
		TwoPad(Year(DT)) & "_" & _
		TwoPad(Month(DT)) & "_" & _
		TwoPad(Day(DT)) & "_" & _
		TwoPad(Hour(DT)) & "_" & _
		TwoPad( Minute(DT)) & "_" & _
		TwoPad( Second(DT))
End Function

Sub LogError(msg)
	logfile.writeLine("Error when called with Workbook " & sXLSM & " to run Macro " & sMacro & " - " & msg)
	WScript.Quit
End Sub

Dim fs , logfile

Set fs = createobject("Scripting.FileSystemObject")

Dim timestamp 

timestamp = sFileTimeStamp(now())

'WScript.Echo timestamp

Dim logpathfile

logpathfile = "E:\Data\BIS\Logging\VBSLog_" & timestamp & ".log"

Set logfile = fs.CreateTextFile(logpathfile)

Dim sXLSM, sMacro , Args , iNumArgs

Set Args = Wscript.Arguments
iNumArgs = Args.count

if iNumArgs < 2 then
	LogError"Invalid number of Arguments " & CSTR(iNumArgs)
END IF

sXLSM = Args(0)
sMacro = Args(1)

if not(fs.FileExists(sXLSM)) then
	LogError"Excel Macro Enabled Workbook " & sXLSM & " does not exist."
END IF
	 
IF iNumArgs > 5 then
	LogError"Sorry, this can only cope with 3 parameters to the Excel Macro Enabled Workbook " & sXLSM & " for Macro " & sMacro
END IF	 
	 
Dim xlApp

Set xlApp = CreateObject("Excel.Application")

IF xlApp is nothing then
	LogError"Cannot start Excel - is it installed?"
END IF 

xlApp.DisplayAlerts = False

Dim xlBook

Set xlBook = xlApp.Workbooks.Open(sXLSM, 0, True)

IF xlBook is nothing then
	LogError"Cannot open workbook " & sXLSM & " in Excel"
end if

Dim iNumParams

'Work out number of parameters provided for the 
'Excel Macro in the Excel XLSM Workbook
iNumParams = iNumArgs - 2

If iNumParams = 1 THEN
	xlApp.Run sMacro ,CSTR( Args(2))
ELSEIF iNumParams = 2 THEN
	xlApp.Run sMacro ,CSTR( Args(2)),CSTR( Args(3))
ELSEIF iNumParams = 3 THEN
	xlApp.Run sMacro ,CSTR( Args(2)),CSTR( Args(3)),CSTR( Args(4))
END IF	
		
xlBook.Close False

set xlBook = Nothing

xlApp.Quit

Set xlApp = Nothing

'WScript.Echo "Finished."

'If it gets here there were no errors so delete the log file
logfile.close
fs.DeleteFile logpathfile

WScript.Quit

(Best to look at the VBS is something like Notepad++

Hope this helps

Regards

Paul Simon

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

Re: Automating TM1 in VBA

Post by Wim Gielis » Thu Jul 02, 2020 11:01 am

Thank you Paul, I will go through the material although I think I can tick many boxes here.
The need is not very urgent anymore so it can take a while for me to pick it up again but thanks a lot !
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

Post Reply