TM1 Forum

Code: Select all

Application.Run "N_CONNECT", "TM1_MODEL_NAME", "admin", ""

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

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 ) ;

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