Suppose I want to go to the 2nd tab in an open Excel file (where I'm not necessarily in that tab to start, and where that tab doesn't have a fixed name), unprotect the sheet, and enter the formula shown below (which requires a modifier key) into cell J1. I know how to unprotect the sheet....
The formula is as follows, where [ctrl] means "press the ctrl key":
="Today's date is" & " [ctrl] ;"
The ctrl key followed by the semicolon enters the current date. Thus if you actually entered that formula into a cell on, say, 2/24/2023, you would see:
Today's date is 2/24/2023
Note 1: I can manually go to the 2nd tab, in case that part is tricky.
Note 2: I'd like to do this entirely within KM. I.e., I don't want to write a VBA script, since I've always had trouble saving them in Excel. If KM can't do it, then I'll just use Excel's "Record Macro" functionality. [Though I'd rather do it with KM, since IME that's more robust.]
Since AppleScript support is a bit broken in the latest Excel, here's a purely KM-actions way of doing this -- you may have to adjust the pauses to suit your machine:
As the first of the Excel-targetting actions, at a guess -- you need Excel to be the active app, but you might need to Unprotect the sheet to be able to select the cell (depending on your Protection settings).
I'm afraid the unprotection won't work at the begining; the focus needs to first move to the protected sheet (sheet 2), then that sheet can be unprotected, after which the text can be entered. But when I tried inserting the unprotection command after the 'Insert Text "Sheet2!J1 by Pasting', it failed. I then tried it one step down, after the 'Return" command (which is right before the 'Insert Text "Today's date is" ' command—it has to be prior to that, to allow text to be inserted into the protected sheet), and it also failed.
All you are doing is using KM to drive dialogs you can do by hand -- so do it manually and work out where you need to place the unprotect command. It might differ depending on the type and degree of protection that has been applied (do you need to include password entry?), but also on your version of Excel.
You probably need to put it after you OK the "Go To" dialog but before you try and enter data in the cell, so something like this:
Not sure how far you've gotten with this project, theorist. One problem I see is that you might have renamed sheet #2. The macro won't work if the sheet does not have its default name: "Sheet2."
I've actually worked on this problem, and could not find a satisfactory solution. My solution was to name cell R1C! (equivalently, A1) on the first worksheet (tab) of the workbook. Then, you can use KM to go to the cell with that name. The second step is to get to the next sheet, which can be done by typing (or having KM type) cmd-. These days, keyboards don't typically have a page down key, but it can effectively be typed using fn-. So, you have to press fn-cmd to get to the next worksheet. Then, you issue whatever string of keystrokes unprotects the sheet, use Edit>Find>Go to to get to the appropriate cell and enter the formula.
If the workbook has several worksheets, you can use KM to construct a loop that puts the same formula at the same location all the pages.
Sorry, theorist. I see now that you specified that the names of the worksheets can vary. The central problem is that Excel does not have any mechanism (as far as I know) to address worksheets by their order in the file. This makes it nigh impossible to move from an arbitrary cell on an arbitrary sheet to a specific cell on a given sheet.
There is an another approach you can try, which involves clicks. I found this approach less reliable, but then I was using QuickKeys, not KM and an earlier version of Excel, This approach may work better for you.
This approach uses the two arrowhead buttons on the lower left of the workbook window. The first step is to hold down the command key and click on the left arrowhead. This moves the scrolling row of tabs to the leftmost position.
Second step is to click on the first tab in the row. This should bring up the first worksheet. Now do the same steps as in my previous reply.
I'm a relatively (HA!) new user of KM, and haven't yet tested doing clicks with it, so I can't say how this method will work.
@QKRefugee : Yeah, as I mentioned at the beginning, the sheets don't have fixed names (i.e., the 2nd tab in each workbook is named differently). Sounds like you're saying that means the "Sheet2!J1" syntax offered by @Nige_S isn't going to work, because it doesn't mean "cell J1 in the 2nd sheet", but rather "cell J1 in the sheet named 'Sheet2'".
TBH, I didn't make much more effort beyond that because I've since got it working with an Excel Macro, and surprisingly robustly. Having said that, I'm thinking the fix to get it working in KM would be to get to the 2nd sheet the way I did with the Excel Macro. For that, I pressed FN–CTRL–UP ARROW* a sufficient number of times that, irrespective of how many sheets there are, you'll find yourself at the first sheet (pressing more has no effect, so it's fine to do this more times than needed), and then pressing FN–CTRL–DOWN ARROW once.
*EDIT: @QKRefugee 's shortcut for this (CMD + ARROW, etc.) is the default one. I use CMD + ARROW, etc. for other things, so I reset my shortcut for this action to FN-CTRL + ARROW. It's been so long since I did this that I forgot mine isn't the default shortcut!
But once I get to the 2nd sheet, I'm still unclear about how to tell KM to insert text into cell J1, i.e. what command I use to tell Excel to make J1 the active cell, since I don't know how to do that with Excel itself** (other than playing the same game I did with the tabs, i.e., hitting the left arrow and the up arrow a sufficient number of times to guarantee I'm in cell A1, and then hitting the right arrow 9 times to get to J1).
**EDIT 2: I'd missed @QKRefugee 's mention of Edit->Find-Go To to get to a cell. The shortcut for this is ^G, which is what @Nige_S was doing.
OK. So while the majority of AppleScript is broken in the current version of Excel, including going to specific cells, there's still some that works. How about grabbing the name of the second sheet and then using that in the macro?
There was also a problem go to the correct cell when sheet protection is set to not allow cell selection. We can get round that by using two "Go To"s, the first to go to the correct sheet so we can unlock it, the second to jump to the now unlocked cell. I've put these in as two Groups -- you may not need the second, depending on your protection settings.
If you don't want to use AppleScript, or don't want two "Go To" dialogs, you could combine the two methods -- your "multiple left-one-sheet" commands, then the "unprotect" step, then the "Go To" step but using $J$1 without the sheet name (because then it uses the current sheet).
(Annoyingly the Windows ⌃-Home key shortcut, which takes you to the first sheet of a workbook, doesn't seem to have an equivalent on the Mac -- else this would have been a lot easier!)
When you say this, what do you mean? Something like this works just fine for me using Excel 16.70, released on 14 Feb.
tell application "Microsoft Excel"
goto reference (range "B13" of active sheet)
set rng to range "$A$11" of worksheet "Sheet2"
goto reference rng
end tell
And later... It works! More annoyingly -- when I try the macro, including AppleScript, on another machine that's still on the older Excel... That works too. Go figure...
Thanks for the nudge, @roosterboy. Now we can make something tighter, that doesn't have to throw up two "Go To" dialogs. Here's a version that uses AppleScript to both unlock the sheet (with password dialog if required) and select the cell, then uses KM actions for the text entry. The sheet number and cell ref have been split out to KM variables to make them easier to change.
