Using tcommand to get Excel data

[Joe Caverly]

While not documented very well in the help file, tcommand can be used in a VBScript (or JScript) to access data from a COM Server, such as Excel.

This example shows the VBScript code that I used, and then called, via the SCRIPT command;

@setlocal
@echo off

::Create the VBScript file
type <<- endtext > %_batchname:excel.vbs
TakeCommand.tcommand("echo Creating Object for Excel.Application")
Dim xlApp: Set xlApp = CreateObject("Excel.Application")
TakeCommand.tcommand("echo Don't show Excel.Application")
xlApp.Visible = False
TakeCommand.tcommand("echo Open workbook")
Dim wb: Set wb = xlApp.Workbooks.Open("E:\Documents\MLHealth_Corona.xlsx")
TakeCommand.tcommand("echo Open worksheet")
Dim ws: Set ws = wb.Worksheets("Sheet1")

'Assign values from cells to VBScript variables
TakeCommand.tcommand("echo Get data from the spreadsheet")
total = ws.Cells(2,7).Value
total_diff = ws.Cells(2,8).Value
recovered = ws.Cells(2,9).Value
recovered_diff = ws.Cells(2,10).Value

'Cleanup
TakeCommand.tcommand("echo Close the workbook")
wb.Close()
TakeCommand.tcommand("echo Quit the Excel.Application")
xlApp.Quit

set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing

'Set TCC Environment Variables from VBScript variables
TakeCommand.tcommand("set total=" + CStr(total))
TakeCommand.tcommand("set recovered=" + CStr(recovered))
TakeCommand.tcommand("set total_diff=" + CStr(total_diff))
TakeCommand.tcommand("set recovered_diff=" + CStr(recovered_diff))

'Display the results back to TCC
TakeCommand.tcommand("echo The next two lines displayed are from excel.vbs")
TakeCommand.tcommand("echo Total Diff Recovered Diff")
TakeCommand.tcommand("echo %total  %total_diff  %recovered      %recovered_diff")
endtext

:: Run the VBScript file
script %_batchname:excel.vbs

echo.
echo The environment variables that we set from VBScript are now in the TCC environment
echo.
echo Total Diff Recovered Diff
echo %total  %total_diff  %recovered      %recovered_diff

endlocal

Sample run;

e:\utils>excel.btm
Creating Object for Excel.Application
Don't show Excel.Application
Open workbook
Open worksheet
Get data from the spreadsheet
Close the workbook
Quit the Excel.Application
The next two lines displayed are from excel.vbs
Total Diff Recovered Diff
4368  114  3037      38

The environment variables that we set from VBScript are now in the TCC environment

Total Diff Recovered Diff
4368  114  3037      38

Posting this mainly for my future reference, but others might also find it useful.

Joe

     _x64: 1
   _admin: 1
_elevated: 1

TCC  27.00.18 x64   Windows 10 [Version 10.0.18363.1256]

 

[Kachupp]

Looks like excel needs be installed.

The following doe's not require or care if excel is installed.
Working in tcc v24 v25, @pshell function appears broken in tcc v27

set fifo=%@pshell[Import-Module PSExcel]
set lilo=%@pshell[Import-XLSX -Path %MyXlsx -Text > Excel.txt]

the module PSExcel needs to be installed in PowerShell

 

[vefatica]

I can't get off the ground with SCRIPT. Should it run any VBS script? When I try to run this one, TCC just disappears.

Set iWMI = GetObject("winmgmts:\\.\root\CIMV2")
Set colItems = iWMI.ExecQuery("SELECT ProcessId,Name,CreationDate,ParentProcessId,KernelModeTime,UserModeTime,WorkingSetSize FROM Win32_Process where Name != 'cscript.exe'")
For Each objItem in colItems
    Wscript.Echo objItem.Name
    Wscript.Echo objItem.CreationDate
    Wscript.Echo objItem.ProcessId
    Wscript.Echo objItem.KernelModeTime
    Wscript.Echo objItem.UserModeTime
    Wscript.Echo objItem.WorkingSetSize
    Wscript.Echo objItem.ParentProcessId
Next

 

[Joe Caverly]

I re-wrote your script, which *should* work;

Set iWMI = GetObject("winmgmts:\\.\root\CIMV2")
Set colItems = iWMI.ExecQuery("SELECT ProcessId,Name,CreationDate,ParentProcessId,KernelModeTime,UserModeTime,WorkingSetSize FROM Win32_Process where Name != 'cscript.exe'")
For Each objItem in colItems
    TakeCommand.tcommand("echo objItem.Name")
    TakeCommand.tcommand("echo objItem.CreationDate")
    TakeCommand.tcommand("echo objItem.ProcessId")
    TakeCommand.tcommand("echo objItem.KernelModeTime")
    TakeCommand.tcommand("echo objItem.UserModeTime")
    TakeCommand.tcommand("echo objItem.WorkingSetSize")
    TakeCommand.tcommand("echo objItem.ParentProcessId")
Next

...but I discovered, as you did, that TCC just disappears.

Testing shows it is the first two lines that cause TCC to disappear.

This is something that @rconn will have to look into.

Joe

 

[Joe Caverly]

@vefatica the purpose of the SCRIPT command is to do things that TCC cannot.

SCRIPT does not work with the WScript object, which makes sense.
(Added: That is, you only have access to the WScript object when you run a .VBS with either cscript.exe or wscript.exe)

If your WMI query can be run in TCC, then run it in TCC, not VBScript.

TCC does not do COM, which is why I use PSHELL or SCRIPT to use COM servers I've written, and also to utilize existing COM servers (Visual Fox Pro, Excel, etc.)

As there are current problems with PSHELL in v27, while I have reverted to v26 for PSHELL use, I wanted to see if the same/similar things could be done with SCRIPT, which I am finding is not only possible, but in some cases, faster, and easier.

Joe

 

[vefatica]

How about this? Should SCRIPT be able to handle it (it doesn't)?

Set iWMI = GetObject("winmgmts:\\.\root\CIMV2")
Set colItems = iWMI.ExecQuery("SELECT ProcessId,Name,CreationDate,ParentProcessId,KernelModeTime,UserModeTime,WorkingSetSize FROM Win32_Process where Name != 'cscript.exe'")

Set fso = CreateObject ("Scripting.FileSystemObject")
Set stdout = fso.GetStandardStream (1)

For Each objItem in colItems
    stdout.WriteLine objItem.Name
    stdout.WriteLine objItem.CreationDate
    stdout.WriteLine objItem.ProcessId
    stdout.WriteLine objItem.KernelModeTime
    stdout.WriteLine objItem.UserModeTime
    stdout.WriteLine objItem.WorkingSetSize
    stdout.WriteLine objItem.ParentProcessId
Next

 

[Joe Caverly]

@vefatica run this using SCRIPT;

Set iWMI = GetObject("winmgmts:\\.\root\CIMV2")
Set colItems = iWMI.ExecQuery("SELECT ProcessId,Name,CreationDate,ParentProcessId,KernelModeTime,UserModeTime,WorkingSetSize FROM Win32_Process where Name != 'cscript.exe'")

This is the same problem as in your previous script.

Something in these two lines is conflicting with the SCRIPT command, causing TCC to close.

This is something that @rconn will have to answer.

Joe

 

[Joe Caverly]

Hey @vefatica see if this works for you.

It demonstrates two methods of displaying output on the display;

Set fso = CreateObject ("Scripting.FileSystemObject")
Set stdout = fso.GetStandardStream (1)

stdout.WriteLine "Hello from VBScript"
TakeCommand.tcommand("echo Hello from SCRIPT")

Set stdout = Nothing
Set fso = Nothing

Sample run;

e:\utils>script vince.vbs
Hello from VBScript
Hello from SCRIPT

Joe

 

[vefatica]

Yeah, that works..

I don't have a very good overview of scripting (except for batch files). So why can't SCRIPT handle Wscript.Echo? When I Google, that seems to be THE way to write to the console in VBscript. (... or are folks mixing VBScript with something else?)

 

[Joe Caverly]

In reference to Using tcommand to get Excel data

SCRIPT does not work with the WScript object, which makes sense.
(Added: That is, you only have access to the WScript object when you run a .VBS with either cscript.exe or wscript.exe)

WScript is the top object in Windows Scripting Host.

Note that you never have to do a CreateObject to use the WScript object.

It is only included with cscript.exe or wscript.exe

If you run a script via a Scripting Host that you designed yourself (or, for instance, the SCRIPT command from TCC), you do not have access to the WScript object, as it does not exist.

It only exists if you run your script from cscript.exe or wscript.exe

From an MSDN article;

WSH executables (wscript.exe in particular) are not terribly complex programs. In its simplest form, WSH is just a minimal program, minus a Windows UI, that knows how to process script code. Executing script code from within a compiled application is a matter of implementing a few COM interfaces if you're working with C++, or incorporating the Microsoft Script Control if you prefer Visual Basic.

There really is no need to use WScript.Echo in a .VBS that you run using SCRIPT, as SCRIPT has its own way of doing that;

TakeCommand.tcommand("echo Hello from TCC")

Joe

 

[vefatica]

Thanks, Joe.

... but COM thingies (like a WMI interface?) should be available to SCRIPT?

 

[Joe Caverly]

Thanks, Joe.

... but COM thingies (like a WMI interface?) should be available to SCRIPT?

Yepper, all COM Servers, whether in-process or out-of-process, should work with the SCRIPT command.

As demonstrated in my OP, the Excel COM Server works fine with the SCRIPT command.

@rconn will have to see why the WMI COM interface will not work with the SCRIPT command.

Now, if you will be using a 32-bit COM Server with the 64-bit TCC SCRIPT command, you will need to provide a DllSurrogate for the 32-bit COM Server.

Ref: Using a 32-bit In-Process COM Server DLL from 64-bit TCC for info on how to do that.

Joe