Use Workbook BeforeSave Event to Save Copy to Different Location

[RyanH]

I have a workbook that I would like to save a copy to a different location
everytime I save the workbook. I use the BeforeSave Event. The copy saves
perfectly but it doesn't save to its original folder, why?

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

Dim strPrompt As String
Dim intButtons As Integer
Dim strTitle As String

On Error Resume Next
With ThisWorkbook

' save a copy in public folder in power vault
.SaveCopyAs ("\\Powervault\Global Schedule BU\" & ThisWorkbook.Name)

' if error occurs notify user
If Err.Number > 0 Then
strPrompt = "The back up file for " & ThisWorkbook.Name & "
was not saved in '\\Powervault\Global Schedule BU' folder."
strPrompt = strPrompt & " Please make a note of this and
notify Ryan."
intButtons = vbExclamation
strTitle = "Problem"
MsgBox strPrompt, intButtons, strTitle
End If
End With

End Sub

 

[Dave Peterson]

Your code worked ok for me. Are you sure you let the procedure finish?

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

Dim strPrompt As String
Dim intButtons As Integer
Dim strTitle As String

With Me
' save a copy in public folder in power vault
On Error Resume Next
.SaveCopyAs ("\\Powervault\Global Schedule BU\" & .Name)
' if error occurs notify user
If Err.Number <> 0 Then
err.clear
strPrompt = "The back up file for " & .Name _
& " was not saved in '\\Powervault\Global Schedule BU' folder."
strPrompt = strPrompt _
& " Please make a note of this and notify Ryan."
intButtons = vbExclamation
strTitle = "Problem"
MsgBox strPrompt, intButtons, strTitle
End If
on error goto 0
End With

End Sub

The code I posted has minor changes.

Since the code is in the ThisWorkbook module, I used the Me keyword instead of
ThisWorkbook. And since you were using the with/end with structure, I removed
the qualifiers from some of the properties you used.

 

[RyanH]

Note: This workbook has a .xla extension. Does that matter? I save this
workbook either in the immediate window or in the VBE.

 

[Dave Peterson]

I think so.

Add

Me.save
before your End Sub

(Little details are important! <vbg>)

 

[Dave Peterson]

I forgot to stop the .save from calling the event:

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

Dim strPrompt As String
Dim intButtons As Integer
Dim strTitle As String

MsgBox Me.Saved

With Me
' save a copy in public folder in power vault
On Error Resume Next
.SaveCopyAs ("\\Powervault\Global Schedule BU\" & .Name)
' if error occurs notify user
If Err.Number <> 0 Then
strPrompt = "The back up file for " & .Name _
& " was not saved in '\\Powervault\Global Schedule BU' folder."
strPrompt = strPrompt & " Please make a note of this and notify
Ryan."
intButtons = vbExclamation
strTitle = "Problem"
MsgBox strPrompt, intButtons, strTitle
End If
End With

'Application.EnableEvents = False
Me.Save
Application.EnableEvents = True

MsgBox Me.Saved

End Sub


Delete the msgboxes (Me.saved) when you're done testing.

 

[RyanH]

Since I am still learning VBA I have two questions.

1.) What is the difference in using Me and ThisWorkbook in this case?

2.) Does it matter that the workbook is an Add-In, meaning it has a .xla
extension?
--
Cheers,
Ryan

Your code worked ok for me. Are you sure you let the procedure finish?

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

Dim strPrompt As String
Dim intButtons As Integer
Dim strTitle As String

With Me
' save a copy in public folder in power vault
On Error Resume Next
.SaveCopyAs ("\\Powervault\Global Schedule BU\" & .Name)
' if error occurs notify user
If Err.Number <> 0 Then
err.clear
strPrompt = "The back up file for " & .Name _
& " was not saved in '\\Powervault\Global Schedule BU' folder."
strPrompt = strPrompt _
& " Please make a note of this and notify Ryan."
intButtons = vbExclamation
strTitle = "Problem"
MsgBox strPrompt, intButtons, strTitle
End If
on error goto 0
End With

End Sub

The code I posted has minor changes.

Since the code is in the ThisWorkbook module, I used the Me keyword instead of
ThisWorkbook. And since you were using the with/end with structure, I removed
the qualifiers from some of the properties you used.

 

[RyanH]

Since I am still learning VBA I have two questions.

1.) What is the difference in using Me and ThisWorkbook in this case?

2.) Does it matter that the workbook is an Add-In, meaning it has a .xla
extension? If I click save in the VBE it should fire the Add-In workbook
save event, right?
--

--
Cheers,
Ryan

Your code worked ok for me. Are you sure you let the procedure finish?

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

Dim strPrompt As String
Dim intButtons As Integer
Dim strTitle As String

With Me
' save a copy in public folder in power vault
On Error Resume Next
.SaveCopyAs ("\\Powervault\Global Schedule BU\" & .Name)
' if error occurs notify user
If Err.Number <> 0 Then
err.clear
strPrompt = "The back up file for " & .Name _
& " was not saved in '\\Powervault\Global Schedule BU' folder."
strPrompt = strPrompt _
& " Please make a note of this and notify Ryan."
intButtons = vbExclamation
strTitle = "Problem"
MsgBox strPrompt, intButtons, strTitle
End If
on error goto 0
End With

End Sub

The code I posted has minor changes.

Since the code is in the ThisWorkbook module, I used the Me keyword instead of
ThisWorkbook. And since you were using the with/end with structure, I removed
the qualifiers from some of the properties you used.

 

[Dave Peterson]

#1. Not much difference in this case. Less typing for sure.

#2. I think so. See the other post(s).

Since I am still learning VBA I have two questions.

1.) What is the difference in using Me and ThisWorkbook in this case?

2.) Does it matter that the workbook is an Add-In, meaning it has a .xla
extension?

 

[Chip Pearson]

The "Me" identifier (used only in Object Modules -- Class modules,
ThisWorkbook, the Sheet modules, and UserForm code modules) always
points to the object in which it is used. Within the ThisWorkbook
module, "ThisWorkbook" and "Me" behave the same; both refer to the
workbook in which the code resides. I typically use "Me" within the
ThisWorkbook module and "ThisWorkbook" in regular code modules, where
"Me" is not allowed.

It shouldn't matter whether the workbook is an XLS workbook or an XLA
add-in. The BeforeSave event works the same for an XLS and an XLA. If
your BeforeSave event (or any other event) is not firing as expected,
the likely cause is that events have been disabled. You can test this
in the VBA Editor. In the VBE, press CTRL G to display the Immediate
window. There, type the following (note the leading ? character):

?Application.EnableEvents

and press ENTER. The result, either True or False, will be displayed
in the Immediate window.

To turn events on (or off), enter the following in the Immediate
window and press ENTER (note that there is no ? in this line):

Application.EnableEvents = True ' True = on, False = off

If events are turned off, you need to review your code to find where
you are turning off events and not turning them back on. This often
happens when events are disabled by code and then the code tests for
some error condition and exits without turning events back on.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[RyanH]

Thanks for the responses! I did some further testing and here are my results.

I confirmed that enable events = True. If I open the Add-In workbook and
use the shortcut save button in VBE the 2 copies are saved but it is not
saved in its original folder I opened it up in. (Note: I confirm if it was
saved by looking in each folder and viewing the Last Modified Date and Time.)
If I use the immediate window and type, ThisWorkbook.Save it does not save
the copies, but saves in the original folder location. I am really scrathing
my head on this one! Any ideas?

This add-in is used as a reference for another workbook. Does it matter if
the add-in workbook is in use?

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

Dim strPrompt As String
Dim intButtons As Integer
Dim strTitle As String
Dim myUserName As String
Dim objNetwork As Object
Dim strBackUpPath As String

On Error GoTo ErrorHandler

With Me
' save a copy in public Power Vault folder
strBackUpPath = "\\Powervault\Global Schedule BU\"
.SaveCopyAs (strBackUpPath & .Name)

' get computer network user name
Set objNetwork = CreateObject("Wscript.network")
myUserName = objNetwork.UserName

' save a copy in Ryan's My Documents if ryanh is signed in
If myUserName = "ryanh" Then
strBackUpPath = "C:\Documents and Settings\ryanh\My
Documents\Ryan's BackUp Programs\Global Schedule BackUps\"
.SaveCopyAs (strBackUpPath & .Name)
End If
End With

Exit Sub

'---------------------
ErrorHandler:

' if error occurs notify user
If Err.Number > 0 Then
strPrompt = "The back up file for " & ThisWorkbook.Name & " may not
have been saved in '" & strBackUpPath & "'."
strPrompt = strPrompt & " Please make a note of this and notify
Ryan."
intButtons = vbExclamation
strTitle = "Problem"
MsgBox strPrompt, intButtons, strTitle
End If

End Sub