update workbook on open
 

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!

update workbook on open
 

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


--
Gary''s Student - gsnu200790


"Chad" wrote:

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!

update workbook on open
 

Awsome! Thanks....
--
Newbies need extra loven.........


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200790


"Chad" wrote:

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!

update workbook on open
 

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!

update workbook on open
 

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

--
Gary''s Student - gsnu200790


"Chad" wrote:

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!

update workbook on open
 

That works! Thanks!
--
Newbies need extra loven.........


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200790


"Chad" wrote:

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!