Writing to an open workbook from a VB script

[jtdewhurst]

Hi, I've done quite a bit of research, and I haven't found anything.

Is there anyway to write to a workbook that the current user already has open from a VB Script?

 

[kumarapush123]

Usually if it is opened and locked for editing by another user then it is not possible to edit that workbook either manually or through any Macro.
But in case if you are looking for writing to another Excel from one Excel, then try this link that discuss on different methods to do that.

http://officetricks.com/different-methods-to-import-data-from-external-excel-file/

 

[jtdewhurst]

Usually if it is opened and locked for editing by another user then it is not possible to edit that workbook either manually or through any Macro.
But in case if you are looking for writing to another Excel from one Excel, then try this link that discuss on different methods to do that.

Different Methods to Import Data From External Excel File | OfficeTricks – Technical Bloggers Space

Basically I'm trying to find a way for a Script to output data to an excel worksheet. the Script is itself called from the open workbook. I was wonder if because the user who has the workbook open is the same one running the script if there was anyway to write data to the workbook without closing it first.

 

[kumarapush123]

Yes. It is possible using the Workbooks collection object. But the limitation is that you should exactly mention the workbook name inside the code.
Either you can search in this forum for Workbooks Collection or refer point 2 in the link that I provided for a sample code.

 

[John_w]

Basically I'm trying to find a way for a Script to output data to an excel worksheet. the Script is itself called from the open workbook.

It can be done by having the VBScript refer to the open workbook by its name - the Set Wb line in the following code, which writes the current time to A1 on the first sheet.

Public Sub Create_and_Run_VBScript()

    Dim VBScriptFile As String
    Dim fileNum As Integer
        
    'Create the VBScript.vbs file
    
    VBScriptFile = Environ("temp") & "\VBScript.vbs"
    fileNum = FreeFile
    Open VBScriptFile For Output As fileNum
    
    Print #fileNum, "Option Explicit"
    Print #fileNum, "Dim Excel, Wb"
    Print #fileNum, "Set Excel = GetObject(, ""Excel.Application"")"
    Print #fileNum, "Set Wb = Excel.Workbooks(""" & ThisWorkbook.Name & """)"
    Print #fileNum, "Wb.Worksheets(1).Range(""A1"").Value = Now"
    Close fileNum
        
    'Debugging aid - open the script in Notepad
    
    Shell "Notepad " & VBScriptFile
                    
    'Run the script
    
    Shell "wscript " & Chr(34) & VBScriptFile & Chr(34), vbNormalFocus
                                                   
End Sub

Simply call Create_and_Run_VBScript from the Workbook_Open routine in the ThisWorkbook module to run the script when the workbook is opened.