How to run macro without open Excel

Question

I tried to execute a .vbs script to run macro inside Excel without opening the workbook. but when I open the workbook I found that nothing changed.

macro for changing column type from text to number percentage.

Public Sub updateColumnFormat()

    'Disable screen update and calculation to improve the performance of import
    Application.ScreenUpdating = False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.EnableCancelKey = True
    Application.Calculation = False
    Application.ScreenUpdating = False
    
    Sheets("Status").Select
    [B:B].Select 'Update data type from text to number of CreditLimit (Column B)
    With Selection
      .NumberFormat = "General"
      .Value = .Value
    End With
    Selection.Style = "Percent"
    Call Refreashstage
    'Sheets("Status").Select
    'Range("B3").Select
    'Sheets("Sales").Select
    On Error Resume Next

End Sub

Sub Refreashstage()

    Sheets("Status").Select
    Range("B3").Select
    Sheets("FY21 Sales Forecast").Select

End Sub

.vbs script to execute macro without open the Excel

Set ObjExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'C:\Users\NB\Desktop\test6.xlsm'!Module1.updateColumnFormat"
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing

I just tested it. running a script from currently unopened workbook just automatically triggers the workbook to open. When you do objExcel.Application.Run you're opening the workbook in the hidden excel application that you created. When you do objExcel.Application.Quit, you're closing the workbook after opening it. — Toddleson
@Toddleson, Yes when I run the .vbs script I found the hidden file(~$test6) like when I open the excel. and after finished the hidden file was gone because objExcel.Application.Quit right? but when I open the workbook for check I found that the result doesn't change and the file modified date also doesn't change. So what should I do to call macro to take effect? — alice

Toddleson Toddleson · Accepted Answer · 2021-07-16T16:56:20

Running a script from currently unopened workbook automatically triggers the workbook to open. When you do objExcel.Application.Run you're opening the workbook in the hidden excel application that you created. If you were to insert the line objExcel.Visible = True after the .Run line, you would see the open workbook.

When you do objExcel.Application.Quit, you're closing the workbook after opening it. And the line Application.DisplayAlerts = False disabled the pop-up that would've asked if you wanted to save the changes made to the workbook before closing. It defaults to "No" and discards all changes.

Close the workbook properly before doing objExcel.Quit with:

VBA:objExcel.Workbooks(1).Close SaveChanges:=True

VBScript: objExcel.Workbooks(1).Close True

Side Note: Your code uses .Select in very unnecessary ways. .Select is only really useful for drawing the user's attention towards screen regions or cells. But your workbook is not even visible, so .Select is only slowing down execution and making object references ambiguous.

I would suggest your code be improved to something like:

    'Update data type from text to number of CreditLimit (Column B)
    With ThisWorkbook.Sheets("Status").Columns("B")
        .NumberFormat = "General"
        .Value = .Value
        .Style = "Percent"
    End With
    Call Refreashstage

How to run macro without open Excel

1 Answers