Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have an issue with the tSystem component on remote engine.
My use case is the following : I have to parse an Excel file, but it has a picklist and dynamic cells which update accordingly. So I loop over the picklist values. At each iteration:
1/ I use a tJava to update the picklist pragrammatically and force Excel to update its buffer (cell values) at opening
2/ A 1st tSystem calls a script "command_open.bat" which opens the Excel file
3/ A 2nd tSystem calls a script "command_save_close.bat", which calls a VBA script "CloseExcelWB.vbs" to save the buffer and close the file
You can find the content of these 3 scripts at the end below.
On the studio (Cloud data integration 7.3.1), it works fine. When I parse the data inside the excel file I can see cells are updated. But on the remote engine (v2.10.4), which is on the same machin as the studio, the 2 tSystem components actually do nothing, without error message (TMC logs below as well). So I just have the picklist updated (the tJava part), but neither of the other cells. It could be that the 2 tsystem don't have the right permissions to access the scripts stored in the file system. There is a solution which points to the right direction to give the remote engine the proper permissions :
But this works only for Gen2 remote engine as far as I understand, and we have a standard one. The first step in the solution invokes a subdirectory "default" in the remote engine install directory, which does not exist in mine :
Thank you for any help!
Best regards,
== command_open.bat :
start "C:/Program Files/Microsoft Office/root/Office16/EXCEL.EXE" "tmp.xlsm"
== command_save_close.bat:
ping localhost -n 5
"C:/Users/svc_talend/Documents/Distrigo/tool/CloseExcelWB" "tmp.xlsm"
== CloseExcelWB.vbs
Dim objExcel
Dim objWB
dim i
dim WB_To_Close
dim msg
if wscript.arguments.Count = 0 then
Call Syntax
wscript.quit
end if
WB_To_Close = wscript.arguments.item(0)
set objExcel = GetObject(, "Excel.Application")
If UCase(WB_To_Close) = "ALL" then
Do While ObjExcel.Workbooks.Count > 0
set objWB = objExcel.Workbooks(1)
objWB.Save
objWB.Close
Loop
Else
for i = 1 to objExcel.Workbooks.Count
set objWB = objExcel.Workbooks(i)
if UCase(objWB.Name) = UCase(WB_To_Close) then
objWB.Save
objWB.Close
exit for
end if
next
End If
if objExcel.Workbooks.Count = 0 then
objExcel.Quit
end if
wscript.quit
Sub Syntax()
msg = "Syntax: "
msg = msg & vbCrLF & "CloseExcelWB [All | WB Name to Close.xls?]"
msg = msg & vbCrLF & " the All argument will save and close all WBs"
msg = msg & vbCrLF & " WB Name is not case sensitive"
msg = msg & vbCrLF & " Each WB to be closed is saved first"
msg = msg & vbCrLF & " If no more WBs are in the instance of Excel, then"
msg = msg & vbCrLF & " the instance of Excel is closed, otherwise"
msg = msg & vbCrLF & " the Excel application is not closed."
msgbox msg
End Sub
== TMC logs
INFO | 2021-07-30T13:00:39.795+0000 | tipaas-jobserver-client:2.10.4 | tSystem_2 - Command to execute: 'cmd /c command_open_sellin.bat'.
INFO | 2021-07-30T13:00:39.795+0000 | tipaas-jobserver-client:2.10.4 | tSystem_2 - Setting the parameters.
INFO | 2021-07-30T13:00:39.795+0000 | tipaas-jobserver-client:2.10.4 | tSystem_2 - The command has been executed successfully.
INFO | 2021-07-30T13:00:39.795+0000 | tipaas-jobserver-client:2.10.4 | tSystem_2 - Root directory: 'C:/Users/svc_talend/Documents/Distrigo/tmp'.
INFO | 2021-07-30T13:00:39.795+0000 | tipaas-jobserver-client:2.10.4 | tSystem_2 - Executing the command.
INFO | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tSystem_1 - Command to execute: 'cmd /c command_save_close_sellin.bat'.
INFO | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tSystem_1 - Setting the parameters.
INFO | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tSystem_1 - Executing the command.
INFO | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tLogRow_12 - Content of row 1: 2021-07-30 15:00:39|20210730150025_ZX9P6|20210730150025_ZX9P6|20210730150025_ZX9P6|INSIDBOARD|LoadKPI_distriGO_toPowerSlide_SellIn|Production_8229832789887098276|4|tWarn|tWarn_9|Il repassera par la|42
INFO | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tSystem_1 - Root directory: 'C:/Users/svc_talend/Documents/Distrigo/tmp'.
INFO | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tLogRow_12 - Printed row count: 1.
WARN | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tWarn_9 - Message: Il repassera par la. Code: 42
INFO | 2021-07-30T13:00:39.811+0000 | tipaas-jobserver-client:2.10.4 | tSystem_1 - The command has been executed successfully.
