I need to save a duplicate copy of a workbook each time it is saved,
overwritting the duplicate. I tried this code and it did not work. No
errors but it did not create the copy.

Private Sub Worksheet_beforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveCopyAs "g:\EHSAdr.xls"
End Sub

Thanks!

When you go into the VBE, double click on the ThisWorkbook object (in the
project explorer).

Then use the dropdowns at the top of the code window and choose Workbook. Then
use the righthand side dropdown to choose BeforeSave, you'll see that the event
isn't what you called it.

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

You can't make events up, so it's much better/safer to use those dropdowns to
get the correct event and arguments.

Marnie wrote:

I need to save a duplicate copy of a workbook each time it is saved,
overwritting the duplicate. I tried this code and it did not work. No
errors but it did not create the copy.

Private Sub Worksheet_beforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveCopyAs "g:\EHSAdr.xls"
End Sub

Thanks!

--

Dave Peterson

Dave
Thanks! That worked for a regular file name. However I am actually trying
to update a document on a Sharepoint site so the path name is a web address.
If I manually do a Save AS with the web address it works fine. If I put the
same web address in this code I get "Run-time error 1004. The file cannot be
found." Do you know if there is a limit to the number of characters that can
be used in a file name in this context?

Thanks
Marni

"Dave Peterson" wrote:

When you go into the VBE, double click on the ThisWorkbook object (in the
project explorer).

Then use the dropdowns at the top of the code window and choose Workbook. Then
use the righthand side dropdown to choose BeforeSave, you'll see that the event
isn't what you called it.

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

You can't make events up, so it's much better/safer to use those dropdowns to
get the correct event and arguments.

Marnie wrote:

I need to save a duplicate copy of a workbook each time it is saved,
overwritting the duplicate. I tried this code and it did not work. No
errors but it did not create the copy.

Private Sub Worksheet_beforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveCopyAs "g:\EHSAdr.xls"
End Sub

Thanks!

--

Dave Peterson

I will try that. Thanks!

"Dave Peterson" wrote:

I don't know anything about Sharepoint or how to save to a web location, but if
I recall correctly (and I may be in error), you're limited to 255 or 256
characters in the path.

Maybe you could experiment by making a few test folders and see when it blows
up.

Marnie wrote:

Dave
Thanks! That worked for a regular file name. However I am actually trying
to update a document on a Sharepoint site so the path name is a web address.
If I manually do a Save AS with the web address it works fine. If I put the
same web address in this code I get "Run-time error 1004. The file cannot be
found." Do you know if there is a limit to the number of characters that can
be used in a file name in this context?

Thanks
Marni

"Dave Peterson" wrote:

When you go into the VBE, double click on the ThisWorkbook object (in the
project explorer).

Then use the dropdowns at the top of the code window and choose Workbook. Then
use the righthand side dropdown to choose BeforeSave, you'll see that the event
isn't what you called it.

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

You can't make events up, so it's much better/safer to use those dropdowns to
get the correct event and arguments.

Marnie wrote:

I need to save a duplicate copy of a workbook each time it is saved,
overwritting the duplicate. I tried this code and it did not work. No
errors but it did not create the copy.

Private Sub Worksheet_beforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveCopyAs "g:\EHSAdr.xls"
End Sub

Thanks!

--

Dave Peterson


--

Dave Peterson

I got it to work by using SaveAs instead of SaveCopyAs. I also had to change
it to the BeforeClose event. In the BeforeSave event it saved the copy but
not the original file.

Thanks for your help!

"Marnie" wrote:

I will try that. Thanks!

"Dave Peterson" wrote:

I don't know anything about Sharepoint or how to save to a web location, but if
I recall correctly (and I may be in error), you're limited to 255 or 256
characters in the path.

Maybe you could experiment by making a few test folders and see when it blows
up.

Marnie wrote:

Dave
Thanks! That worked for a regular file name. However I am actually trying
to update a document on a Sharepoint site so the path name is a web address.
If I manually do a Save AS with the web address it works fine. If I put the
same web address in this code I get "Run-time error 1004. The file cannot be
found." Do you know if there is a limit to the number of characters that can
be used in a file name in this context?

Thanks
Marni

"Dave Peterson" wrote:

When you go into the VBE, double click on the ThisWorkbook object (in the
project explorer).

Then use the dropdowns at the top of the code window and choose Workbook. Then
use the righthand side dropdown to choose BeforeSave, you'll see that the event
isn't what you called it.

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

You can't make events up, so it's much better/safer to use those dropdowns to
get the correct event and arguments.

Marnie wrote:

I need to save a duplicate copy of a workbook each time it is saved,
overwritting the duplicate. I tried this code and it did not work. No
errors but it did not create the copy.

Private Sub Worksheet_beforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveCopyAs "g:\EHSAdr.xls"
End Sub

Thanks!

--

Dave Peterson


--

Dave Peterson

You may want to try .savecopyas in the beforesave event once more.

Marnie wrote:

I got it to work by using SaveAs instead of SaveCopyAs. I also had to change
it to the BeforeClose event. In the BeforeSave event it saved the copy but
not the original file.

Thanks for your help!

"Marnie" wrote:

I will try that. Thanks!

"Dave Peterson" wrote:

I don't know anything about Sharepoint or how to save to a web location, but if
I recall correctly (and I may be in error), you're limited to 255 or 256
characters in the path.

Maybe you could experiment by making a few test folders and see when it blows
up.

Marnie wrote:

Dave
Thanks! That worked for a regular file name. However I am actually trying
to update a document on a Sharepoint site so the path name is a web address.
If I manually do a Save AS with the web address it works fine. If I put the
same web address in this code I get "Run-time error 1004. The file cannot be
found." Do you know if there is a limit to the number of characters that can
be used in a file name in this context?

Thanks
Marni

"Dave Peterson" wrote:

When you go into the VBE, double click on the ThisWorkbook object (in the
project explorer).

Then use the dropdowns at the top of the code window and choose Workbook. Then
use the righthand side dropdown to choose BeforeSave, you'll see that the event
isn't what you called it.

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

You can't make events up, so it's much better/safer to use those dropdowns to
get the correct event and arguments.

Marnie wrote:

I need to save a duplicate copy of a workbook each time it is saved,
overwritting the duplicate. I tried this code and it did not work. No
errors but it did not create the copy.

Private Sub Worksheet_beforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveCopyAs "g:\EHSAdr.xls"
End Sub

Thanks!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson