Run a macro on last day of each month at midnight

[NZAS]

Hi
I have a file that has a Mracro (Macro2) to refresh the data and a pivot table.
I want to be able to automatically run this macro on the last day of each month at midnight. The file would not be open at that time this would need to be run, so will need to open the file on the last day of each month at midnight "00:00", and run the macro (macro2), when done then saveas with a the new file eg 18 Nov Monthend (year, month, Monthend) when closing the file.
This then can be opened any time to veiw the data which is a snapshot of data at month end at midnight.
Cheers
NZAS

 

[PatOBrien198]

You can use the windows Task Scheduler to kick off a process on the last day of each month at a specific time.
The process can be either a macro enabled workbook with a macro that starts when the workbook is opened, or
the process could be a "vbs" script that opens the workbook and runs a specific macro.


The vbs script file could be something like:

Dim fso, oExcel, oWorkbook, strXlsName, strXlsPath

Set fso = CreateObject("Scripting.FileSystemObject")
Set oExcel = CreateObject("Excel.Application")

strXlsPath = fso.GetAbsolutePathName(".") & "\YOUR_WORKBOOK.xlsm"
oExcel.Workbooks.Open strXlsPath, , False
Set oWorkbook = oExcel.ActiveWorkbook

oWorkbook.Application.Visible = True
oWorkbook.Application.Run "YOUR_MACRO_TO_RUN"

'Wait while Excel script runs
WScript.Sleep 15000
oWorkbook.Close True 'save changes
'your macro should be programmed to do the saving with the new name.

oExcel.Application.Quit

Set fso = Nothing
Set oWorkbook = Nothing
Set oExcel = Nothing

 

[NZAS]

It is a macro enable workbook where the macro runs when the workbook has been opened

 

[PatOBrien198]

Then you can set up a Task in the Task Scheduler to open your workbook on the last day of every month at midnight.