Howdy Folks, I’m another Applescript newbie in over my head. I’m working on a script to copy xl files into a master xl file. the files is a roster with student and class information. the number of students will vary. WIth help from a friend I have it about 80% where I want it. Need help with the rest. I hope its okay to ask multiple question about the script if not i do apologize.
-
when the script runs it asks for the location of the file. the files are titled Houston_Sam_DWI_Jan.xlsx. I have several files in a master folder that i am trying to get data from, but the script goes through one at a time.
-
I know i need to loop it somehow to go through all of the files containing “DWI” in the title, I just don’t know how to do it.
-
the script is set up to get a range of cells, but there are other individual cells i need to copy like dates(C7), Instructor(H7), and location(C11). How do i get these individual cells and paste them to the master doc: Location(E7), Dates (F7), Instructor(G7) and have them repeat down the column as the number of students from each of the classes populates the list.
-
finally, i have the master file name as annual report, the script does update the anual report file, but when it goes to save it creates a file named “sheet 1”. i just want it to update the annual report file and save all changes.
here is the script i am currently working with:
set master_path to alias "Users:bs:Desktop:master:Annual Report.xlsx"
get_all_files(master_path)
on get_all_files(master_path)
set example_path to choose file with prompt "Find an example file to work with"
transfer_data(example_path, master_path)
end get_all_files
on transfer_data(child_path, master_path)
tell application "Microsoft Excel"
set child_book to (open workbook workbook file name (child_path as string))
set child_doc to worksheet 1 of child_book
set master to worksheet 1 of (open workbook workbook file name (master_path as string))
set num to 15 --All lists start at index 12 or later, I'm putting 10 to be safe
set students to {}
tell child_doc --grab values from child document
repeat until (value of cell (("A" & num) as string)) is 1
set num to num + 1
end repeat
repeat until (value of cell (("B" & num) as string)) is ""
set end of students to {name:(value of cell (("B" & num) as string)), driver_id:(value of cell (("C" & num) as string)), DOB:(value of cell (("D" & num) as string)), pre_test:(value of cell (("J" & num) as string)), post_test:(value of cell (("K" & num) as string)), cert_id:(value of cell (("L" & num) as string))}
set num to num + 1
end repeat
end tell
tell master
set num to 7
log (value of cell (("B" & num) as string))
repeat until (value of cell (("B" & num) as string)) is ""
set num to num + 1
end repeat
repeat with student in students
set value of cell (("B" & num) as string) to name of student
set value of cell (("C" & num) as string) to driver_id of student
set value of cell (("D" & num) as string) to DOB of student
set value of cell (("H" & num) as string) to pre_test of student
set value of cell (("I" & num) as string) to post_test of student
set value of cell (("J" & num) as string) to cert_id of student
set num to num + 1
end repeat
save master
end tell
save child_book
close child_book
save active workbook in master_path
close active workbook
end tell
end transfer_data
Any help would be greatly appreciated.
AppleScript: 2.5.1
Browser: Safari 537.36
Operating System: Mac OS X (10.8)