Can anyone tell me how to automatically copy an Excel file to anothe
location when I open it? I'm trying to allow people to view a copy of
file each time I update it.

Many thanks

Stev

--
Message posted from http://www.ExcelForum.com

copy this code in your 'This Workbook' module. check for
the parameters of SaveAs method in the vba help

Private Sub Workbook_Open()
Application.DisplayAlerts = False ' this will not ask you _
'if you want to replace the existing file
ActiveWorkbook.SaveAs (filepath,....)
Application.DisplayAlerts = True
End Sub

this will save it as a file everytime you open this file
even if you are opening it to update the file.

Private Sub Workbook_BeforeClose()
ActiveWorkbook.SaveAS (...)
End sub


hope this helps

-----Original Message-----
Can anyone tell me how to automatically copy an Excel
file to another
location when I open it? I'm trying to allow people to
view a copy of a
file each time I update it.

Many thanks

Steve


---
Message posted from http://www.ExcelForum.com/

.

hi,
dont mean to be rude, but dont you want to save the book, when you
close it?, otherwise you'll be "publishing" the new one only after you
have closed it and THEN reopen it, so yu might open the file, it's
publsihed, do some work then close it. you get ill, and dont open the
book for another few day, so the thing hasn't been updated.
If i was you i would use the code, that nice man gave you, but put it
in the "before close" event of the workbook, or.... beter still in a
command button/toolbar button, this way you can have more contol of
when you "publish" you updates - alowwing you to edit, save and not
"publish", or "publish" half way though.

just some ideas

good luck
rosscoe

I pasted this into the ThisWorkbook module:

Private Sub Workbook_BeforeClose()
ActiveWorkbook.SaveAs ("C:\Test.xls")
End Sub

However, after making changes to the workbook and saving I get th
following error message on exit:

Compile error: Procedure declaration does not match description o
event or procedure having the same name

Being a complete novice at VB can you put me right please?

I tried Excel's own "File\Save as\Tools\ General Options\Always make
backup" but this only works for "Save As" not just "Save"

TIA

Stev

--
Message posted from http://www.ExcelForum.com

If you let excel help you when you create that sub, you'll be better off.

Rename your sub to something else.

Then use the dropdowns at the top of the code window to choose both the Workbook
(not (General)) and Before_Save event.

You should see the procedure shell start like:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

=======
But I don't think "Always create backup" works the way you say. You change the
option via the file|saveas dialog, but it'll create a backup with a normal Save,
too.

I think I would have used the beforesave event. You may want to look at
responses to your other posts.




"selsley <" wrote:

I pasted this into the ThisWorkbook module:

Private Sub Workbook_BeforeClose()
ActiveWorkbook.SaveAs ("C:\Test.xls")
End Sub

However, after making changes to the workbook and saving I get the
following error message on exit:

Compile error: Procedure declaration does not match description of
event or procedure having the same name

Being a complete novice at VB can you put me right please?

I tried Excel's own "File\Save as\Tools\ General Options\Always make a
backup" but this only works for "Save As" not just "Save"

TIA

Steve

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson