Easymorph v5 : Run Macro that triggers a "RefreshAll"

Support
#1

Create a simple excel file that connects through power query to another excel file (small data).
Create inside this excel file a macro like this :

Public Sub Refresh()

ThisWorkbook.RefreshAll

End Sub

Be sure that you approve the location of the file in excel so that macros can be run.

=> If I run the macro the data are refreshed and the table showing the data is up to date

=> If I use the new action Excel command > Run VBA macro it’s running in easymorph, macro is recognized because I have no error message, Output is empty. But If I go into my excel file to check the result I can see my data were not refreshed : the refresh did not work.

#2

For information I stayed with version before V5 and run macro with powershell.

Macro inside Excel is like this.

Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.ScreenUpdating = True

It was not me who write this macro in Excel.
This macro is also called in “Workbook_BeforeSave” function.
I doubt this is the right solution.

I saw that if there is pivot table and some columns are no longer available, there is GUI message that asks question in Excel. When this happens, EasyMorph can’t do anything because it can’t answer and Excel stays open indefinitely.

#3

Do you save the workbook in your VBA macro?

Also, did you try using the “Recalculate workbook” command in the “Excel command” action (in v5)? It does the same but better :slight_smile:

#4

@Florent helped me to find the issue. I tested it in PowerShell and I realized that the option “Enable background refresh” set on my excel power query was causing trouble. @dgudkov maybe you should indicate that in the documentation, as well as something about macro security because I guess that if options are not set to enable macros + trust location of excel, it must not work (although I did not test).

So after I could disable this background refresh (checked by default) it is ok. And recalculate is doing the job so it’s even better !

Thanks to both of you !