assign variables in Excel...

bloppart · September 3, 2011, 8:54pm

Hi,

I’m trying to assign variables in applescript from cells in Excel.
I know that I have to make a loop, but I’ve no success with that.
The process is :
select first cell of first row
assign variable
select column+1
assign variable
select column+1 etc…
then
current row is current row+1
select first cell of current row
assign variable
select column+1
assign variable
select column+1 etc…
until row is empty

I’m not really a programmer and all I’m able to do is :

tell application "Microsoft Excel"
	Activate
	set SP to (" ") --for a space between vars in display dialog
	Select Cell "$A$1"
	CopyObject Selection
	set varA1 to (the clipboard as text)
	Select Cell "$B$1"
	CopyObject Selection
	set varB1 to (the clipboard as text)
	Select Cell "$C$1"
	CopyObject Selection
	set varC1 to (the clipboard as text)
	
	--next row
	Select Cell "$A$2"
	CopyObject Selection
	set varA2 to (the clipboard as text)
	Select Cell "$B$2"
	CopyObject Selection
	set varB2 to (the clipboard as text)
	Select Cell "$C$2"
	CopyObject Selection
	set varC2 to (the clipboard as text)
end tell

tell application "Finder"
	display dialog varA1 & SP & varB1 & SP & varC1 & SP & varA2 & SP & varB2 & SP & varC2
end tell

I’ve tried many ways to select the next column and next row but I can’t find any way… not to speak of the loop itself !

Any help, please ?

Phil

Model: MacPro
AppleScript: 2.3(118)
Browser: Firefox 5.0.1
Operating System: Mac OS X (10.6)

mikerickson · September 3, 2011, 11:08pm

You can get values from a worksheet in bulk rather than looping

set sp to " "
tell application "Microsoft Excel"
	set myArray to get value of (get range "A1:D3")
end tell
display dialog (item 1 of item 1 of myArray) & sp ¬
	& (item 2 of item 1 of myArray) & sp ¬
	& (item 3 of item 1 of myArray) & sp ¬
	& (item 1 of item 2 of myArray) & sp ¬
	& (item 2 of item 2 of myArray) & sp ¬
	& (item 3 of item 2 of myArray) & sp

myArray is a list.
Each element of myArray is a list,
Item 1 of myArray is a list of all the values in the first row of the range.

in my test file, this was the value of myArray
{{“val of A1”, “val of B1”, “val of C1”, “val of D1”}, {“val of A2”, “val of B2”, “val of C2”, “val of D2”}, {“val of A3”, “val of B3”, “val of C3”, “val of D3”}}

bloppart · September 4, 2011, 9:27am

Hi Mike,

Thanks for your response.
Array was something I heard about but never used… until your message; I played with it during 2 hours (some problems because of old version of Excel) this morning and succeeded to what I wanted to do.

tell application "Microsoft Excel"
	set myArray to get value of (get range "A1:E19")
end tell

set com1 to (item 1 of item 1 of myArray)
set latlong1 to (item 2 of item 1 of myArray)
set image1 to (item 3 of item 1 of myArray)
set image1a to (item 4 of item 1 of myArray)
set comm1 to (item 5 of item 1 of myArray)

set com2 to (item 1 of item 2 of myArray)
set latlong2 to (item 2 of item 2 of myArray)
set image2 to (item 3 of item 2 of myArray)
set image2a to (item 4 of item 2 of myArray)
set comm2 to (item 5 of item 2 of myArray)

set com3 to (item 1 of item 3 of myArray)
set latlong3 to (item 2 of item 3 of myArray)
set image3 to (item 3 of item 3 of myArray)
set image3a to (item 4 of item 3 of myArray)
set comm3 to (item 5 of item 3 of myArray)

-- and so on until the end of the datasheet

However, what I did’nt say is that I have 5 columns x 20 rows in my data sheet and 100 placeholders in the text file I want to populate. Sure I can do that with arrays, but should’nt it be less code lines with loops?

copy first cell/first row
assign to varNameBLAH(& column index)–gives varNameBLAH1
current column is current column+1
copy current cell/first row
assign to varNameBLOP(& column index)–gives varNameBLOP1
…and so on… until column 5

copy first cell/second row

assign to varNameBLAH(& column index)–gives varNameBLAH2
current column is current column+1
copy current cell/second row
assign to varNameBLOP(& column index)–gives varNameBLOP2
…and so on… until row 20, column 5

Now, all my vars are assigned; the script should switch to BBEdit (displaying in dialog box was just for control), search all placeholders with same names than my vars, just replace them… and voilÃ !..in theory :°)
Is that realistic ?

Thanks for your time

Phil

mikerickson · September 4, 2011, 10:44pm

I wouldn’t use all those variables. Their names are so regular that you can work from the array directly.

just Keep in mind that
“comN” is item 1 of of item N of myArray
“latlongN” is Item 2 of Item N of myArray
etc

Then you can do you loop easily.

In the Text file, what are you looking for / replacing?

bloppart · September 5, 2011, 1:41pm

Hi,

I want to replace place holders

com1…com19
comm1…comm19
image1…image19
imageA1…imageA19
latlong1…latlong19
lead1…lead19
here is what I’ve thought of :

tell application "BBEdit" to open file "Macintosh HD:Users:me:test-applescript.xml"

tell application "Microsoft Excel"
	set myArray to get value of (get range "B1:B19")
end tell

set N to 1

repeat with N from 1 to 19 -- I would have liked 'until formula of active cell is "" '
	set search_strings to "comm" & N -- search "comm&1...19" placeholders in text file
	set replace_strings to {(item N of myArray)} -- values replacing "comm" & N
	N = N + 1
	tell application "BBEdit"'s document 1
		replace (search_strings) using (replace_strings's item N) searching in it ¬
			options {returning results:0} saving no
	end tell
end repeat

All the placeholders beginning with “comm” have been replaced (not the number coming with) with “cour” (which is the first value of the array) and Applescript is then using “tour” which is the second value of the array.
I tried also adding :

repeat with P from 1 to count search_strings
	tell application "BBEdit"'s document 1
		replace (search_strings's item P) using (replace_strings's item N of myArray) searching in it ¬
			options {returning results:0} saving no
	end tell
	P = P + 1
end repeat

…with no success.
Is - “comm” & N - the right syntax?.. or is the loop wrong?
I don’t understand all what I do. I’m very new to programming.

Thanks for your help

Phil

mikerickson · September 5, 2011, 3:44pm

I don’t understand what you are trying to do with the TextEdit file.
When you say you want to replace comm1 with comm19, do you mean that you want to replace the string “comm19” with the value comm1, i.e. the value from A5 on the Excel sheet?

If so, have you looked at Word/Excell’s Mail Merge?

bloppart · September 5, 2011, 4:24pm

Mike,

I’m sorry; I was not clear in my explanations :
comm1 to comm19 in the xml file will be replaced with data in column 1, rows 1 to 19 from excel data sheet.
But maybe my AppleScript is very confusing :°)

com1 in xml file becomes “blue” from excel A1 cell
com2 in xml file becomes “red” from excel A2 cell
com3 in xml file becomes “green” from excel A3 cell… and so on
then
comm1 in xml file becomes “garden” from excel B1 cell
comm2 in xml file becomes “park” from excel B2 cell
comm3 in xml file becomes “road” from excel B3 cell… and so on
then
image1 in xml file becomes “plane” from excel C1 cell
image2 in xml file becomes “boat” from excel C2 cell
image3 in xml file becomes “car” from excel C3 cell… and so on…

(e.g. from MyArray A1:C3 in this case)

I think it’s more clear now !

Thanks