Integrating VBA inside VBScript file

Question

I have a working VBScript file and VBA macro inside Excel worksheet that does this:

Refreshes all data connections
Writes a timestamp in a specific cell
Save and closs the Excel worksheet

VBS file:

 Set objExcel = CreateObject("Excel.Application")

 objExcel.Application.Run "'G:\Blank.xlsm'!Module9.Date"
 objExcel.DisplayAlerts = False
 objExcel.Application.Quit

 Set objExcel = Nothing

VBA inside the Blank.xlsm worksheet:

Sub Date()
    ActiveWorkbook.RefreshAll

    With Range("M12")
        .Value = Now()
        .NumberFormat = "dd/mm/yy hh:mm"
        ActiveWorkbook.Save
    End With
End Sub

Is it possible to keep the Excel macro-free .xslx file and run both of those functions from a VBScript file, which would not call the macro inside the Excel workbook to do the things I need, but rather complete those tasks by itself? I'm very new to VBScript (and frankly, VBA, too), so I'm sorry if this comes as too basic of a question.

Look into using an XLAM file (Excel Add-in) which can operate on a code free XLSX file. — braX
Are you sure your VBA code compiles as is? You can't have a method named Date! — 41686d6564
Please don't edit the question to add additional requirements after receiving an answer and accepting it. If you have another question, you should ask a new question. — 41686d6564

41686d6564 41686d6564 · Accepted Answer · 2018-05-17T18:38:52

Yes, of course, it's possible. Here:

Option Explicit

Dim objExcel, objWorkBook

Set objExcel = CreateObject("Excel.Application")
Set objWorkBook = objExcel.Workbooks.Open(filePath)

objExcel.Application.Visible = False
objExcel.DisplayAlerts = False

objWorkBook.RefreshAll

With objWorkBook.Sheets(1).Range("M12")
    .Value = Now()
    .NumberFormat = "dd/mm/yy hh:mm"
End With

objWorkBook.Save
objWorkBook.Close

objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

Integrating VBA inside VBScript file

1 Answers