update workbook on open

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

Hello, I have a template that generates info in it from a access database. I
would like to have a window come up as soon as the excel file opens and ask
to save it and I want it to save to whomevers desktop as a name like Slitter
and the date Example: Slitter_6-8-2008.xls

Can this be done and if so how? Possibley a link to an example or a tut?
Thanks!
 
Try this workbook event macro:

Private Sub Workbook_Open()
u = Environ("username") & "_"
u2 = Environ("username")
ChDir "C:\Documents and Settings\" & u2 & "\Desktop"
ActiveWorkbook.SaveAs Filename:=u & Replace(Date, "/", "-")
End Sub


Because it is workbook code, it is very easy to install and use:

1. right-click the tiny Excel icon just to the left of File on the Menu Bar
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
Sorry I spoke to soon... The code works great but if I open the .xls that it
created it wants to create another copy because the code is in the copy as
well. and it gives me an error: 1004 Method 'SaveAs' of object' _Workbook'
failed.


Thanks!
 
Replace the old version with this new version:

Private Sub Workbook_Open()
u = Environ("username") & "_"
u2 = Environ("username")
ChDir "C:\Documents and Settings\" & u2 & "\Desktop"
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=u & Replace(Date, "/", "-")
End Sub
 
Back
Top