Copy pasting selected rows from active excel document to new document

Scripting Forums AppleScript | Mac OS X
#1

Hi,

I was wondering if someone could help me.

I have been working on trying to display certain rows of an excel document to be shown on a separate monitor (apple TV) and I found the inbuilt excel functions while quite strong aren’t exactly what I am looking for.

I was wondering if it is possible to have a multi line dialogue box where the user inputs a number of order codes and have AS copy and paste the rows that contain those order codes over to a new/predefined excel document.

For example:

Excel Document 1:
Order Code: Item: (other information on other columns)
12 1a
23 3c
34 6f
45 10j
56 4d

Apple script dialogue box:
12
45
56

Temp/defined excel document
Order Code: Item:
12 1a
45 10j
56 4d

Although there is one problem I may have trouble with as one part of the first spreadsheet as size columns and depending on the size depends on what cell gets filled in although I could solves this by replicating the headings in the original excel document if using a predefined document. although I would like to be able to clear the receiving document when the apple script is used again.

Sorry if this is a bit confusing but it would be a great help if someone can help.

Thanks,
Bruce

#2

Hi Bruce,

Hopefully this should give you a start.

set getCodesDialog to display dialog "Please enter the code numbers you wish to search" & return & "for as a comma separated list:" default answer "12,45,56"

set theCodeNumberList to my splitString((text returned of getCodesDialog), ",")

tell application "Microsoft Excel"

activate

	set theOriginalCodeList to {}
	set theOriginalCodeList to formula of used range of active sheet
	
	set theNewCodeList to {}
	
	repeat with thisSearchItem in theCodeNumberList
		repeat with thisItem in theOriginalCodeList
			if (item 1 of thisItem as text) = (thisSearchItem as text) then
				set theNewCodeList to theNewCodeList & {contents of thisItem}
			end if
		end repeat
	end repeat
	
	make new workbook

	set formula of range "A1:B1" to item 1 of theOriginalCodeList
	set formula of range ("A2:B" & (((count of theNewCodeList) + 1) as text)) to theNewCodeList

end tell


to splitString(aString, delimiter)
	set retVal to {}
	set prevDelimiter to AppleScript's text item delimiters
	log delimiter
	set AppleScript's text item delimiters to {delimiter}
	set retVal to every text item of aString
	set AppleScript's text item delimiters to prevDelimiter
	return retVal
end splitString

HTH