Automatically run macro daily without opening any workbooks

Question

I am still relatively new to VBA and not too experienced with the Application.OnTime method. I have been looking at multiple resources, and I cannot see a clear way to automatically run a macro daily without opening any workbooks (assuming I run the macro first in my workbook).

Is this possible? I would like to be more efficient instead of running multiple macros every morning.

Let me know if you need more info but my code would simply be a copy/paste process:

Sub MyMacro()
Application.ScreenUpdating = False

Dim OH As Workbook
Dim PO As Workbook


Set OH = Workbooks.Open("filepath")
Set PO = Workbooks.Open("filepath2")

'clear sheet
ThisWorkbook.Sheets("OH").Range("A2:O10000").ClearContents
'clear other sheet
ThisWorkbook.Sheets("OP").Range("A2:AG10000").ClearContents


'Paste new data
OH.Sheets("OH").Range("B3:P10000").Copy 
Destination:=ThisWorkbook.Sheets("OH").Range("A2")
PO.Sheets("OP").Range("A3:AG20000").Copy 
Destination:=ThisWorkbook.Sheets("OP").Range("A2")


OH.Close savechanges:=False
PO.Close savechanges:=False


'Refresh all pivot tables
Dim PT As PivotTable
Dim WST As Worksheet
    For Each WST In ThisWorkbook.Worksheets
    For Each PT In WST.PivotTables
        PT.RefreshTable
    Next PT
Next WST


'Clear last sheet
ThisWorkbook.Sheets("Pivot1 paste").Range("A6:E10000").ClearContents

ThisWorkbook.Sheets("Pivot1").Range("A6:D10000").Copy 
Destination:=ThisWorkbook.Sheets("Pivot1 paste").Range("A6")

'Paste variable column to last sheet
Dim cell As Range
For Each cell In ThisWorkbook.Sheets("Pivot1").Range("E3:AZ6")
    If cell.Value = "Out" Then cell.EntireColumn.Copy 
Destination:=ThisWorkbook.Sheets("Pivot1 paste").Columns(5)
Next

'Save with current date and close
ThisWorkbook.SaveAs ("TargetFilepath") 
& ".xlsm")
ThisWorkbook.Close


Application.ScreenUpdating = True
End Sub

Solution

VBA is made to work within Microsoft Office, but you can leverage VB Script to open a workbook and run a macro.

Place the following in a .vbs file. You will create a schedule to call and execute this file. Make sure to set the correct path and edit "test.xlsm!mymacro" to the name of your workbook and the macro you wish to call.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\path\to\test.xlsm")

objExcel.Application.Run "test.xlsm!mymacro"
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

To test this, I created a quick macro in test.xlsm to create a text file in the same directory and verified that it was there after I ran the VB Script. There is nothing special about the macro below, you can call whatever macro you desire. This will save you from having to manually open the workbook and run the macro.

Sub mymacro()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim oFile As Object
    Set oFile = fso.CreateTextFile("C:\Users\bcohan\Downloads\testing.txt")

    oFile.WriteLine "test"
    oFile.Close

    Set fso = Nothing
    Set oFile = Nothing
End Sub

Once you have the above working, you should be able to create a scheduled task in windows to run your script.

Comments

[T.M.]

See also Run Excel macro from outside Excel using VbScript from command line

[user10101843]

Ok, thank you this is very helpful. I will try to set this up for myself and if I have any questions for setting up in the process I will refer to this thread.

[user10101843]

I actually do have an important follow-up question: Does my computer have to be on ( not on sleep mode or shut off) for the scheduled macro to run?

[Cohan]

I'm more of a linux guy, so I know in linux, if a cronjob is missed, the computer will run them when it wakes. So you might get hammered once you boot the machine, but yes, the computer has to be on in one way or another for it to run. Depending on the nature and significance of the macro, if you're using this for work, you may be able to see if there's a way to execute it from another machine that is always on.

[user10101843]

Hello, I have a couple of elementary questions: 1) Would I put the first script in a blank workbook with a sub and end sub? 2) Would the same follow for the second script, and would I create the scheduled task off of the first script? If so, what is the significance of the text file? Thank you, just want to make sure I grasp what is occurring.

[Cohan]

Edited my answer a bit, but you just need to put the script into a .vbs file and schedule your system to run it when you want it to run. For that part, I'd consult the web to figure out how to do it on your system.

[user10101843]

My absolute last question: I test ran the first script above that I saved as .vbs and I received a "The macro may not be available in this workbook or all macros may be disabled". After searching for solutions, all I got was to change the workbook Trust Center to support macros, but that did not solve the problem.

[Cohan]

I have my macro settings set to Enable all macros... and that seems to do the trick. I don't know of any other settings off the top of my head. Check that you spelled the workbook name and macro name correctly.