Two fold problems here, figured i’d put them together to save space…
For one, I’ve never had any problems compiling this app until now…but suddenly I get this…
I’m not sure how that even happened, but how can I fix that?
Secondly…kel helped me out with some code, but there is one small error message.
set temp_cell to (Offset temp_cell RowOffset 1)
It gave me a "Expected “,” but found identifier (-2741) error.
Anyone have any idea how to fix this?
(Full code is below)
-- building a list of lists, sub_db
set rec1 to "field 1a
field 2a
field 3a"
set rec2 to "field 1b
field 2b
field 3b"
set rec1_list to paragraphs of rec1
set rec2_list to paragraphs of rec2
set sub_db to {} -- list of lists
set end of sub_db to rec1_list
set end of sub_db to rec2_list
-- the list of lists looks like this
-- {{"field 1a", "field 2a", "field 3a"}, {"field 1b", "field 2b", "field 3b"}}
set the_file to (choose file) as string
tell application "Microsoft Excel"
launch
Activate
Open the_file
tell front Workbook
tell front Worksheet
set d_range to UsedRange
set last_cell to last Cell of d_range
set temp_cell to last_cell
repeat with this_rec in sub_db
set new_row_num to (Row of temp_cell) + 1
set temp_cell to (first Cell of Row new_row_num)
repeat with this_field in this_rec
set Value of temp_cell to this_field
set temp_cell to (Offset temp_cell ColumnOffset 1)
end repeat
end repeat
end tell
end tell
end tell
Note that the Project Gallery needs to be turned off.
I switched it over to records in rows and it looks just like yours. I couldn’t see any difference. Maybe it’s different in Office 2004 than Office X, but the records in columns worked. Here’s the changed script:
– building a list of lists, sub_db
set rec1 to “field 1a
field 2a
field 3a”
set rec2 to “field 1b
field 2b
field 3b”
set rec1_list to paragraphs of rec1
set rec2_list to paragraphs of rec2
set sub_db to {} – list of lists
set end of sub_db to rec1_list
set end of sub_db to rec2_list
– the list of lists looks like this
– {{“field 1a”, “field 2a”, “field 3a”}, {“field 1b”, “field 2b”, “field 3b”}}
– usually, records are entered in rows, but in your case records are columns, CHANGED 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 d_range to UsedRange
set last_cell to last Cell of d_range
set temp_cell to last_cell
repeat with this_rec in sub_db
set new_row_num to (Row of temp_cell) + 1
set temp_cell to (first Cell of Row new_row_num)
repeat with this_field in this_rec
set Value of temp_cell to (contents of this_field) – added this
set temp_cell to (Offset temp_cell ColumnOffset 1)
end repeat
end repeat
end tell
end tell
end tell
I don’t see that line in my script. Did you run the script? Does it work? Try looking in the dictionary for Excel. Do you see the Offset command and its parameters? You can debug your script also. Try making small scripts and looking at the results. You might try getting the UsedRange, getting the last cell, and try Offset that cell. Does it error?
I realized that I probably won’t have any blank information, as that would be a corruption of the previous code – this script is the only thing touching the database file. So, what I decided to do was just use this for assessing the number of rows, then adding one. Now I just have to take the data from each variable and put it into the worksheet. Would it be easiest to individually script each variable for a given column or should I make a list based out of the variables and then just input the truncated list?
set the_file to (choose file) as string
tell application "Microsoft Excel"
launch
activate
open the_file
tell front workbook
tell front worksheet
set d_range to first row index of (get end (used range of sheet 1 of active workbook) direction toward the bottom)
set new_range to d_range + 1
end tell
end tell
end tell
Personally, I like to work with the list of lists rather than list variables. To me, it’s easier, but finding out which can be more efficient is a good activity if you have the time. I can see:
L = {{1,2,3}, {4,5,6}, {7,8,9}}
better than:
a = {1,2,3}
b = {4,5,6}
c = {7,8,9}
When you use L above, you’ll probably use two repeat loops. You only need one variable as opposed to three and repeat loops are quick for AppleScript.
BTW, I had thought of just using the column numbers instead of the last cell used. But, I thought, since I had the variable already, that I use that. Glad you caught that.
Would it make any sense to make a list of lists if I only will be entering one row worth of information per execution of the application?
I have all the data as variables already.
Perhaps I could make like a list L
L = {Var1,Var2,Var3,VarX…} etc and then have the program just drop each record in the row according to the variable?
Or is there a way i could directly code
“Put var1” into column 1 of (preestablished row), put var 2 into column 2 of (preestablished row) ?
Obviously there ‘is’ a way, but what would be the most feasible? Sorry about so many questions, I’m trying to learn fast but need a little nudge in the right direction from someone with a lot more experience
I’m not experienced with Excel, but getting better with your help.
I should have changed it over earlier to entering by rows. Here’s what I got for entering a list into a new record:
set the_list to {1, “”, 3, 4}
tell application “Microsoft Excel”
Activate
set d_range to UsedRange of Worksheet 1
set last_record to last Row of d_range
set new_record to (Offset last_record RowOffset 1)
set Value of new_record to the_list
end tell
If you use the whole row, then you get all those "n/a’ entries. The way I figure is that you probably have field labels from the first to the last used column. The above script lets you enter the data from the list without those "n/a"s.
About the list of lists, I don’t know how you’re getting the data that is to be entered into the spreadsheet. You might end up with many records. Usually the best way to get data from one app to another, is to get all the data from the first app, then enter all the data into the second app. If you like this way, then you might use a list of lists to hold the data. Otherwise, you might get one record from app1, then enter that record into app2, then get another record from app1, then enter that into app2, etc. Many times you lose time going from one source to the other like this. That’s why getting all your data at once might be a better idea.
set rec1 to {1, “”, 3}
set rec2 to {4, 5}
set rec3 to {“”, 8, 9}
set sub_db to {rec1, rec2, rec3}
tell application “Microsoft Excel”
Activate
set d_range to UsedRange of Worksheet 1
set new_record to last Row of d_range
repeat with this_rec in sub_db
set new_record to (Offset new_record RowOffset 1)
set Value of new_record to this_rec
end repeat
end tell
Greetings – Heres the thing: All of the data that I need will be already grabbed earlier in the script. So I have all of the data stored in various global variables…like LN, FN, SEX, AGE, DUR, PROT…etc. So I’ve been checking the dictionaries and was having trouble locating a command string that would just create a big list…like…
set the_rec to the list {LN, FN, SEX, AGE, DUR, PROT, A1C, SBP, CHOL} obviously that syntax doesn’t work…do you know one that would?
next what I’m going to have to do is place that record list into the row that our d_range script found…so we’d have found that our .xls currently has 10 rows, it will add one. Then it will paste the list record (seperated by commas?) into each respective column in the order the list is in.
Example:
Cell H7 would be LN, I7 would be FN, J7 would be SEX…etc. This part should be pretty easy, I’ll keep trying to find the right command, but you may know offhand.
Let me know and then I should be DONE with this baby. I’ll post if I find it myself but its frustrated me amply…i’m learning though.
Ah yes, indeed I do get that N/A junk. However, i’ve never been able to run any of your scripts with the that (Offset last_record RowOffset 1) to work, it always gives me a “Expected ‘,’ but found identifier” error on that line…I’m not sure why that is at all. I think your way or some other workaround would work best…the other option I can see is if I can make it so that the only fields that can be altered are the 9 or whatever I’m editing…Perhaps you can help me figure out what the deal is with that error code.
