Page 1 of 2

How to use an excel list to search and replace in Quark docs

Posted: 14 Jul 2009, 00:29
by JLMahurin
Can anyone help on this with Quark 7?Search and replace from an Excel list, line by line, through 20 separate Quark docs (each Quark doc 32 pages long) and replace new item numbers for Quark from Excel.I had been doing this by hand ... 5000 items to search and replace on 20 quark docs. Usually takes days. I want to make this script more automated. Can anyone shine a light on how to make this work. Here is the Quark search / replace scriptdisplay dialog "Search for this number:" default answer "Search"set searchstring to text returned of resultdisplay dialog "Replace with this number:" default answer "Replace"set replaceString to text returned of resulttell application "QuarkXPress"activatemy SearchAndReplace(searchstring & "", replaceString & "")end tellon SearchAndReplace(searchString, replaceString)set sourceFolder to choose folder with prompt "Choose the folder to process."tell application "Finder" to set theFiles to files of folder sourceFolder whose kind contains "QuarkXPress"repeat with oneFile in theFilestell application "QuarkXPress"open oneFiletell document 1tell every storyset (every text where it is searchString) to replaceStringend tellend tellend tellend repeatend SearchAndReplacethis works quite well ... but really need some help getting the list line by line out of excel searching the documents and replacing the text with new text.Thank you in advance for help or suggestions...

How to use an excel list to search and replace in Quark docs

Posted: 14 Jul 2009, 00:39
by Emma
Excel is scriptable, but would a script be able to find the 'search' and 'replace' numbers?
Does the 'search' string exist in Excel, in the same line as the required 'replace' number?
Could you make a text file of sets of search and replaces, and get the script to run on them?
These are random thoughts, it's hard to visualise exactly what is going on. If you're doing this a lot, look at XCatalog from EmSoftware, which enables you to build 'links' into your Quark file which mean you can update the data from a text file (or an on-lne Filemaker database).
Sounds interesting, but we need to know more!

How to use an excel list to search and replace in Quark docs

Posted: 14 Jul 2009, 00:54
by JLMahurin
Thank you for your reply... I only do this project once each year. Generally an intern handled the actual search replace, however we have no intern this year and much to do with fewer resources. The excel rows are formatted exactly as formatted in quark with only a few exceptions... excel format
0123456 | \t | .123456 quark formats
0123456 | \t | .123456 or 0123456 | \r | .123456 there are quark tables but they only contain this format
0123456 | \r | .123456 thank you in advance ...

How to use an excel list to search and replace in Quark docs

Posted: 14 Jul 2009, 03:05
by Scripting_Ace
Excel is scriptable but it is rather a pain to do so.
I would strongly suggest you save your Excel document as tab delimited text file.
Providing that your Excel doc has rows of data in the form of Column A: Old Number, Column B: New Number, the resulting text file will be formatted as:
Old Number 1 [tab] New Number 1 [return]
Old Number 2 [tab] New Number 2 [return]
Old Number 3 [tab] New Number 3 [return]
...
You can easily covert that into a list of lists using the followinf code:

001set Search_File to (path to Your_TabDelimited_File) as text

002set Search_Rows to [b]read [b]file [b]Search_File [b]using delimiter {[b]return}
003[b]set [b]Search_Items [b]to {}
004[b]repeat [b]with [b]This_Row [b]in [b]Search_Rows
005[b]set [b]end [b]of [b]Search_Items [b]to [b]my [b]GetTextItem([b]This_Row [b]as [b]text, [b]tab, 0)
006[b]end [b]repeat

007[b]on [b]GetTextItem([b]ThisString, [b]ThisDelim, [b]ThisItem)
008-- ThisString -> String to look in
009-- ThisDelim -> Text element that delimit the string
010-- ThisItem -> Number of the element to return (0 for all)
011[b]copy [b]the [b]text item delimiters [b]to [b]OldDelims
012[b]set [b]the [b]text item delimiters [b]to [b]ThisDelim
013[b]if [b]class [b]of [b]ThisItem [b]is [b]list [b]then
014[b]set [b]fromItem [b]to ([b]item 1 [b]of [b]ThisItem) [b]as [b]integer
015[b]set [b]toitem [b]to ([b]item 2 [b]of [b]ThisItem) [b]as [b]integer
016[b]set [b]arrItem [b]to ([b]text items [b]fromItem [b]thru [b]toitem [b]of [b]ThisString)
017[b]else
018set arrItem to every text item of ThisString019end if020set the text item delimiters to OldDelims021if class of ThisItem is list then022return arrItem as text023else024if ThisItem is not equal to 0 then025return (item ThisItem of arrItem) as text026else027return arrItem -- return every items028end if029end if030end GetTextItemNotes: The line numbers included with this script are there to aid future discussions. In order to use this script, you will have to strip all of them.Now, it is just a matter of opening each Quark document in sequence and stepping through the Search_Items pairs.HTH

How to use an excel list to search and replace in Quark docs

Posted: 14 Jul 2009, 07:43
by JLMahurin
... Thank you very much for the script solution ... I try very hard to figure things out before bothering others .. I hope to "...learn to fish" ... so I will be able to help others at some point.Thank you for your time and expertise! One question what does arrItem represent?

How to use an excel list to search and replace in Quark docs

Posted: 14 Jul 2009, 09:02
by Scripting_Ace
Well, I have been using this handler for ages and it does much more that what I used it for in your sittuation.
The parameter ThisItem can also receive a list of two numbers ({x,y}) in which case, the handler will return items x thru y of the resulting list given the supplied delimiter.
in that context arritem stands for array (of) item(s) or list if you will.
HTH

How to use an excel list to search and replace in Quark docs

Posted: 14 Jul 2009, 12:27
by JLMahurin
Thank you for sharing your experience. I am very grateful.Best regards JLM

How to use an excel list to search and replace in Quark docs

Posted: 14 Jul 2009, 13:01
by Scripting_Ace
What my codes does is generate a list (SearchList) containing all your search and replace pairs:
{{Search_Item_1,Replace_Item_1}, {Search_Item_2,Replace_Item_2}, {Search_Item_3,Replace_Item_3},...}
So, for each Quark document you will need to do the following:

001tell application "QuarkXPress Passport"
002activate
003repeat with oneFile in theFiles
004open [b]oneFile
005[b]tell [b]document 1
006[b]repeat [b]with [b]ThisPair [b]in [b]SearchList
007[b]set {[b]searchstring, [b]replaceString} [b]to [b]ThisPair [b]as [b]list
008[b]tell [b]every [b]story
009[b]set ([b]every [b]text [b]where [b]it [b]is [b]searchstring) [b]to [b]replaceString
010[b]end [b]tell
011[b]end [b]repeat
012[b]end [b]tell
013[b]close [b]document 1 [b]saving [b]yes
014[b]end [b]repeat
015[b]end [b]tell
[b]Notes: The line numbers included with this script are there to aid future discussions. In order to use this script, you will have to strip all of them.
Note that I did not test this but the it should be very close.
HTH

How to use an excel list to search and replace in Quark docs

Posted: 21 Jul 2009, 15:16
by JLMahurin
I have been able to create a text file in the proper format. I have built a script that searches through text boxes separately, though entering the data manually, Your script generates the list, but I have been unable to join the search replace with the list yours generates and actually search through quark... I have tried stepping through -- I even changed mine to index and search through each page, text box and document sequentially --- reflect searching through each text box on each page and search (I can do this one at a time manually inserting the required data but it is no faster than doing it by hand) I can't seem to get the rows of information to propagate a search through the original list ... I don't seem to grasp the "how to" get one source file to work with the search replace functionality because each row has to be searched separately ... I am, frankly, not understanding how to step through each row of the text file and apply the source row data to the search replace ... I just do not seem to be able to do this on my own. If I may ask for more help on this? I am learning a great deal, but I am spending my good Karma points at blazing speeds this week ... I feel like a baby trying to walk and just can't get up off the floor to my feet... JLM

How to use an excel list to search and replace in Quark docs

Posted: 21 Jul 2009, 17:30
by Scripting_Ace
Try this:
001-- Quark docs
002set QFolder to "Please locate the folder containing your Quark documents..."
003set Doc_Folder to (choose folder with prompt QFolder) as [b]text

004[b]set [b]theFiles [b]to {}
005[b]tell [b]application "Finder"
006[b]tell [b]folder [b]Doc_Folder
007[b]repeat [b]with [b]i [b]from 1 [b]to [b]count [b]of [b]files
008[b]if [b]file type [b]of [b]file [b]i [b]is "xprj" [b]then
009[b]set [b]end [b]of [b]theFiles [b]to [b]file [b]i [b]as [b]text
010[b]end [b]if
011[b]end [b]repeat
012[b]end [b]tell
013[b]end [b]tell

014-- Search Items
015[b]set [b]QFile [b]to "Please locate your Tab Delimited Text file..."
016[b]set [b]Search_File [b]to ([b]choose file [b]with prompt [b]QFile) [b]as [b]text

017[b]set [b]Search_Rows [b]to [b]read [b]file [b]Search_File [b]using delimiter {[b]return}
018[b]set [b]Search_Items [b]to {}
019[b]repeat [b]with [b]This_Row [b]in [b]Search_Rows
020[b]set [b]end [b]of [b]Search_Items [b]to [b]my [b]GetTextItem([b]This_Row [b]as [b]text, [b]tab, 0)
021[b]end [b]repeat

022--Process
023[b]tell [b]application/font> "QuarkXPress Passport"024activate025repeat with oneFile in theFiles026open file oneFile027tell document 1028repeat with ThisPair in Search_Items029set {searchstring, replaceString} to ThisPair as list030tell every story031try032set (every text where it is searchstring) to replaceString033end try034end tell035end repeat036end tell037close document 1 saving yes038end repeat039end tell040on GetTextItem(ThisString, ThisDelim, ThisItem)041-- ThisString -> String to look in042-- ThisDelim -> Text element that delimit the string043-- ThisItem -> Number of the element to return (0 for all)044copy the text item delimiters to OldDelims045set the text item delimiters to ThisDelim046if class of ThisItem is list then047set fromItem to (item 1 of ThisItem) as integer048set toitem to (item 2 of ThisItem) as integer049set arrItem to (text items fromItem thru toitem of ThisString)050else051set arrItem to every text item of ThisString052end if053set the text item delimiters to OldDelims054if class of ThisItem is list then055return arrItem as text056else057if ThisItem is not equal to 0 then058return (item ThisItem of arrItem) as text059else060return arrItem -- return every items061end if062end if063end GetTextItemNotes: The line numbers included with this script are there to aid future discussions. In order to use this script, you will have to strip all of them.HTH