Looking to do the following with a series of Excel (.xlsm) files:
• Open an 2 Excel files (an xlsx file and an .xlsm file with a macro) in it
• When prompt pops up asking whether to Enable or Disable macros, select "Enable Macros"
• When file opens fully (after any calculations, etc.) — i.e. app becomes responsive — run a macro called "CleanTableDeleteRedAndExtraColumns" via App Dropdown Menu: Tools > Macro > Macros...
• When dialog box opens, press Run button
• Macro does its thing and pops up a SAVE AS... dialog box
• At that point, press Save button
• Press Cmd+W to close window (active window)
• Press Cmd+W to close (2nd excel file, the .xlsx file)
Now....the issue is that...when opening the .xlsm file — there are occassions where there are calculations being done within the file (each .xlsm file is different). So we need to wait for the file to fully open and become "active" (i.e. be able to do something in it) before moving to the next step. No 2 .xlsm files are the same, so we can't use arbitrary pauses to wait.
How do we solve this issue, please? Is there an action that waits for a window to become active before moving on to next action?
There are lots of actions that might be able to wait for a window to become active, but only if there's some change in Excel that can be measured by the action. Someone who has Excel (I don't) would have to be able to look for such a change. A typical kind of change that might occur in an app when it becomes active is a change in the menus of the app, perhaps the "Save" menu item is greyed out prior to the app becoming "active." Another kind of change might be a visual change that could be detected by the Find Image action, perhaps the toolbar icons change colour when the app is ready. Another kind of change might be a change to the list of processes (using the command: ps -ax). I have used all these approaches for similar problems in the past.
If Excel does not provide any system behaviour that lets you, the user, determine when the app is ready, then how would KM determine it?
KM can definitely look for and click the Enable Macros button.
If you set up a Workbook_Open event in your xlsm file, that code will automatically run when the file opens. So you wouldn't need to wait for the file to be ready before triggering the macro by targeting the UI. See here for how to do that.
It sounds like the last step of the macro pops up a Save As dialog, so KM should be able to watch for that. It also sounds like you may not even need the dialog if all you're doing is hitting the Save button. Is the save name prepopulated by the macro? Why not just use the macro to do the save as and skip the dialog altogether?
The macro should also be able to handle closing the xlsx file.
The only other part where I think you would need intervention by KM would be closing the xlsm file because a macro running in a workbook won't be able to close that same workbook. (At least AFAIK; I've never actually tried it.)