Have an Excel Open Itself and Save uniquely each time using VBA

[Michael515]

Hi,

I'm trying to have an excel open itself up in the background (so I don't have to actively open it) and then once open run a task where it refreshes the workbook (I have created a refresh workbook button labeled "Refresh_Workbook()" and then save itself uniquely each time it does that (so eventually it has multiple file names with a time stamp). This is the code I'm currently using:

Private Sub Workbook_Open()
    Workbooks.Open Filename:="C:\Users\Person\Desktop\My Documents\WorkbookRefreshProject.xlsm"
    Application.OnTimeValue ("10:08:00"), "Refresh_Workbook()"
    ActiveWorkbook.SaveCopyAs ("[URL="file://\\filepath\Feed"]\\filepath\Feed[/URL] Program\FormFLow to C:\Users\Person\Desktop\My Documents\WorkbookRefreshProject-" & Format(Now(), ("mmddyyyy") & ".xlsm"))
End Sub

Let me know if you have any questions or comments.

 

[Michael515]

I also realized I can use Windows Task Scheduler to open the Excel, so now I just need to code to refresh the workbook and save it uniquely each time (I don't want the workbooks to save over themselves, if I have task scheduler run 8 times in a day, I want to have 8 saved workbooks), and then close itself, here's my new code:

Sub Auto_Open()
    Workbooks("WorkbookRefreshProject").RefreshAll
    ActiveWorkbook.SaveCopyAs Filename:="C:\Users\T622577\Desktop\My Documents\WorkbookRefreshProject-" & Format(Now(), ("mmddyyyy") & ".xlsm")
End Sub

 

[Michael515]

Okay so now I got the code for it to work with refresh and add a time in, now I just want to date it as well as well before the time:

Sub Auto_Open()
    Workbooks("WorkbookRefreshProject").RefreshAll
    ActiveWorkbook.SaveCopyAs Filename:="C:\Users\T622577\Desktop\My Documents\WorkbookRefreshProject- " & Format(Now(), "hh-mm-ss") & ".xlsm"
    ThisWorkbook.Saved = True
    Application.Quit
   
End Sub

 

[BQardi]

Just add the formatting (mmddyyyy) before the time:

Sub Auto_Open()
    Workbooks("WorkbookRefreshProject").RefreshAll
    ActiveWorkbook.SaveCopyAs Filename:="C:\Users\T622577\Desktop\My Documents\WorkbookRefreshProject- " & Format(Now(), "[COLOR=#ff0000]mmddyyyy [/COLOR]hh-mm-ss") & ".xlsm"
    ThisWorkbook.Saved = True
    Application.Quit
   
End Sub

 

[Michael515]

Just add the formatting (mmddyyyy) before the time:

Sub Auto_Open()
    Workbooks("WorkbookRefreshProject").RefreshAll
    ActiveWorkbook.SaveCopyAs Filename:="C:\Users\T622577\Desktop\My Documents\WorkbookRefreshProject- " & Format(Now(), "[COLOR=#ff0000]mmddyyyy [/COLOR]hh-mm-ss") & ".xlsm"
    ThisWorkbook.Saved = True
    Application.Quit
   
End Sub

Thank you!

 

[BQardi]

You're welcome