VanDyke Software Forums - Reading SecureCRT output from Excel VBA

VanDyke Software Forums (https://forums.vandyke.com/index.php)

- Scripting (https://forums.vandyke.com/forumdisplay.php?f=14)

- - Reading SecureCRT output from Excel VBA (https://forums.vandyke.com/showthread.php?t=14545)

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

Code:

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.

Quote:

Originally Posted by gregg (Post 55237)

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?

Quote:

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.

Code:

    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

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.

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:

Code:

# $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.