Forum Discussion
Triggering an Open event macro via .xlam or tasks scheduler or etc
Hello All,
i have put together a flow in Power Automate Desktop, which collects info from a special sheet installed/prepared on each .xls* format file of a company. The vba code of installing/monitoring that special sheet is ready, currently linked to AfterSave event, however Open might be also working and even better.
The problem is that installing (with other words the first run of vba code) should be somehow triggered but it is not working directly via .xlam. The use of PERSONAL.XLSB would be not preferred, neither the change of user file formats to .xlsm. Also checked some event types of tasks scheduler but it is not covering my issue i think. Maybe a way could be also that a PAD flow runs eg. every 10 minutes and check all open workbooks of all users if that is possible at all. Do you have any ideas?
3 Replies
- If you have access to Power automate I suggest you change the VBA to Office script, which you can trigger in a Power Automate Flow. See my intro here: https://jkp-ads.com/articles/excel-office-script-call-from-power-automate.asp
- JKPieterse
...just on a sidenote have a working solution now, however with some compromises:
--PERSONAL.XLSB is running a continuous macro, however with DoEvents, so not blocking the user processes
--every minutes the run of special sheet checker fewliner code is triggered if the overall open worksheet number =INFO("NUMFILE") is not less
--the counter value is taken (like harvesting) by the fewliner frequently but it is also not a problem if the last chunk is staying in a closed file (as the next time opening will take it anyway)
--deleted files (not Shift+Del) can be also sought for counter values later
--it is enough to check all the users' PERSONAL.XLSB by the PAD flow - JKPieterse
Hello Jan, thank you for your hints collection, i will go through your intro as it is pretty comprehensive info. Hopefully it is not restricted to OneDrive strored files only. Re the logical position of trigger it would be better by the way if it could be in the Excel instances of each user instead of one PAD instance (practically via a technical account). Eg if 10 concurrent user has 9 files open then PAD should be triggered 90x times sequentially while each Excel instance (or simply user) only 9x times but parallel. Again thanks for your tips i will be surely able to combine them with my future projects!
