.bat file to run macro without workbook event

[This_user_name]

Hi,
I have had some issues trying to schedule the running of a macro on my computer while I'm away.
I'd like to try it with a .bat file.

I am aware of the fact this is possible to do with vbscript but it's not an option because of a plethora of issues:
I managed to run the macro at 5:00 Am every morning for about two weeks, then IT changed the User groups, where the default account on the computer had no rights to run scripts anymore, so I set the Administrator user account for running it, also set it to "run with highest privileges", configured for my OS (Windows 7), so it ran for a couple of days, and then IT changed something again. No idea how, but they somehow accomplished that no scheduler task with a script will start. The script just simply won't start at all. If I click on it manually it will start up both the excel file and the macro, but not on scheduler. I don't know how but it won't even try it, because for last run the scheduler history tells Never for each and every single one of them.

So anyways, it most likely won't work either, but I'd like to get a .bat file that starts my excel file and runs the macro. All I could find anywhere on the internet was for the .bat to open the excel file which had a workbook_open event to start the macro. I don't want to go down that route, because I want to be able to open the file without running a macro that takes 15+ minutes to run.
Is this possible? Is there a code for running a macro in an open excel file?

 

[steve the fish]

How often do you open the file manually before 5AM? Just test the time so if the scheduler opens the file the macro can run but if you open it at 9AM the macro still runs but doesn't continue because the of the time test.

 

[John_w]

I'd like to get a .bat file that starts my excel file and runs the macro. All I could find anywhere on the internet was for the .bat to open the excel file which had a workbook_open event to start the macro. I don't want to go down that route, because I want to be able to open the file without running a macro that takes 15+ minutes to run.
Is this possible? Is there a code for running a macro in an open excel file?

VBScript is only method I only know which can run a specific macro in an Excel workbook and avoid the Workbook_Open event.

However, if you can't use VBScript then a simple approach is to define a local environment variable in the .bat file and check if it exists using the VBA Environ function in Workbook_Open. If it exists then you know that the workbook was opened by the .bat file.

 

[Jaafar Tribak]

Another alternative is to open the workbook via an intermediate workbook

Create a new workbook and name it "Intermediate" .. Place the following code in the ThisWorkbook Module of this Intermediate workbook and save it

Option Explicit

Private Sub Workbook_Open()
    Dim oWb As Workbook
    
    On Error GoTo Xit
    Application.EnableEvents = False
    Set oWb = Workbooks.Open("C:\Test\ScheduledWorkbook.xls")
    If Not oWb Is Nothing Then
        Application.Run "'" & oWb.Name & "'!YourMacro"
    End If
Xit:
    Application.EnableEvents = True
    Me.Close False
End Sub

Where C:\Test\ScheduledWorkbook.xls is the full name of the scheduled workbook and "YourMacro" is the Macro to be ran (Change paths and names as required)

Finally, open the intermediate workbook via the Batch file .. Something like :

Batch file command:
Start "Excel.exe" "C:\Test\Intermediate.xls"

 

[This_user_name]

Another alternative is to open the workbook via an intermediate workbook

Create a new workbook and name it "Intermediate" .. Place the following code in the ThisWorkbook Module of this Intermediate workbook and save it

Option Explicit

Private Sub Workbook_Open()
    Dim oWb As Workbook
    
    On Error GoTo Xit
    Application.EnableEvents = False
    Set oWb = Workbooks.Open("C:\Test\ScheduledWorkbook.xls")
    If Not oWb Is Nothing Then
        Application.Run "'" & oWb.Name & "'!YourMacro"
    End If
Xit:
    Application.EnableEvents = True
    Me.Close False
End Sub

Where C:\Test\ScheduledWorkbook.xls is the full name of the scheduled workbook and "YourMacro" is the Macro to be ran (Change paths and names as required)

Finally, open the intermediate workbook via the Batch file .. Something like :

Batch file command:
Start "Excel.exe" "C:\Test\Intermediate.xls"

I've done your solution and it works perfectly! Thank you very much! Great help!