Newbian help requested!

Ahh! ok guys, what I have is a series of variables (9 to be precise) from early up in a big script. I set the_rec to be a list of all of these variables. Ahh hell, read my script. What I need is a way to place the data from this list into the spreadsheet WITHOUT #NA/1 entries appearing (which does if you place the data on a random row without a defined range. Evidently “set value of pre_range to the_rec” doesn’t work for some random error. This is all done in Excel 2004.



set the_file to (choose file)
tell application "Microsoft Excel"
   activate
   open (the_file as alias)
   tell worksheet 1 of workbook 1
       set LN to "Doe"
       set FN to "Drew"
       set SEX to "Male"
       set AGE to 24
       set DUR to 267
       set PROT to 0
       set A1C to 45
       set SBP to 4554
       set CHOL to 2
       set the_rec to {LN, FN, SEX, AGE, DUR, PROT, A1C, SBP, CHOL}
       set d_rows to first row index of (get end (used range of sheet 1 of active workbook) direction toward the bottom)
       set next_row to d_rows + 1
       set pre_range to ("R" & next_row & "C1:R" & next_row & "C9")
       set value of pre_range to the_rec
   end tell
end tell

Please help me with a new way to do my last line!!

Model: Dual 2.4 GHz PowerPC G5
Browser: Internet Explorer 6.0
Operating System: Mac OS X (10.3.9)

I assure you there is nothing random about the error. That error message is telling you exactly what you are telling it to do that it can’t do :smiley:

set value of pre_range to the_rec
produces the error and tells you how AS is ‘perceiving’ your variables.

set value of is

Can’t set <> of

pre_range

“R6C1:R6C9”–This is what you told it prerange is with the statement

set pre_range to (“R” & next_row & “C1:R” & next_row & “C9”)

to the_rec

which AS thinks is “{“Doe”,“Drew”,“Male”,24,267,0,45,4554,2}” from your statement

set the_rec to {LN, FN, SEX, AGE, DUR, PROT, A1C, SBP, CHOL}

That error is not random and is the key to what your doing wrong. The “set value” statement is a prime suspect. Hope that helps.
SC

Ah yeah – i figured it was something with set value…but i couldn’t think of what to use instead of value…hmm. any help on that? the problem i ran into was that if i obviously can’t set (that range) to (the list) because that would change the range to some list, wouldn’t it?

I don’t have Excel in X, but

is right.
Have you checked your dictionary in Excel?

SC

By the way, I noticed you have Internet Explorer. If you grab scripts or script snippets off the web, this script I made is great; select text in a window, run the script and a dialog pops up asking you to name the selection. The selection is auto-saved to a Script Editor file on the desktop. This eliminates “copy and paste this in a new script editor window”.
http://scriptbuilders.net/files/bbspost2script1.x1.9.html

set pre_range to (“R” & next_row & “C1:R” & next_row & “C9”)-- is simply a string, so the command
set value of pre_range to the_rec
gives an error because you’re telling Excel to give you the Value of a string.

The proper syntax should be -again I don’t have a version for X-

set the Value of Range pre_range to the_rec

SC

As you’ve discovered, you’re better off using “A1” style references for this job.

There’s a potential problem with hard-wiring the last cell reference - in case the length of the list ‘the_rec’ should ever change at some stage. To accommodate this possibility, you might want to consider something like:

set the_rec to {"Doe", "Drew", "Male", 24, 267, 0, 45, 4554, 2}
tell application "Microsoft Excel" to tell sheet 1 of active workbook
	set rNum to (first row index of (get end (used range) direction toward the bottom)) + 1
	set range ("A" & rNum & ":" & (get address row rNum's cell (count the_rec)))'s value to the_rec
end tell

Awesome it works (thanks sitcom and kai). Next question and comment

Comment: In order to accomodate for the problems that could occur if there is already a document open in excel, i have added the following bit:


tell application "Microsoft Excel" to save file
		tell application "Microsoft Excel" to quit

and at the beginning, i removed the
“activate” and to mimic clicking on an excel document rather than opening the program and finding the document…i simply put


set the_file to (choose file)
tell application "Microsoft Excel"
	open (the_file as alias)

Ok, now my question becomes…everything works fine except Excel has a stupid “New file wizard” box that pops up on default and prohibits the script from closing excel until someone manually closes the box. Is there any way I can close that box via script?

Awesome it works (thanks sitcom and kai). Next question and comment

Comment: In order to accomodate for the problems that could occur if there is already a document open in excel, i have added the following bit:


tell application "Microsoft Excel" to save file
		tell application "Microsoft Excel" to quit

and at the beginning, i removed the
“activate” and simply put


set the_file to (choose file)
tell application "Microsoft Excel"
	open (the_file as alias)

Ok, now my question becomes…everything works fine except Excel has a stupid “New file wizard” box that pops up on default and prohibits the script from closing excel until someone manually closes the box. Is there any way I can close that box via script?

Can be dealt with more efficiently by
[b]
tell application “Microsoft Excel”
close every open window saving yes–Got the syntax from 10.1 Excel
end tell

set the_file to (choose file)
tell application “Microsoft Excel”
open (the_file as alias)
–continue script
[/b]-Not AS format because it’s not tested
SC

Closing windows with saving (as sitcom suggests) is probably a better way forward in this situation. It avoids a complete quit, which is probably a bit of overkill - not to mention another source of delay (since the rest of your script still needs Excel to be launched again, anyway).

However, you might also want to consider situations in which you (or some other user) may not wish to save a particular workbook in its current state. And once the script has started running, there’s no going back… :o

A alternative approach might be to leave any existing workbooks as they are, make sure that your script’s target file is the active workbook, let it do its stuff - and then allow the user to continue with any other work.

The following example shows one way of doing this. (It also includes a launch statement, to avoid any startup routines if Excel has not yet been launched.)

set the_file to (choose file of type {"XLS8"}) as Unicode text

tell application "Microsoft Excel"
	launch (* avoid any opening dialogs, etc. *)
	activate
	open the_file
	repeat until full name of active workbook is the_file
		delay 0.2
	end repeat
	
	-- continue with script --
	
end tell

My sentiments exactly- that’s why I put my words as such…
"Can be dealt with more efficiently by "
tell application “Microsoft Excel”
close every open window saving yes

The last option “yes” is really up to the user, to save/ not to save and should be handled with care.

Kai, is there a way to access the ID of the document or window ID, so you could tell Excel exactly which window you’re targeting?
SC

Agreed - although it might also be worth considering something like: ‘close windows saving ask’. (I realise you’re trying to second-guess what options might be available in all this, which ain’t that easy - especially when the software might differ significantly.) :slight_smile:

That’s a very good point, sitcom.

I wasn’t too concerned about the issue in this case, since the suggested script checked that the correct target was active - and was then able to execute the subsequent routine virtually instantaneously. Nevertheless, I appreciate where you’re coming from. During a longer operation, there’s always the risk that a user could activate another workbook, which would then become the target of the script - with potentially disastrous results. :o

While there’s no obvious unique identifier, it’s possible here to use a workbook’s name. Unlike some other applications, Excel won’t allow more than one file of a given name to be open at any time - even if they’re located in different folders.

So, given the possibility of loss of focus, I’d certainly prefer to go for a safer (if slightly longer) option - perhaps something like:

set filePath to (choose file of type {"XLS8"}) as Unicode text
tell application "Finder" to set fileName to file filePath's name

tell application "Microsoft Excel"
	launch
	activate
	if fileName is in name of workbooks then
		if workbook fileName's full name is not filePath then ¬
			display dialog "Another file named \"" & fileName & ¬
				"\" is already open. Please close it before continuing." buttons ¬
				{"Cancel"} default button 1 with icon 0
	else
		open filePath
		repeat until fileName is in name of workbooks
			delay 0.2
		end repeat
	end if
	tell sheet 1 of workbook fileName
		
		-- continue with script --
		
	end tell
end tell