How to invoke macros in PERSONAL.XLSB ?

[bda75Participant Started: 8Replies: 9Total: 17]

I have workbooks in a folder and I would like to run a macro on each of them as part of the refresh task (1 task to refresh the whole folder).
I do not want the macro to be included in each file. Rather, I would like to store it in the PERSONAL.XLSB workbook.

I have created simple macro that inserts a text string, and invoke it by pasting “PERSONAL.XLSB!Module1.Macro1” in the task settings box. I have tried various combinations, but to no avail.

I have no issue when running a macro which resides inside the workbook being refreshed, but it’s not what I need. I would like to use a single macro for many workbooks without having to copy the code to each. And I would like the updated workbooks to be macro-free.

Your help is welcome.

[CharlesModerator Started: 2Replies: 34Total: 36]

Have you tried PERSONAL.XLSB!Macro1 ?

[bda75Participant Started: 8Replies: 9Total: 17]

This was my first attempt. Unsuccessful.

[bda75Participant Started: 8Replies: 9Total: 17]

So is it supported ? How should Macro1 in Module1 of PERSONAL.XLSB workbook be invoked ?

[bda75Participant Started: 8Replies: 9Total: 17]

1. So after a little bit of trial and error, here are my findings which may be of interest to some others:
   PU does not load PERSONAL.XLSB when opening a file to be refreshed, so no wonder it couldn’t run macro in this file.
2. Installed AddIns are loaded as expected. So one can store their any macro that needs to be run. I have named mine “PowerUpdateMacros.xlam”.
3. But in order to run a macro in an addin, PU apparently requires the full path to it (I learnt it from the failed task history)

so in the end, here is the PU macro setting was successful in my case:
‘C:\Users\myusername\AppData\Roaming\Microsoft\AddIns\PowerUpdateMacros.xlam’!mymacro

Note that the module name is not required when invoking the macro, which is different from when you run a macro that resides in the updated workbook itself.

[Author]

Posts