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, May 17 '18 at 18:36
Are you sure your VBA code compiles as is? You can't have a method named `Date`! — 41686d6564 stands w. Palestine, May 17 '18 at 18:41
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](https://stackoverflow.com/questions/ask). — 41686d6564 stands w. Palestine, May 17 '18 at 20:47

41686d6564 stands w. Palestine · Accepted Answer · 2018-05-17T18:43:54.317

2

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

edited May 17 '18 at 18:43

answered May 17 '18 at 18:38

41686d6564 stands w. Palestine

19,168
12
41
79

Works like a charm, big thanks! I added a write protection for the workbook though and struggling to properly add password operator to the Worksbooks.Open method - will update the OP. – dotsent12 May 17 '18 at 19:52
@dotsent12 Please don't edit the question to add additional requirements. If you have another question, you should [ask a new question](https://stackoverflow.com/questions/ask). – 41686d6564 stands w. Palestine May 17 '18 at 20:46
1

Anyway, for this specific issue, you don't have to ask a new question because it's been asked before several times. Since [VBScript doesn't support named arguments](https://stackoverflow.com/a/42194325/4934172), you should use `objExcel.Workbooks.Open (filePath,,,,, "WritePassword")`. – 41686d6564 stands w. Palestine May 17 '18 at 21:08

Integrating VBA inside VBScript file

1 Answers1