I need immediate help figuring a quick way (a script or something) to extract rows from an excel sheet based on a list of ISBNs in a text file, and generate a new excel sheet with the same headers, but with only the list of ISBNs in the text file. The ISBNs are the same in the main excel sheet, the sheet contains 2000 ISBNs and I only need 250 of them.
Thanks,
Bill
Bill,
Post what you have so far and an example of the text file and the excel file in
its current state and what it should look like in its finished state. Also post
links to sample code you have found on MacScripter. This way others do not
duplicate the work you have already done.
Merry Christmas,
Craig
If each column of your data range has unique headers and there are no blank rows in the data range, Advanced Filter can be used.
-- get list of ISBN numbers from ???
set isbnList to {"0-553-24567-8", "0-345-31559-6"}
tell application "Microsoft Excel"
-- set ranges to work on
set sourceWorksheet to sheet "Sheet1" of workbook "Workbook2"
set destinationWorksheet to sheet "Sheet2" of workbook "Workbook2"
set dataRange to range ("A1:C6") of sourceWorksheet
set destinationRange to range ("A1:C1") of destinationWorksheet
set urCol to count of columns of used range of sourceWorksheet
set criteriaRange to get offset range ("A1") of sourceWorksheet row offset 0 column offset (urCol + 1)
set criteriaRange to get resize criteriaRange row size ((count of isbnList) + 1) column size 1
set value of cell 1 of criteriaRange to "ISBN"
repeat with i from 1 to count of isbnList
set value of cell (i + 1) of criteriaRange to item i of isbnList
end repeat
advanced filter dataRange action filter copy ¬
criteria range criteriaRange ¬
copy to range destinationRange
delete entire column of criteriaRange
end tell
Just ran the script. it works with small sets of numbes. but when I run on a 3000 column, columns from A-AN, I get out of range issues.
In the script to relfect what I just mentioned above, I changed some fields to:
(“A1:C6”) → (“A1:AN3000”)
(“A1:C1”) → (“A1:AN3000”)
did I do anything wrong?
Thanks,
Bill
My test file worked with a data range A1:A3000, with the changes you indicated
I would use a destination range of A1:AN1 rather than your A1:AN3000, but it worked either way. (Excel VBA would error with the larger destination range, so I picked up that habit.)
Which version of Excel are you using? I’m using Excel2004.
If you’re having size issues, where is your column of ISBN’s. A looping workaround could be written.
Here’s the script that worked for me.
-- get list of ISBN numbers from ???
set isbnList to {"0-123-456-7890", "345-6789-123"}
tell application "Microsoft Excel"
-- set ranges to work on
set sourceWorksheet to sheet "Sheet1" of workbook "testForScript.xls"
set destinationWorksheet to sheet "Sheet2" of workbook "testForScript.xls"
set dataRange to range ("A1:AN3000") of sourceWorksheet
set destinationRange to range ("A1:AN1") of destinationWorksheet
set urCol to count of columns of used range of sourceWorksheet
set criteriaRange to get offset range ("A1") of sourceWorksheet row offset 0 column offset (urCol + 1)
set criteriaRange to get resize criteriaRange row size ((count of isbnList) + 1) column size 1
set value of cell 1 of criteriaRange to "ISBN"
repeat with i from 1 to count of isbnList
set value of cell (i + 1) of criteriaRange to item i of isbnList
end repeat
advanced filter dataRange action filter copy ¬
criteria range criteriaRange ¬
copy to range destinationRange
delete entire column of criteriaRange
end tell
I am running 2004 as well. I just ran the script, and get the error that range in the sheet I am extracting data from doesn’t understand advanced filter!
I don’t know how to respond.
As I recall “Illegal Extract…something or other” is the error one gets when there are data columns that either have no header or duplicate headers.
OR…the header in the Criteria range (in this case “ISBN”) is not one of the data range headers.
If you PM me, I’ll give you my e-mail address so you can send me a file.
If you’re using Excel 2004, you might want to drive everything with VBA rather than AppleScript. The script posted is just a “translation” of a VBA routine.
ok. I figured my error. The sheet didn’t have columns!
so, not it works. but it copies all the rows not only the matching records! what you suggest.
Can you post the script you are using?
I’m not sure what you mean by a sheet not having columns.
Have you tried to get Advanced Filter to work from within Excel?
I just thought of something else, if the isbnList contains an inadvertant null string, eg. {“”, “0-123-456-7890”, “345-6789-123”} it will move all the rows.
after several attempts, I figured out what was wrong. The first column MUST be named ISBN.
The next I like to do with the script is to read a text file with a list of ISBNs instead of manually cleaning up the file. the text file will have ISBNs listed one ISBN on each line. can you help with that.
-- get list of ISBN numbers from file
set isbnList to listFromFile()
tell application "Microsoft Excel"
-- Filters data for isbnList
-- set ranges to work on
set sourceWorksheet to sheet "Sheet1" of workbook "testForScript.xls"
set destinationWorksheet to sheet "Sheet2" of workbook "testForScript.xls"
set dataRange to range ("A1:AN3000") of sourceWorksheet
set destinationRange to range ("A1:AN1") of destinationWorksheet
set urCol to count of columns of used range of sourceWorksheet
set criteriaRange to get offset range ("A1") of sourceWorksheet row offset 0 column offset (urCol + 1)
set criteriaRange to get resize criteriaRange row size ((count of isbnList) + 1) column size 1
set value of cell 1 of criteriaRange to "ISBN"
repeat with i from 1 to count of isbnList
set value of cell (i + 1) of criteriaRange to item i of isbnList
end repeat
advanced filter dataRange action filter copy ¬
criteria range criteriaRange ¬
copy to range destinationRange
delete entire column of criteriaRange
end tell
on listFromFile()
-- gets list to filter from user chosen file
set uIstring to choose file
tell application "TextEdit"
open uIstring
set textDoc to words of document 1
close document 1
quit
return textDoc
end tell
end listFromFile
cool. That works. I will mess with it to make some more changes, but thanks a lot for your help.