VBA - Message Box for users while Save Workbook Data is in progress

[Former Member]

Hello Experts! Hope you can help us with one VBA-related question. This question is rather a workaround for one specific EPM behavior (we have SAP BPC 10.1 NW). When data submission is executed from a single tab (

SaveAndRefreshWorksheetData), the message "Saving Data... Please wait..." pops up. However, when data is submitted from a workbook (SaveAndRefreshWorkbookData), there's no warning message at all - the Excel appears 'frozen' until the process is complete. Now, we're working on a template that allows users submit a lot of data to the system at once. Users will have option to submit data either from each tab (for tweaks) or from the workbook (original monthly submission). Now, the problem with the workbook - there's no message that pops up and warn users to wait... thus, if submission takes 2-3 min, they might terminate Excel thinking something went wrong. Please, note - we don't want to replace 'submit workbook' functionality by submitting individual sheets in the code since ABAP needs to capture all accounts from all tabs for each intersection in order to package and run calculations correctly. Thus.... save workbook functionality is important. My questions:

1. Is there something we can do from the system perspective to enable "Saving Data... Please wait..." message when workbook is getting saved? I'm really surprised that the behavior is different here.

2. If the answer to q.1 is 'no', then would it be possible to handle this pesky issue with VBA? I was trying to insert message box, but my timing is not working correctly... ideally, we'd like the message box to be visible while the process is running or at least after a message that #### records are getting saved.

Any advice would be greatly appreciated!

Mariya

[former_member186338 · Accepted solution]

I have tested on EPM SP 33 Patch 1 and can confirm the bug! With SaveAndRefreshWorkbookData there is no "Saving Data... Please wait..."

And there is no setting to correct it, contact SAP support to fix the bug.

Workaround:

Non-modal VBA form for message is not a good solution, it will be visible for both SaveAndRefreshWorksheetData and SaveAndRefreshWorkbookData.

I recommend using Excel status bar with the following simple code (insert a module for this code):

Option Explicit

Public Function BEFORE_SAVE()
    Application.StatusBar = "Saving data. Please be patient..."
End Function

Public Function AFTER_SAVE()
    Application.StatusBar = False
End Function

During save the user will see:

[Former Member]

Vadim, you're the best! Thank you!!!

[former_member186338]

Well, my solution is better then nothing 🙂

[Former Member]

My apologies:

Version 10.0 SP 28 Patch 3 .NET 4.5

Build 9843

Thank you, Vadim!

[former_member186338]

First of all I am very surprised that EPM or AO SP/Patch is not provided! What client is used?