CODEWITHSUNDEEP
excelvba
user10101843
user10101843

Reputation: 41

Automatically run macro daily without opening any workbooks

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

Upvotes: 2

Views: 15123

Answers (1)

Cohan
Cohan

Reputation: 4564

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.

Upvotes: 6

Related Questions