Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Workbook BeforeSave Event to Save Copy to Different Location
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 -- Cheers, Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Workbook BeforeSave Event to Save Copy to Different Location
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 wrote: 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 -- Cheers, Ryan -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Workbook BeforeSave Event to Save Copy to Different Location
Note: This workbook has a .xla extension. Does that matter? I save this
workbook either in the immediate window or in the VBE. -- Cheers, Ryan "RyanH" wrote: 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 -- Cheers, Ryan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Workbook BeforeSave Event to Save Copy to Different Location
I think so.
Add Me.save before your End Sub (Little details are important! <vbg) RyanH wrote: Note: This workbook has a .xla extension. Does that matter? I save this workbook either in the immediate window or in the VBE. -- Cheers, Ryan "RyanH" wrote: 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 -- Cheers, Ryan -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Workbook BeforeSave Event to Save Copy to Different Location
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 wrote: Note: This workbook has a .xla extension. Does that matter? I save this workbook either in the immediate window or in the VBE. -- Cheers, Ryan "RyanH" wrote: 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 -- Cheers, Ryan -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Workbook BeforeSave Event to Save Copy to Different Locati
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 "Dave Peterson" wrote: 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 wrote: 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 -- Cheers, Ryan -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Workbook BeforeSave Event to Save Copy to Different Locati
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 "Dave Peterson" wrote: 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 wrote: 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 -- Cheers, Ryan -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Workbook BeforeSave Event to Save Copy to Different Locati
#1. Not much difference in this case. Less typing for sure.
#2. I think so. See the other post(s). RyanH wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Cheers, Ryan -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Workbook BeforeSave Event to Save Copy to Different Locati
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) On Thu, 23 Oct 2008 09:00:04 -0700, RyanH wrote: 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? -- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Workbook BeforeSave Event to Save Copy to Different Locati
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 -- Cheers, Ryan "Chip Pearson" wrote: 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) On Thu, 23 Oct 2008 09:00:04 -0700, RyanH wrote: 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? -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Beforesave - Save copy on my desktop | Excel Programming | |||
Protect Worksheet / Workbook in BeforeSave Event | Excel Programming | |||
Save method and BeforeSave event | Excel Programming | |||
BeforeSave sub won't save another workbook when triggered by another event sub | Excel Programming | |||
BeforeSave workbook event | Excel Programming |