How to search files, get cells, loop, and save

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.

  1. 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.

  2. 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.

  3. 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.

  4. 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)

Hi Sam, welcome to MacScripter.

The skeleton script below addresses your points 1,2 & 4.
You’ll have to be more precise (actually, 100% precise and complete…) about the data you need to move. It looks like each line in a source file contains data for one student, but it’s not entirely clear what other cells you want to copy. I think we can loose the students list of records entirely, which will speed up things tremendously.

And maybe you should study Excel’s (massive) AppleScript dictionary a bit more. It’ll take time up front, but save a lot of coding later.

-- using meaningful variable names is a good thing
-- as you'll discover when you are trying to modify a script you wrote a year ago

set pathToTargetWorkbook to "Users:bs:Desktop:master:Annual Report.xlsx" -- drop 'alias', and save a coercion later

set sourceFolder to choose folder with prompt "Select the folder with the student data files:"
tell application "Finder" to set sourceFiles to files of sourceFolder whose name contains "DWI" -- assuming these are all Excel files

tell application "Microsoft Excel"
	-- I believe there's something not quite right with Excel's 'active this-or-that'
	-- so I always explicitly declare any objects I need, which also helps de-confusing me
	set targetBook to open workbook workbook file name pathToTargetWorkbook
	set targetSheet to worksheet 1 of targetBook
	--
	-- this is where the work gets done
	-- the target workbook is already open
	-- now looping over the source workbooks
	repeat with aFile in sourceFiles
		set sourceBook to open workbook workbook file name (aFile as text)
		set sourceSheet to worksheet 1 of sourceBook
		
		tell sourceSheet -- to determine where the data are
			-- data handling code omitted
		end tell
		
		tell targetSheet -- to determine where the data should go
			-- data handling code omitted
			-- should not do this when in tell block for same object. And save a sheet? Maybe not what you want.
			-- if you must, use 'save', without object reference
			#save targetSheet
		end tell
		
		-- move the data!
		-- here's where reading the dictionary pays off - bigtime
		copy range sourceRange destination targetRange
		
		#save sourceWorkbook - you only read from it, saving is not necessary
		close sourceBook saving no -- suppress dialog, just in case
		#save active workbook in pathToTargetWorkbook - may not yield what you expect
		save targetBook -- in pathToTargetWorkbook - not needed, it's known
		#close active workbook-- don't close until done
	end repeat
	-- all done, can now save and close target book
	close targetBook saving yes -- not using 'active workbook' to avoid unexpected result
end tell