I'm currently developing a Keyboard Maestro command for Excel and I need some help with a few tasks:
The macro I'm working on is a loop, but I need it to reference a different column each time it runs. Instead of using arrow keys (since there are other processes in between), I'd like it to start from column A and increment to A+1 (B), A+2 (C), A+3 (D), etc. The same should apply for rows. How can I achieve this?
Once the macro finishes processing all the data in a row and encounters an empty cell to the right, how can I make Keyboard Maestro identify this and move down to the next row, repeating the column-by-column process?
Since the macro will be running for a long time, I'd like to leave it running overnight and ensure it stops when it finishes. Therefore, I want Keyboard Maestro to stop the process when there is no text in both the right cell and the cell below, and then display a message indicating that the process is complete.
Could someone help me with these tasks? Thanks in advance!
Excel has great support for AppleScript which in principle makes what you're trying to do somewhat more straightforward.
I can get you started with a KM subroutine I wrote to help another user out: it lets you read data from cell(s) in an Excel worksheet. You can find it here;
The original thread that led to this subroutine is here:
In terms of incrementing row and column indexes and testing a cell for emptiness is not really too difficult to do in KM.
As tiffle says, AppleScript is the ideal way to solve this, but since I'm not very good with AppleScript, I tend to solve problems like this using keystrokes. From what I gather, the number of rows and columns in the spreadsheet is fixed and can be determined in advance, manually, by yourself. So a macro could simply be told at the start how many rows and columns there are, and then loop through the table using keystrokes accordingly.
As I indicated above, instead of having the macro make the determination, it seems possible to manually determine the size of the table in advance, and feed that information to the macro.
Here's how I would start a macro to solve a problem like this:
I would suggest grabbing the value of the entire range you are reading all at once and looping through that in AppleScript rather than looping and grabbing one cell at a time. Not only will it be faster (because you aren't sending Apple Events back and forth for every single cell), but you also won't have to test if there are blank cells to the right and below in order to know when you are done (because you just loop through what you got and then you're done).
Obviously, this advice will be conditioned by what exactly you are doing within your loop to the cells and what you mean by "Once the macro finishes processing all the data in a row" but in general it's good advice when dealing with Excel and AppleScript.
Another vote for grabbing everything in one go then processing later. But if, for some reason, you can't do that...
Mac Excel doesn't have a single shortcut to get to the "Name box", but F6 will get you there eventually -- for me it takes 4 presses when a cell is the focus.
The good thing about the "Name box" is that you can address cells with the R1C1, so you only have to increment numbers and not work through the alphabet!
So your pseudocode would be something like:
-- make sure we start at A1, change if your first cell is different
set theRow to 1
set theColumn to 1
repeat 4 times
keystroke F6
end
insert by typing "r" & theRow & "c" & theColumn
keystroke Return
copy cell contents
while %SystemClipboard% is not empty -- this loop processes rows
while %SystemClipboard% is not empty -- this loop process columns within a row
-- process the cell contents here
-- presumably using the clipboard contents
set theColumn to theColumn + 1
repeat 4 times
keystroke F6
end
insert by typing "r" & theRow & "c" & theColumn
keystroke Return
copy cell contents
end while
set theColumn to 1
set theRow to theRow + 1
repeat 4 times
keystroke F6
end
insert by typing "r" & theRow & "c" & theColumn
keystroke Return
copy cell contents
end while
display dialog "Finished!"
Totally untested. But you should be able to convert the pseudocode into KM actions. Start with a small workbook and, instead of processing cells, flash up dialogs reporting their contents.
