Running a VBA macro from VBS file

[dotsent]

Hi!

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

1) refresh all data connections
2) write a timestamp in a specific cell
3) save & close 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 an 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.

Thanks!

 

[JLGWhiz]

This should allow you to execute the same actions from the VBS workbook as a stand alone macro.

Sub runDate()
Dim wb
On Error Resume Next
    Set wb = Workbooks("Blank.xlsm") 'edit file extension
        If Err.Number = 9 Then
            Set wb = Workbooks.Open("G:\Blank.xlsm")
        End If
On Error GoTo 0
Err.Ckear
    With wb
        .RefreshAll
        With .Range("M12")
            .Value = Now()
            .NumberFormat = "dd/mm/yy hh:mm"
        End With
        .Save
    End With
End Sub