View Single Post
  #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