I want to say thanks in advance, this board has been a great help. My current issue is that i have a group of defined variables that are passed from Excel they are defined as valueOfCell6, valueOfCell7… I want to run a loop that evaluates the “values” of these variables and performs functions accordingly. How do I increment these values and run the loop. I set the intiial values and then begin but i am doing something wrong.
tell application "System Events"
set valueColumn to 6
set cellValue to "valueOfCell" & valueColumn
repeat while cellValue is not equal to "t"
set cellValue to value of "valueOfCell" & valueColumn
if (cellValue = "x") then
--key code 49
keystroke "this was an x"
delay 0.2
key code 48
delay 0.2
else if (cellValue = " ") then
--key code 48
keystroke "this was blank"
delay 0.2
end if
set valueColumn to valueColumn + 1
end repeat
keystroke "this was an the A for accept"
delay 0.2
end tell
What is the overall goal of this script.
What is the keycode 48 doing?
It looks like you might be looping down an Excel column and Putting
“this was an X” or “this was blank” into appropriate cells until you find a cell with a “t” and putting “This was A for Accept” in that cell.
If that is what you are doing, this avoids using a send key aproach.
tell application "Microsoft Excel"
set myWorksheet to get active sheet
-- start loop for top cells
set myCell to get range "A4" of myWorksheet
set maxRow to count of rows
set maxRow to first row index of (get end (row maxRow of entire column of myCell) direction toward the top)
-- maxRow is so the lack of "t" doesnt lead to a huge loop
repeat while value of myCell ≠"t" and ((first row index of myCell) ≤ maxRow)
if value of myCell = "x" then
set value of myCell to "this was an x"
else if value of myCell = "" then
set value of myCell to "this blank"
end if
set myCell to get offset myCell row offset 1
end repeat
set value of myCell to "end"
-- end topcell loop
end tell
No Im actually not in Excel. I have to send keystrokes as this is inputting values into a Java Applet that is not scriptable. I just need to know how to increment the variables. Here is the entire script but it is pretty long.
--Setting the Global Variables
set rowNumber to 3
set sheetNumber to 1
--Setting the worksheet names
set work1 to "49001 PHY (0-1 Male)"
set work2 to "49001 PHY (1-5 Male)"
set work3 to "49001 PHY (5-13 Male)"
set work4 to "49001 PHY (13+ Male)"
set work5 to "49001 PHY (0-1 Female)"
set work6 to "49001 PHY (1-5 Female)"
set work7 to "49001 PHY (5-13 Female)"
set work8 to "49001 PHY (13+ Female)"
repeat while sheetNumber < 9
--Activates Excel and sets first variable
tell application "Microsoft Excel"
-- Activates Microsoft Excel.
activate
-- Activates the Correct Worksheet
tell active workbook
--set b to "work" & sheetNumber
activate object worksheet "49001 PHY (0-1 Male)"
end tell
-- SETS THE VALUES OF THE FIRST REPORT ID TO VERIFY IT IS NOT BLANK
set a to "A" & rowNumber
copy value of cell a to valueOfCell1
end tell
repeat while valueOfCell1 is not equal to " "
--Activates Excel and sets variables
tell application "Microsoft Excel"
-- Activates Microsoft Excel.
activate
-- Activates the Correct Worksheet
tell active workbook
--activate object worksheet work & sheetNumber
activate object worksheet "49001 PHY (0-1 Male)"
end tell
-- SETS THE VALUES OF THE CELLS
set a to "A" & rowNumber
copy value of cell a to valueOfCell1
set a to "B" & rowNumber
copy value of cell a to valueOfCell2
set a to "C" & rowNumber
copy value of cell a to valueOfCell3
set a to "D" & rowNumber
copy value of cell a to valueOfCell4
set a to "E" & rowNumber
copy value of cell a to valueOfCell5
set a to "F" & rowNumber
copy value of cell a to valueOfCell6
set a to "G" & rowNumber
copy value of cell a to valueOfCell7
set a to "H" & rowNumber
copy value of cell a to valueOfCell8
set a to "I" & rowNumber
copy value of cell a to valueOfCell9
set a to "J" & rowNumber
copy value of cell a to valueOfCell10
set a to "K" & rowNumber
copy value of cell a to valueOfCell11
set a to "L" & rowNumber
copy value of cell a to valueOfCell12
set a to "M" & rowNumber
copy value of cell a to valueOfCell13
set a to "N" & rowNumber
copy value of cell a to valueOfCell14
set a to "O" & rowNumber
copy value of cell a to valueOfCell15
set a to "P" & rowNumber
copy value of cell a to valueOfCell16
set a to "Q" & rowNumber
copy value of cell a to valueOfCell17
set a to "R" & rowNumber
copy value of cell a to valueOfCell18
set a to "S" & rowNumber
copy value of cell a to valueOfCell19
set a to "T" & rowNumber
copy value of cell a to valueOfCell20
set a to "U" & rowNumber
copy value of cell a to valueOfCell21
set a to "V" & rowNumber
copy value of cell a to valueOfCell22
set a to "W" & rowNumber
copy value of cell a to valueOfCell23
set a to "X" & rowNumber
copy value of cell a to valueOfCell24
set a to "Y" & rowNumber
copy value of cell a to valueOfCell25
set a to "Z" & rowNumber
copy value of cell a to valueOfCell26
set a to "AA" & rowNumber
copy value of cell a to valueOfCell27
set a to "AB" & rowNumber
copy value of cell a to valueOfCell28
set a to "AC" & rowNumber
copy value of cell a to valueOfCell29
set a to "AD" & rowNumber
copy value of cell a to valueOfCell30
set a to "AE" & rowNumber
copy value of cell a to valueOfCell31
set a to "AF" & rowNumber
copy value of cell a to valueOfCell32
set a to "AG" & rowNumber
copy value of cell a to valueOfCell33
set a to "AH" & rowNumber
copy value of cell a to valueOfCell34
set a to "AI" & rowNumber
copy value of cell a to valueOfCell35
set a to "AJ" & rowNumber
copy value of cell a to valueOfCell36
set a to "AK" & rowNumber
copy value of cell a to valueOfCell37
set a to "AL" & rowNumber
copy value of cell a to valueOfCell38
set a to "AM" & rowNumber
copy value of cell a to valueOfCell39
set a to "AN" & rowNumber
copy value of cell a to valueOfCell40
set a to "AO" & rowNumber
copy value of cell a to valueOfCell41
set a to "AP" & rowNumber
copy value of cell a to valueOfCell42
set a to "AQ" & rowNumber
copy value of cell a to valueOfCell43
set a to "AR" & rowNumber
copy value of cell a to valueOfCell44
set a to "AS" & rowNumber
copy value of cell a to valueOfCell45
set a to "AT" & rowNumber
copy value of cell a to valueOfCell46
set a to "AU" & rowNumber
copy value of cell a to valueOfCell47
set a to "AV" & rowNumber
copy value of cell a to valueOfCell48
set a to "AW" & rowNumber
copy value of cell a to valueOfCell49
set a to "AX" & rowNumber
copy value of cell a to valueOfCell50
set a to "AY" & rowNumber
copy value of cell a to valueOfCell51
set a to "AZ" & rowNumber
copy value of cell a to valueOfCell52
set a to "BA" & rowNumber
copy value of cell a to valueOfCell53
set a to "BB" & rowNumber
copy value of cell a to valueOfCell54
set a to "BC" & rowNumber
copy value of cell a to valueOfCell55
set a to "BD" & rowNumber
copy value of cell a to valueOfCell56
set a to "BE" & rowNumber
copy value of cell a to valueOfCell57
set a to "BF" & rowNumber
copy value of cell a to valueOfCell58
set a to "BG" & rowNumber
copy value of cell a to valueOfCell59
set a to "BH" & rowNumber
copy value of cell a to valueOfCell60
set a to "BI" & rowNumber
copy value of cell a to valueOfCell61
set a to "BJ" & rowNumber
copy value of cell a to valueOfCell62
set a to "BK" & rowNumber
copy value of cell a to valueOfCell63
set a to "BL" & rowNumber
copy value of cell a to valueOfCell64
set a to "BM" & rowNumber
copy value of cell a to valueOfCell65
set a to "BN" & rowNumber
copy value of cell a to valueOfCell66
set a to "BO" & rowNumber
copy value of cell a to valueOfCell67
set a to "BP" & rowNumber
copy value of cell a to valueOfCell68
set a to "BQ" & rowNumber
copy value of cell a to valueOfCell69
set a to "BR" & rowNumber
copy value of cell a to valueOfCell70
set a to "BS" & rowNumber
copy value of cell a to valueOfCell71
set a to "BT" & rowNumber
copy value of cell a to valueOfCell72
set a to "BU" & rowNumber
copy value of cell a to valueOfCell73
set a to "BV" & rowNumber
copy value of cell a to valueOfCell74
set a to "BW" & rowNumber
copy value of cell a to valueOfCell75
set a to "BX" & rowNumber
copy value of cell a to valueOfCell76
set a to "BY" & rowNumber
copy value of cell a to valueOfCell77
set a to "BZ" & rowNumber
copy value of cell a to valueOfCell78
set a to "CA" & rowNumber
copy value of cell a to valueOfCell79
set a to "CB" & rowNumber
copy value of cell a to valueOfCell80
set a to "CC" & rowNumber
copy value of cell a to valueOfCell81
set a to "CD" & rowNumber
copy value of cell a to valueOfCell82
set a to "CE" & rowNumber
copy value of cell a to valueOfCell83
set a to "CF" & rowNumber
copy value of cell a to valueOfCell84
set a to "CG" & rowNumber
copy value of cell a to valueOfCell85
set a to "CH" & rowNumber
copy value of cell a to valueOfCell86
set a to "CI" & rowNumber
copy value of cell a to valueOfCell87
set a to "CJ" & rowNumber
copy value of cell a to valueOfCell88
set a to "CK" & rowNumber
copy value of cell a to valueOfCell89
set a to "CL" & rowNumber
copy value of cell a to valueOfCell90
set a to "CM" & rowNumber
copy value of cell a to valueOfCell91
set a to "CN" & rowNumber
copy value of cell a to valueOfCell92
set a to "CO" & rowNumber
copy value of cell a to valueOfCell93
set a to "CP" & rowNumber
copy value of cell a to valueOfCell94
set a to "CQ" & rowNumber
copy value of cell a to valueOfCell95
set a to "CR" & rowNumber
copy value of cell a to valueOfCell96
set a to "CS" & rowNumber
copy value of cell a to valueOfCell97
set a to "CT" & rowNumber
copy value of cell a to valueOfCell98
set a to "CU" & rowNumber
copy value of cell a to valueOfCell99
set a to "CV" & rowNumber
copy value of cell a to valueOfCell100
set a to "CW" & rowNumber
copy value of cell a to valueOfCell101
set a to "CX" & rowNumber
copy value of cell a to valueOfCell102
set a to "CY" & rowNumber
copy value of cell a to valueOfCell103
set a to "CZ" & rowNumber
copy value of cell a to valueOfCell104
set a to "DA" & rowNumber
copy value of cell a to valueOfCell105
set a to "DB" & rowNumber
copy value of cell a to valueOfCell106
set a to "DC" & rowNumber
copy value of cell a to valueOfCell107
set a to "DD" & rowNumber
copy value of cell a to valueOfCell108
set rowNumber to rowNumber + 1
set a to "a" & rowNumber
copy value of cell a to idChecker
end tell
repeat while idChecker is not equal to " "
-- Activates Text Mate
tell application "TextMate"
activate
end tell
tell application "System Events"
delay 2
key code 55
delay 0.2
keystroke "4"
delay 0.5
keystroke valueOfCell1 as text
delay 2
key code 36
delay 2
key code 36
delay 2
key code 48
delay 0.2
key code 48
delay 0.2
key code 48
delay 0.2
key code 48
delay 0.2
key code 48
delay 0.2
key code 48
delay 0.2
key code 48
delay 0.2
key code 48
delay 0.2
key code 48
delay 0.2
key code 48
delay 0.2
key code 48
delay 0.2
key code 48
delay 0.2
key code 48
delay 0.2
end tell
tell application "System Events"
set valueColumn to 6
set cellValue to "valueOfCell" & valueColumn
display dialog cellValue
repeat while cellValue is not equal to "t"
set cellValue to value of "valueOfCell" & valueColumn
if (cellValue = "x") then
--key code 49
keystroke "this was an x"
delay 0.2
key code 48
delay 0.2
else if (cellValue = " ") then
--key code 48
keystroke "this was blank"
delay 0.2
end if
set valueColumn to valueColumn + 1
end repeat
keystroke "this was an the A for accept"
delay 0.2
end tell
end repeat
end repeat
set sheetNumber to sheetNumber + 1
end repeat
The main problem lies in here
tell application "System Events"
set valueColumn to 6
set cellValue to "valueOfCell" & valueColumn
display dialog cellValue
repeat while cellValue is not equal to "t"
set cellValue to value of "valueOfCell" & valueColumn
if (cellValue = "x") then
--key code 49
keystroke "this was an x"
delay 0.2
key code 48
delay 0.2
else if (cellValue = " ") then
--key code 48
keystroke "this was blank"
delay 0.2
end if
set valueColumn to valueColumn + 1
end repeat
keystroke "this was an the A for accept"
delay 0.2
end tell
end repeat
This is the problem area where i cant find out the best way to set the incremented variable so that it pulls the next value set in the original set of variables pulled from excel.