Macro to Trigger Workbook_BeforeSave when Saving Template?

[former_member523998]

Hey everyone. I have a pretty basic question regarding VBA and BPC. The VBA event Workbook_BeforeSave() does not seem to work when saving BPC templates to the server. I know there's a similar issue with the Workbook_Open() event from the server, and one can create a regular macro after_workbook_open().

Does anyone know if there's a similar one for BeforeSave? I tried before_workbook_save() and it was unsuccessful.

[former_member186338 · Accepted solution]

Unfortunately there is no event triggered when you save to server folder. You can create you own button with macro using SaveToServerRootFolder. In this macro you can insert the required code (but even in this case it will not perform correctly if user press cancel)

[former_member523998 · Accepted solution]

Thanks. I hate accepting answers that are "NOPE!" but if it's correct then... accepted.

not sure if BPC developers read these boards but.....

Dear SAP...

Having a WORKBOOK_BEFORESAVE event would be a valuable feature to have. When making changes to templates, admins routinely forget to do certain things to reset a template (set context members properly, have blank templates, etc). To get around that, many developers such as myself will create a reset_macro of some kind. This saves time and ensures that admins won't forget any steps.

However, without the WORKBOOK_BEFORESAVE event, it requires the admin to remember to run this macro before a template is saved. Typically Admins DO remember, but if they forget the significance could be consequential (HR template with salaries displayed!).

Mr. Kalinin's suggestion isn't bad given the options, but it still requires the admin to remember to execute a macro when saving a template.

Hope that's useful.
��

[former_member186338]

It's strange to me, why the standard event Workbook_BeforeSave is not triggered...

Sample:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = CStr(Now())
End Sub

If I execute macro like:

Public Sub Save1()
ThisWorkbook.SaveAs "C:\Users\KalininVE\Desktop save.xlsm"
End Sub

Then standard event will work...

Looks like EPM code is doing something like:

Public Sub Save1()
Application.EnableEvents = False
ThisWorkbook.SaveAs "C:\Users\KalininVE\Desktop save.xlsm"
Application.EnableEvents = True
End Sub

P.S.

Even with disabled events it's still possible to execute Workbook_BeforeSave using Application.Run:

Public Sub SaveProc()
Application.EnableEvents = False
Application.Run "ThisWorkbook.Workbook_BeforeSave", False, False
Application.DisplayAlerts = False
ThisWorkbook.SaveAs "C:\Users\KalininVE\Desktop save.xlsm"
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

[former_member523998]

Woa... woa... hang on. I already accepted your answer, and gave it an upvote. Are you trying to acquire even more reputation build out of my question?? How about giving me an upvote?? I've got a score that reflects the same level of danger as a tree in Fortnite. 🙂

More seriously, yes, it behaves exactly as you describe with EnableEvents set to FALSE. I am guessing it doesn't actually make this application setting change, but rather the "Save To Server" doesn't trigger the event that Excel recognizes as a SAVE.

As far as your P.S. comment, yes all macros can be called using the Application.Run command, including Private and sometimes "unviewable" ones. However the whole point of event macros is to execute the procedure during "normal" saving behavior. So essentially what you list is no different from inserting any other Module.Macro into a subroutine. I'm sure you know this, but I thought I would clarify for other readers as it's more of an Excel tip than BPC.

[former_member186338]

Hi Steven Rider

I am talking about possible EPM code to perform save to server.

2 options:

1. Do not disable events.

2. If disable events is required for some reason (no idea why), then at least directly call BeforeSave using Application.Run

None of this options are used currently