Cafe VBA TM1 Automation

[image2x]

Is Cafe's VBA automation only for BI objects and not for TM1 worksheet objects/references like dbrw's and flexviews?

The documentation does not make this distinction but the login function appears to want a BI server reference and not a TM1 app server URL.

Please tell me this is not true!

Thanks.

[image2x]

As I've yet to find an official method to automate Cafe login and refresh with TM1 objects, I had to go down the emulation path.

Below are two AutoIT scripts that get the job done. The first is the actual Cafe refresh script while the second is for maintaining a username/hashed-password .ini file (not really secure but better than nothing).

Code: Select all

#include <Crypt.au3>
#AutoIt3Wrapper_UseX64= N

; Usage: cafe_refresh_workbook.exe c:\path\to\workbook.xlsx
; Cafe must be configured for "Load most recently used system and package" /w last use in same context as workbook
; Workbook.xlsx must be configured and saved with Manual calculation flagged
; Script will Open Workbook, Login to Cafe, Refresh Data, Save Workbook, and Quit Excel
; Compile to obfuscate username/password info

;$sWorkbook = "C:\temp\Book1.xlsm"  ; Enable for debugging
$sWorkbook = $CmdLine[1];

$inipath = "c:\path\to\autologin.ini"
$private_pass = 'SuperSecretPassword'
$userid = IniRead($inipath, "User_Info", "User_ID","")
$enc_pass = IniRead($inipath, "User_Info", "Password", "")
$password = StringEncrypt ( False, $enc_pass, $private_pass )

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")    ; Initialize a COM error handler
$oExcel = ObjCreate("Excel.Application")
if @error Then
  Msgbox (0,"ExcelTest","Error Getting an active Excel Object. Error code: " & hex(@error,8))
exit
endif

; Necessary as Excel Automation doesn't fully process add-ins even though they may appear to be loaded
$oExcel.Application.RegisterXLL ("C:\Program Files (x86)\ibm\cognos\Cognos for Microsoft Office\CognosOfficeBI.xll")
$oExcel.Application.RegisterXLL ("C:\Program Files (x86)\ibm\cognos\Cognos for Microsoft Office\CognosOfficeTM1.xll")

$oExcel.Visible = 1
$oExcel.WorkBooks.Open($sWorkbook)

; SendKeys approach
; Couldn't get IE.au3 lib to work with IE Class inside of WindowsForms Class
; If some knows a better method, by all means, spill the beans
Sleep (5000)
Send ("!B")
Send ("!B")
Sleep (4000)
Send ("{TAB}")
Sleep (2000)
Send ("{Enter}")
Sleep (2000)
Send ($userid)
Send ("{TAB}")
Send ($password)
Send ("{TAB}")
Send ("{Enter}")
Sleep (2000)
Send ("!B")
Send ("!R")
Sleep (1000)
Send ("{Enter}")

Sleep (10000)  ; Adjust as necessary for worst case timing of Cafe refresh function
$oExcel.ActiveWorkbook.Save
$oExcel.ActiveWorkbook.Close
Sleep (2000)
$oExcel.Quit

; Custom defined error handler
Func MyErrFunc()

  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"    & @CRLF  & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description  & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "       & @TAB & hex($oMyError.number,8)  & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "       & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile     & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )

Endfunc

Func StringEncrypt($bEncrypt, $sData, $sPassword)
    _Crypt_Startup() ; Start the Crypt library.
    Local $sReturn = ''
    If $bEncrypt Then ; If the flag is set to True then encrypt, otherwise decrypt.
        $sReturn = _Crypt_EncryptData($sData, $sPassword, $CALG_RC4)
    Else
        $sReturn = BinaryToString(_Crypt_DecryptData($sData, $sPassword, $CALG_RC4))
    EndIf
    _Crypt_Shutdown() ; Shutdown the Crypt library.
    Return $sReturn
EndFunc   ;==>StringEncrypt

Code: Select all

#include <Crypt.au3>
#include <MsgBoxConstants.au3>

$inipath = "c:\path\to\autologin.ini"

$UserID = InputBox("Input Your User ID", "Input Your User ID")

if @error =1 Then Exit;

$password = InputBox("Input Your Password", "Input Your Password")

if @error =1 Then Exit;

;$UserID = IniRead($inipath, "User_Info", "User_ID","")
;$enc_pass = IniRead($inipath, "User_Info", "Password", "")

Local $sEncrypted = StringEncrypt(True, $password, 'SuperSecretPassword')

msgbox(1,"","Your encrypted password is " & $sEncrypted)

Local $password = StringEncrypt(False, $sEncrypted, 'SuperSecretPassword')
msgbox(1,"","your decrypted password is " & $password)

IniWrite($inipath, "User_Info", "User_ID", $UserID)
IniWrite($inipath, "User_Info", "Password", $sEncrypted)


Func StringEncrypt($bEncrypt, $sData, $sPassword)
    _Crypt_Startup() ; Start the Crypt library.
    Local $sReturn = ''
    If $bEncrypt Then ; If the flag is set to True then encrypt, otherwise decrypt.
        $sReturn = _Crypt_EncryptData($sData, $sPassword, $CALG_RC4)
    Else
        $sReturn = BinaryToString(_Crypt_DecryptData($sData, $sPassword, $CALG_RC4))
    EndIf
    _Crypt_Shutdown() ; Shutdown the Crypt library.
    Return $sReturn
EndFunc   ;==>StringEncrypt