Trying to shorten script

[skranch]

Hi there,

I am trying to pick about 40 different values from Excel to use in my script, below is just a snippet…is there a better way to do this? Also is there a limit to how long a script can be? ie. number of characters?

Thanks,

set rowid to 0
set colid to 0

repeat with currentOrder from 1 to orderCount
set rowid to rowid + 1

tell application “Microsoft Excel”
set cellPosition to “r” & rowid & “c” & colid as string
set variable1 to FormulaR1C1 of Range cellPosition
set colid to colid + 1

	set cellPosition to "r" & rowid & "c" & colid as string
	set variable2 to FormulaR1C1 of Range cellPosition
	set colid to colid + 1

	set cellPosition to "r" & rowid & "c" & colid as string
	set variable3 to FormulaR1C1 of Range cellPosition
	set colid to colid + 1

etc…etc…

end tell
end repeat

[jonn8]

In OS 9 I believe there is a 32K limit to the script length when using Script Editor. Other script editors allow longer scripts. I doubt your script needs to be nearly as long as you think. You could probably optimize your script significantly using a nested repeat. Something like:

set the_variables to {}
tell application “Microsoft Excel”
repeat with rowid from 1 to 10
repeat with colid from 1 to 3
set end of the_variables to FormulaR1C1 of Range (“r” & rowid & “c” & colid as string)
end repeat
end repeat
end tell
return the_variables

Jon

---

[This script was automatically tagged for color coded syntax by Convert Script to Markup Code]

[skranch]

Thanks Jon,

I can see the values being picked up in my event log window. Is there a way to assign values from the result to individual variables so that they can be used in the rest of my script.

ie. set the result to variable 1, variable 2, variable 3 etc…

[jonn8]

You’re better off using them from the list that is generated (the_variables). What are you doing with the values later in the script?

Jon

[skranch]

I am using the values to populate some online forms using IE, for example one of the values from Excel is assigned to a variable called ‘salutation’ in the script, this used to compare the values that are on the webform (salutationList), when a match is found the form is populated. There are about 5 different pages that script goes through to process one order.

In it’s current state the script is working ok in osx but ideally need to have it run on os9, unfortunately getting an error which is ‘text would be longer than its maximum possible length’ ?

set salutationList to {“Mr”, “Mrs”, “Miss”, “Ms”, “Dr”}
repeat with i from 1 to (count salutationList)
if item i of salutationList is equal to salutation then
–display dialog salutation
tell application “Internet Explorer”
Activate
do script “window.document.forms[2].salutation.options[ '” & i & “'].selected = true;”
do script “i=0”
end tell
end if
end repeat

[jonn8]

No, you don’t need OS X for this. If you know the number of items in the_variables list (in the sample I gave above, you’ll end up with 30 items), then you can assign individual variables to the items in the list using the following convention (assuming the_variables has 3 values):

set the_variables to {“a”, “b”, “c”} –this is for demo purposes, this will be generated by the script before this point
set {variable1, variable2, variable3} to the_variables
return "variable1 = " & variable1 & return & "variable2 = " & variable2 & return & "variable3 = " & variable3

Jon

---

[This script was automatically tagged for color coded syntax by Convert Script to Markup Code]

[skranch]

Appreciate your help Jon, exactly what i needed.javascript:emoticon(‘:D’)