is this even possible???????

[malkhazov]

is there a way to get excel to
open at a specific time of day
on specific days 9all days?)
perform specific functions (this I know is possible)
name itself with the current date
and save in a specified directory
and close itself

???????

 

[macleanb]

From the sounds of it you can probably work out all the last bits. On Win2K there is a wizard for adding scheduled tasks - I assume there must be similar utilities for most other windows platforms too. Using this you can run excel with a command line to open your worksheet which will have an on_open macro to do the work. Of course if anyone uses this PC they might be a little confused by all this stuff happenoing "on its own" If you want to go into more specifics let me know

 

[malkhazov]

Ben--

I tried finding the on-open property in Excel VBA help but could not find any more information. Can you give some direction on where I can get more info on this function?

 

[Oaktree]

Try searching VBA help (or the board) for workbookopen events

 

[Smitty]

Look at the Workbook "Open Event" in the VBA helpfile.

This example will save with a file name that you enter and the current date:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">Dim</SPAN> NewName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    NewName = InputBox("Please enter the new file name", "New File Name") & " - " & Format(Date, "dd-mm-yy")
    
    <SPAN style="color:#007F00">'   Do stuff here</SPAN>
    
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\All Users\Desktop\Temp\" & NewName & ".xls", FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        <SPAN style="color:#00007F">False</SPAN>, CreateBackup:=<SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

 

[reedssr]

i do this daily for reports at work

yeah, no problem, i have a daily event scheduled in windows which runs the book, place your call to your modules in workbook Open event like the others have told you. make sure you digitally sign an accept your own vba signature so it will run without you being present. do all the stuff you want to your file, use the save as commands to save your new file,

Dump_Backup = Dump_Backup & " " & Month(Now) & "." & Day(Now) & "." & Year(Now) & " " & Hour(Now) & "-" & Minute(Now) & ".csv"

ActiveWorkbook.SaveAs Filename:= _
Dump_Backup, FileFormat:=xlCSV, _
CreateBackup:=False

This particular one saves to a csv file but you can save it to xls instead

use Now() to get the current date and Month() Day() and Year(), create your new filename string for the save command. make sure you play nice and check for other open workbooks so you don't accidentally close someone elses work by using Workbooks.Count