Reading SecureCRT output from Excel VBA

[gregg]

Hello-

I might be missing something, but this script will update a cell in an Open excel sheet with new data. I think the difference is GetObject vs CreateObject

Option Explicit

REM On Error Resume Next
Dim xlApp
Set xlApp = GetObject(, "Excel.Application")
If xlApp is Nothing Then
    MsgBox "Excel not loaded [" & Err.Number & "] " & Err.Description
Else
    ' MsgBox "App Loaded"
    Dim wb_name, sheet_name, row, col, new_data
REM     wb_name     = WScript.Arguments.Item(0)
REM     sheet_name  = WScript.Arguments.Item(1)
REM     row         = Int(WScript.Arguments.Item(2))
REM     col         = Int(WScript.Arguments.Item(3))
REM     new_data    = WScript.Arguments.Item(4)
    wb_name = "writetest.xlsx"
    sheet_name = "Sheet1"
    row = 3
    col = 8
    new_data = "some new data"

    UpdateWorkbookCell xlApp, wb_name, sheet_name, row, col, new_data
End If

Sub UpdateWorkbookCell(xlApp, wb_name, sheet_name, row, col, new_data)
    Dim wb
    For Each wb In xlApp.Workbooks
        If wb_name = wb.Name Then
            MsgBox wb.Name
            Dim sheet
            For Each sheet In wb.Sheets
                If sheet_name = sheet.Name Then
                    MsgBox sheet.name
                    sheet.Cells(row, col) = new_data
                End If
            Next
        End If
    Next
End Sub

It's lacking a lot of error handling, but does update the already opened excel workbook/sheet/cell with the new value. The commented out WScript.Arguments bits were for testing from the command line but the script also ran inside of sCRT v9 just fine.

[djvj]

Originally Posted by gregg

Hello-

I might be missing something, but this script will update a cell in an Open excel sheet with new data. I think the difference is GetObject vs CreateObject

It's lacking a lot of error handling, but does update the already opened excel workbook/sheet/cell with the new value. The commented out WScript.Arguments bits were for testing from the command line but the script also ran inside of sCRT v9 just fine.

Let me get this straight, you ran that with an open spreadsheet, and saw the cells update on the sheet and didn't have to reload the spreadsheet to see the new values?

[gregg]

Let me get this straight, you ran that with an open spreadsheet, and saw the cells update on the sheet and didn't have to reload the spreadsheet to see the new values?

This was my experience, yes. I just tried it again for sanity, scrt open on the left side of my screen and excel with an open workbook on the right. Running the script I saw the values pop up in excel.

I even did multiple updates to random cells and watched each one populate in.

    UpdateWorkbookCell xlApp1, wb_name, sheet_name, 9, 7, new_data
    crt.Sleep(1000)
    UpdateWorkbookCell xlApp1, wb_name, sheet_name, 10, 3, new_data
    crt.Sleep(1000)
    UpdateWorkbookCell xlApp1, wb_name, sheet_name, 4, 6, new_data
    crt.Sleep(1000)
    UpdateWorkbookCell xlApp1, wb_name, sheet_name, 7, 2, new_data
    crt.Sleep(1000)
    UpdateWorkbookCell xlApp1, wb_name, sheet_name, 1, 9, new_data

Now, I didn't launch scrt from excel, but I don't see why that would make a difference. I would think you can get all of the relevant values from excel and pass them to the scrt script via /ARG (such as wb name, sheet, cells) and then use crt.Arguments to get them to push values back into the sheet.

fwiw, I'm running scrt 9.0.2 and excel standard 2016 on win10 x64

[djvj]

Ok thank you.

When I get some time, I'll try your method and see what I get.

Would be really nice if this works the way I need it to.

[gregg]

If you're more keen to use Python3 in scrt (I would be), looks like pywin32 can handle the COM objects to do the same kind of thing.

My quick test:

# $language = "Python3"
# $interface = "1.0"

import win32com.client

ExcelApp = win32com.client.GetActiveObject("Excel.Application")
wb =  ExcelApp.Workbooks("writetest.xlsx")
sheet = wb.WorkSheets(1)
r = sheet.Range("C9")
r.Value="Updated from SecureCRT"

Of course, you'll need to install python3 such that it integrates with sCRT and also pywin32, for that I just did

pip install pywin32
(https://pypi.org/project/pywin32/)

You'll also likely have to open a new sCRT instance for it to pick up the new library.