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
– braX, Commented May 17, 2018 at 18:36
Are you sure your VBA code compiles as is? You can't have a method named Date! — 41686d6564
– 41686d6564, Commented May 17, 2018 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. — 41686d6564
– 41686d6564, Commented May 17, 2018 at 20:47

41686d6564 · Accepted Answer · 2018-05-17 18:43:54Z

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, 2018 at 18:43

answered May 17, 2018 at 18:38

41686d6564

19.8k13 gold badges48 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dotsent12 Over a year ago

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.

41686d6564 Over a year ago

@dotsent12 Please don't edit the question to add additional requirements. If you have another question, you should ask a new question.

41686d6564 Over a year ago

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, you should use objExcel.Workbooks.Open (filePath,,,,, "WritePassword").

Collectives™ on Stack Overflow

Integrating VBA inside VBScript file

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related