I had an extensive set of Applescripts that I used to use in Excel X. When I switched to Excel 2004 for Mac, the scripts were rewritten automatically by the computer when viewed in the script editor. If I look at the identical files on another computer which is still using Excel X, they return to the way I originally wrote them. I have now started to rewrite them for Excel 2004, but I am getting hung on trying to identify a row and column number from the active cell. Here is the script:
tell application “Microsoft Excel”
activate
set RowNum to row of active cell
set ColNum to column of active cell
set formula r1c1 of active cell to “Comm”
set ColNum to (ColNum + 3)
set NewRange to “R” & RowNum & “C” & ColNum
select range NewRange
set formula r1c1 of active cell to “Commission”GSA”
set ColNum to (ColNum + 1)
set NewRange to “R” & RowNum & “C” & ColNum
select range NewRange
run script alias “OS X
Boot:DOCUMENTS:AppleScripts:SigTaxScripts:SigCheckNum.scpt”
end tell
It works until I hit the line “set ColNum to (ColNum + 3)” at which point it hangs and highlights the “3”. I believe I am not getting the script to return the row number and column number of the active cell properly. Does anyone know how to do this, or know of any resource where I could search to discover this? I tried the “Excel 2004 AppleScript Ref.pdf”, but unfortunately it didn’t talk about this aspect. What I am trying to do is to move the active cell 3 cells to the right on the spreadsheet. This all worked perfectly in Excel X.
Any suggestions would be greatly appreciated. I’d hate to have to go back to Excel X or switch to QuicKeys when I am so close to getting it to work again.
I figured someone more expert than I would have stepped-in by now…but here’s a snipped of code that works in Excel X and also worked in the previous version of Excel (Excel 2004?).
set currentRow to (currentRow + 1)
set currentCell to ("R" & currentRow & "C" & currentColumn) as text
Select Range currentCell
It comes from a routine that is updating a spreadsheet automatically so it scans an existing Excel document until it finds the empty row at the end of the column of data vales (currentRow). Alongside currentRow it’s tracking a static value held by currentColum.
The cell I’m trying to target is then formatted into Excel notation (no colon).
So in order to write, it simply increments the row by one and selects it to go to it.
I didn’t try your script, but the biggest difference seems to be that I went through alot of trouble all over my script to coerce to “as text” when dealing with cell values, and you’re trying to address the cells directly (without quotes or otherwise explicitly treating them as text). Maybe that’s the trick? shrug
Thanks Kevin and Stefan for the attempt. I tried several versions of your suggestions but couldn’t make it work yet. I’ll keep trying.
Just another bit of information. When I run the script, the “3” is highlighted and I get the message “Can’t make missing value into type number.” So I’m assuming that I can’t acquire the row number and column number and put them into the variable the way I have done it. Any other thoughts? Any other forums for this? I tried the Excel on Microsoft’s site, but didn’t get any responses. I’ll try to rephrase the question over there.
Thanks again, and I’ll let you know if I figure anything out.
How did you know how to do that? The offset command is buried on page 360 of the manual, and I would have never known to search for that command. The {RowNum, ColNum} reference is earlier, but the syntax of:
tell active cell to set {RowNum, ColNum} to {first row index, first column index}
would have been difficult at best for me. Can you reccommend any good references to learn AppleScript that are current enough to include Excel 2004? Also of your two scripts, is one method preferrable to the other? Using “offset” seems like it might be the simpler solution.
So, a thousand thanks. This was a tremendous help!