Macro for saving workbook data when pressing 'Save data' button on EPM ribbon

[Former Member]

Hello. I have an input form with 2 sheets and i need to make the button 'Save data' which is situated on EPM ribbon and adjusted  default to work as 'save and refresh worksheet data', to work as 'save and refresh workbookdata'. I have written this macro but still have some problems:

Function BEFORE_SAVE()

Dim epmapi_L As Object

Set epmapi_L = Application.COMAddIns("FPMXLClient.Connect").Object

If saveflag = False Then

ask = True

epmapi_L.SetUserOption "HideSubmitWarning", False

Call Save_workbook

End If

If ask = False Then

Exit Function

End If

End Function

________________________________

Public Sub Save_workbook()

Application.ScreenUpdating = False

Dim epmapi As Object

Set epmapi = Application.COMAddIns("FPMXLClient.Connect").Object

saveflag = True

epmapi.SaveAndRefreshWorkbookData

ask = False

End Sub

_____________________________

Function AFTER_SAVE()

saveflag = False

End Function

The problems are:

1. I have 2 consecutive Datasave warnings (one comes from initial save and one from Sub Save_workbook (epmapi.SaveAndRefreshWorkbookData).
2. When there are no data to save and i push Save button, i have one Warning about No data to save and then everything goes to deadlock. The form doesn't work until i reset macro in ALT+F11 window.
3. When i accept data save, the refreshing process begins and after it's finished i receive another warning window that says ' No data to save'. I press ok and input form works. But if i decline first save data warning window - i receive another one again, and after declining it - again everything deadocks.

Maybe someone had experience with task like this, will be grateful for help.

[former_member186338 · Accepted solution]

Tested code for your scenario:

' Set WinAPI Timer

Public Declare Function SetTimer Lib "user32" ( _

    ByVal hwnd As Long, _

    ByVal nIDEvent As Long, _

    ByVal uElapse As Long, _

    ByVal lpTimerFunc As Long) As Long

' Kill WinAPI Timer

Public Declare Function KillTimer Lib "user32" ( _

    ByVal hwnd As Long, _

    ByVal nIDEvent As Long) As Long

' Global var for timer

Public lngTimerID As Long

Dim epm As New FPMXLClient.EPMAddInAutomation

Dim blnSave As Boolean

Function BEFORE_SAVE() As Boolean

    If Not blnSave Then

        BEFORE_SAVE = False

        epm.SetUserOption "HideSubmitWarning", False

        blnSave = True

        lngTimerID = SetTimer(0&, 0&, 100&, AddressOf TimerProc)

    Else

        BEFORE_SAVE = True

    End If

End Function

Sub TimerProc(ByVal hwnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)

    If nIDEvent = lngTimerID Then

        KillTimer 0&, lngTimerID

        epm.SaveAndRefreshWorkbookData

        blnSave = False

    End If

End Sub

Vadim

[Former Member · Accepted solution]

Hi Vadim,

I am trying to capture the save worksheet result in the macro.If the pop up message says "data save is failed" or if "there is no data to save", I don't want to proceed further. If the data save is successful then I have other actions to perform after save.

I am using a macro button to invoke save data. is there a way to capture that event using vb code.

I appreciate your inputs.

Best regards,
Sailu

[former_member186338 · Accepted solution]

Please, open a new discussion!

[Former Member · Accepted solution]

Hi Vadim,

I opened a new  discussion. Can you please  advise.

Best Regards,

Sailu

[Former Member · Accepted solution]

Vadim, thanks for your help!

Tried your code and it works.

I have fixed my code and it seems to work as it should too:

Function BEFORE_SAVE()

Dim epmapi As Object

Set epmapi = Application.COMAddIns("FPMXLClient.Connect").Object

If saveflag = False Then

    saveflag = True

    BEFORE_SAVE = False

    epmapi.SaveAndRefreshWorkbookData

    Exit Function

    ElseIf saveflag = True Then

    saveflag = False 

End If

BEFORE_SAVE = True

End Function

[former_member186338 · Accepted solution]

I don't like an idea to run SaveAndRefreshWorkbookData inside BEFORE_SAVE!

In my code I simply cancel BEFORE_SAVE and launch SaveAndRefreshWorkbookData by timer.

Vadim

[former_member186338]

Please also read my answers here:

[former_member186338]

Hi Alexey,

You are doing slightly strange things

I recommend you to understand sequence of events - just add:

Debug.Print "something"

to BEFORE_SAVE, AFTER_SAVE, BEFORE_REFRESH, AFTER_REFRESH

And you will see how it's working Just look on Immediate window in VBA Editor. Don't use MessageBox!

Vadim