Welcome to the VanDyke Software Forums

Join the discussion today!


Go Back   VanDyke Software Forums > Scripting

Notices

 
 
Thread Tools Rate Thread Display Modes
Prev Previous Post   Next Post Next
  #16  
Old 08-03-2021, 10: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 10:12 AM.
Reply With Quote
 

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 10:28 AM.