Microsoft Excel - multiple documents to 1

Scripting Forums AppleScript | Mac OS X
,
#1

Here’s the situation:
I have about 150 Excel documents with the specifications for printers that we supply to.
For my purposes, it would be alot easier if the information was in one document. Then I could filter it and search all suppliers for certain information. The information is always contained in the same cells (ie address). It’s sort of like a reverse mail merge.
This sounds like a job for Applescript, but I am having trouble getting anywhere with it.
Does anyone know of a similar script, or a good resource to get me started.
Many thanks

Ian

Model: G5 Dual 2.7ghz
AppleScript: 1.10.3
Browser: Safari 417.9.2
Operating System: Mac OS X (10.4)

#2

Ian:

I can help you with this, and you are correct, having the data in the same cell of every document is perfect. Do you need to extract a single cell of data, or a group of cells? Additionally, are all the current Excel files in one folder, or scattered all over the place?

#3

Thanks Craig,
Ideally, I will grab various bits of information from all over the sheet, and place it into another document in a list form (under headers, so it can be filtered/sorted).
I have copied all the files into one folder on my desktop for easy access.

Many thanks

Ian

#4

Ian:

The script is a bit bulky, but it works. You select the folder that contains the .xls files for processing and it will go through them, extracting the data from the cells you list in the variable cells_want. (The script assumes that all files in the folder are .xls files.) It will then make a new sheet with a column for each group of data, so that the rows represent each sheet. The only problem that I am finding is if the target cell contains a formula or reference to another cell, then it returns missing value. I have not had time to dig into that. If you want some real daunting reading, check out the Excel Applescript Reference.

set all_files to {}
set pdk to path to desktop as Unicode text
set a to choose folder
repeat with aa in (list folder a)
	set end of all_files to (pdk & ((info for a)'s name) & ":" & aa)
end repeat
set {master_data, dummy} to {{}, {}}
set cells_want to {"b4", "b7", "h14"}--Here is the list of target cells for each sheet
tell application "Microsoft Excel"
	activate
	repeat with axf in all_files
		open workbook workbook file name axf
		repeat with c in cells_want
			copy value of cell c of active sheet to the end of dummy
		end repeat
		set end of master_data to dummy
		set dummy to {}
		close active workbook
	end repeat
	set col_num to count (item 1 of master_data)
	set new_sheet to make new workbook
	repeat with a_row from 1 to (count master_data)
		repeat with a_Col from 1 to col_num
			set value of cell a_Col of row a_row of active sheet to item a_Col of (item a_row of master_data)
		end repeat
	end repeat
end tell

Good luck,

#5

Craig,

Thank you for your script. I can’t test it as I only have a PC at home, but will have a look first thing on Tuesday morning.

Thanks once again

Ian

#6

Craig,

I have had time to use your script this morning and it works fantastically :smiley:

I will look at the code and try to figure out how it works. :confused:

Thank you

Ian

#7

Glad to hear it functions for you. Don’t be shy about posting back in this thread with specific questions; scripting Word or Excel involves a lot of scanning through their HUGE PDFs, and a lot of trial and error. Once I find anything that works, I save it away in my library, because I never want to go through all that junk again.