Use VBA to run macro daily at a given time

[SuperHeMan]

Hi,

I am new here in this forum so please bear with me if I ask a "dumb" question!

I have made a daily sales report that I run every morning and send to my colleagues. To avoid this simple task I would like to use VBA to auto-execute this on a computer that stay turned on 24/7.
So my thought is that the file should just stay open on this computer and self execute the macro that updates the file, save it, send the file to a predetermined mailing list and print x copies at 06:00 every morning (monday-friday).
I have tried to google this, but am not able to get the VBA code to work. This is what I have so far:


Private Sub Workbook_open()

application.OnTime TimeValue("06:00:00"), "UpdateSalesReport"


End Sub


Sub UpdateSalesReport()

application.ScreenUpdating = False

Sheets("IWH").Select
Connect_Retrieve ("IWH")


application.StatusBar = False

Sheets("Weekly Sales").Select
Range("a1").Select

application.ScreenUpdating = False
ActiveWorkbook.Save


End Sub

My understanding is that the first "sub" only execute when the workbook is opened, not if it stay opened?!? Anyways, I am not able to get it to work when I open the file or if I leave it open....
Do you have any suggestions to how I can solve this?

Thx

 

[WarPigl3t]

So you said that you already have a macro that does what you want. You just want it to execute at a certain time. I've never done this so bare with me. Right click the sheet name tab at the bottom of the document. Sheet1 for example. Select "View Code" from he menu. A VBA module will appear. From the left navagation of the VBA module, you should see some stuff like the sheet names and hopefully one that says "Workbook". Double click on Workbook. Now you are in the VBA module for the entire workbook. There are 2 drop down lists at the top middle area. From the left drop down list, select "Worksheets" or "Workbook". Pretty sure there is only 1 option to choose from so just select it. From the right drop down list, see if there is an option to run the code every so often. I don't know if there is one. I usually use the "Change" command. Unfortunately the "Change" command will not help you so see if there is another option. If there is one and you select it, it will insert a Private handler. Then you can write a line of code that says.

If TIME() = "6:00 AM" Then
    Call yourMacro
End If

 

[SuperHeMan]

Thx for the suggestion, but I couldn't find the option needed in the workbook dropdown list.
So I have started to look for alternatives and came over a VBScript that might help me through the windows task scheduler. However I was not able to make it work...the VBScript is as follows:

Set objExcel = CreateObject("Excel.Application")Set objWorkbook = objExcel.Workbooks.Open("C:\VBScript test.xlsx")
objExcel.Application.DisplayAlerts = False
objExcel.Application.Visible = True


objExcel.ActiveWorkbook.Save = True
objExcel.ActiveWorkbook.Close = True


Set objExcel.Application.Quit = True
WScript.Echo "Finished."
WScript.Quit

Does anyone have any idea on how I can get this to work?

 

[CharlesChuckieCharles]

Most of the scheduling should be in the scheduler

OUTPUT
Build a macro to produce your report (call it Auto_open) that works every time you run the macro, thats the report done


SCHEDULING
using windows task scheduler(in admin tools from the control panel) create a basic task with a run option

reference the full path of the spreadsheet Myreportingcentre.xlms

and to trigger at a particular time 18:00 etc


when the scheduler time is hit he excel sheet is opened and the auto_open is run (don't forget to have the Auto_open close the xlsm when finished)

Its mostly about the scheduler and keeping the code viable for it ( ie not having it open around 18:00 or better still leaving it alone completely)

 

[SuperHeMan]

Sorry, I was a bit unclear about why I am trying to use the VBScript.. The excel file I will use in the end is password protected (however I have not incorporated this in the test script as I am trying to build it to understand the logic first) and as far as I have understood the scheduler have no way around this on its own?!? Is this true?

 

[SuperHeMan]

Hi,

I have tried using this VBscript without any luck... It seems that the problem is opening the file, so something is probably wrong with the password settings but I am not able to figure it out.
Would appreciate some guidance!

Set objExcel = CreateObject("Excel.Application")Set objWorkbook = objExcel.Workbooks.Open("C:\Users\hummely\Desktop\Weekly Sales - AutoUpdate.xlsm",,,,,"testpassword")


strExcelPath = "C:\Users\hummely\Desktop\Weekly Sales - AutoUpdate.xlsm"


objexcel.Visible = True 
objWorkBook.Refreshall
objExcel.DisplayAlerts = False
objExcel.Application.Run "'Weekly Sales - AutoUpdate.xlsm'!AutoUpdateANDSave"
objExcel.ActiveWorkbook.SaveAs strExcelPath
objworkbook.Saved = True
Const OverwriteExisting = TRUE
objworkbook.Close
objExcel.Application.Quit


WScript.Echo "Finished :)"
WScript.Quit

 

[SuperHeMan]

I managed to get the file and script to run, but the macro will not run properly...any ideas on how I can get the macro to run?
The scriptcode I currently use is this:

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("C:\Users\hummely\Desktop\Copy of Weekly Sales - AutoUpdate v2.xlsm", , , ,"test" ,"test" ,True)


strExcelPath = "C:\Users\hummely\Desktop\Copy of Weekly Sales - AutoUpdate v2.xlsm"


objWorkBook.Refreshall


objexcel.Visible = True 
objExcel.DisplayAlerts = False
objExcel.Run "UpdateSalesReport"
objExcel.ActiveWorkbook.SaveAs strExcelPath


objworkbook.Saved = True
Const OverwriteExisting = TRUE
objworkbook.Close
objExcel.Application.Quit


WScript.Echo "Finished :)"
WScript.Quit

I have also tried the following codes around the macro without any success:

objExcel.Application.Run "UpdateSalesReport"
objExcel.Application.Run "'Copy of Weekly Sales - AutoUpdate v2.xlsm'!UpdateSalesReport"
objExcel.Run "'Copy of Weekly Sales - AutoUpdate v2.xlsm'!UpdateSalesReport"

 

[SuperHeMan]

I have now managed to get the vbscript to work on a test file, however the macro in the actual file is not running correctly via the vbscript but runs without any issues manually in the file. I suspect that the reason why it will not run is due to the fact that the macro is quite heavy and connects to IWH/Essbase and use about 1-2 minutes to retrieve all data from the server. So is there any way I can delay the vbscript so that it will give the excel file enough time to run the macro properly?? Or is it better to split the script in three: one that starts excel, one who the macro in question, one that eventually save and close the excel file?

Would appreciate any input!