Greetings guys, I hit a rock at the end of my last thread, but I know this can’t be that hard to fix with some workaround…although I can’t seem to think of a way to do it. So basically, I have a program that grabs a bunch of variables from a form. I want this script, each time the program is run, to grab each of those variables, open excel and add a row for the new information.
The Problem: Everything works with the following script, except every record not filled with a variable in the row is filled with “#N/A” likely because the script is trying to edit the whole row but doesn’t know what to put. How can I fix this? Is there any way to set the range (but dynamically so that it doesn’t depend on which row it is in)?
Please help me with a workaround for this.
set the_file to (choose file) as string
tell application "Microsoft Excel"
launch
activate
open the_file
tell front workbook
tell front worksheet
set LN to "Rodysill"
set FN to "Drew"
set SEX to "Male"
set AGE to 19
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_range to first row index of (get end (used range of sheet 1 of active workbook) direction toward the bottom)
set new_record to d_range + 1
set value of row new_record to the_rec
end tell
end tell
end tell
P.S. Kel has been very helpful in writing this code and offered a workaround, although since the inception of his code which is slightly different than mine, I’ve been unable to use part of his script, as two of the commands don’t seem to exist in my dictionaries.
I don’t have Excel 2004 but in Excel v.X, this works:
The key is to just get the range of cells you want to fill-in (R=row; C=column) and then set the value. Again, I don’t have Excel 2004 but this will probably work (or be close enough for you to figure out the rest:
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 "Rodysill"
set FN to "Drew"
set SEX to "Male"
set AGE to 19
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 next_row to (row 1 of (used range direction toward the bottom)) + 1
set new_range to range ("R" & next_row & "C1:R" & next_row & "C" & (count the_rec))
set value of new_range to the_rec
end tell
end tell
Greetings man and thanks a ton: One problem I got –
set next_row to (row 1 of (used range direction toward the bottom)) + 1
I get an error "Expected “,” but found identifier on the park “direction” Syntax error. I’ve seen this before on scripts that have worked from kel, etc. How can I fix this?
Browser: Internet Explorer 5.23
Operating System: Mac OS X (10.4)