Welcome to the VanDyke Software Forums

Join the discussion today!


Go Back   VanDyke Software Forums > Scripting

Notices

Reply
 
Thread Tools Rate Thread Display Modes
  #16  
Old 08-03-2021, 11:09 AM
gregg gregg is offline
Registered User
 
Join Date: Oct 2010
Posts: 75
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.

Last edited by gregg; 08-03-2021 at 11:12 AM.
Reply With Quote
  #17  
Old 08-05-2021, 07:40 PM
djvj djvj is offline
Registered User
 
Join Date: Oct 2011
Posts: 13
Quote:
Originally Posted by gregg View Post
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?
Reply With Quote
  #18  
Old 08-06-2021, 12:31 AM
gregg gregg is offline
Registered User
 
Join Date: Oct 2010
Posts: 75
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

Last edited by gregg; 08-06-2021 at 12:35 AM. Reason: version info
Reply With Quote
  #19  
Old 08-07-2021, 02:49 PM
djvj djvj is offline
Registered User
 
Join Date: Oct 2011
Posts: 13
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.
Reply With Quote
  #20  
Old 08-07-2021, 05:03 PM
gregg gregg is offline
Registered User
 
Join Date: Oct 2010
Posts: 75
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.
Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -6. The time now is 07:06 PM.