Change text in cells in a specific column of a table in QuarkXpress

Hi everyone,

I have a script in which I can find price tags in QuarkXPress in text boxes and change it via Applescript (and Applescript toolbox for the regex).

I get this with the big help of 2 very kind members of this community “DJ Bazzie Wazzie” & “Yvan Koenig”. Thanks again for it :slight_smile:

I wanted to extend the script with the ability to find price tags in tables in specific columns to change it via regex, but i get stucked wit it. Try everything I could to get this specific column. I managed to count the tables in the document and the columns of each table.

Here is the Script so far (working with text boxes!):


set theFolder to (choose folder) as string
set theFiles to paragraphs of (do shell script "ls " & quoted form of POSIX path of theFolder)
repeat with i from 1 to count theFiles
	repeat 1 times --to simulate continue
		set theFile to theFolder & item i of theFiles as string
		if theFile does not end with ".qxd" then
			exit repeat -- simulate a continue command
		end if
		openFile(theFile)
		-- if number of document = 0 the file isn't opened
		if nrOfOpenDocuments() is 0 then
			exit repeat -- simulate a continue command
		end if
		processOpenDocument()
		closeAndSaveDocument(theFile)
	end repeat
end repeat

on processOpenDocument()
	repeat with storyIndex from 1 to countStories()
		set theStory to getStoryContents(storyIndex)
		set priceOffsets to AST find regex "[0-9X]{1,3},[0-9X]{2}" in string theStory with returning offsets
		if priceOffsets is not missing value then
			-- reverse the list, edit from end to front so the offsets stays correct
			set priceOffsets to reverse of priceOffsets
			repeat with priceRange in priceOffsets
				set foundString to getRangeOfStory(storyIndex, rm_so of priceRange, rm_eo of priceRange)
				setRangeOfStory(storyIndex, rm_so of priceRange, rm_eo of priceRange, "000,00 €")
			end repeat
		end if
	end repeat
end processOpenDocument


-------------------------------------------------------------
-- QXP HANDLERS
-------------------------------------------------------------

on nrOfOpenDocuments()
	tell application "QuarkXPress" to return count of documents
end nrOfOpenDocuments

on openFile(thePath)
	tell application "QuarkXPress" to open alias thePath with Suppress All Warnings
end openFile

on closeAndSaveDocument(thePath)
	tell application "QuarkXPress" to close document 1 with saving
end closeAndSaveDocument

on countStories()
	tell application "QuarkXPress"
		tell document 1 to return count of stories
	end tell
end countStories

on getStoryContents(i)
	tell application "QuarkXPress"
		tell document 1 to return contents of story i
	end tell
end getStoryContents

on setRangeOfStory(i, s, e, t) -- index, start range, end range, text
	tell application "QuarkXPress"
		tell document 1 to set text of characters s thru e of story i to t
	end tell
end setRangeOfStory

on getRangeOfStory(i, s, e) -- index, start range, end range
	tell application "QuarkXPress"
		tell document 1 to return characters s thru e of story i
	end tell
end getRangeOfStory

-------------------------------------------------------------
-- MISCELLANEOUS HANDLERS
-------------------------------------------------------------
on getPathsFromFolder()
	set rawData to do shell script "ls " & quoted form of POSIX path of inPath
	set theFileNames to AST find regex "([^" & return & "]+)($|" & return & ")" in string rawData regex group 2
	
	repeat with x from 1 to count theFileNames
		set item x of theFileNames to inPath & item x of theFileNames
	end repeat
	return theFileNames
end getPathsFromFolder

Hopefully somebody can help to change texts in a specific column (with always the same header!).

Here is a screenshot of a table:

The count of columns can vary between 10 to 13. The column with the “Preis” header is not always on the same position.

Thanks in advance for any help.

Model: Mac Pro (Early 2009)
AppleScript: 2.2.1
Browser: Safari 537.36
Operating System: Mac OS X (10.7)

Does anybody got an idea how I can solve it?

Need to change prices in cells in a table. The column with the prices have always “Preis” in the one of the rows (mostly the second row!) but differs in position. It could be column on position 11 or 10 or 13…

I have no idea how I can get those prices in the cells with regex and change it!

Any help is really appreciated!

Thanks on advance.

Hi there,

Had a quick look at this, in particular what table properties I could get.
For testing purposes I created a table with 3 rows and 5 columns. In cell 4 on row 1 I inserted “Preis”.

I came up with this:

tell application "QuarkXPress"
	
	set theSelectedTable to selection
	
	set rowCount to count of table rows of theSelectedTable
	set colCount to count of table columns of theSelectedTable
	
	set theTableCells to every text cell of theSelectedTable
	
	repeat with i from 1 to colCount
		
		if story 1 of item i of theTableCells = "Preis" then
			set theColumnWithPreisIn to i
		end if
		
	end repeat
	
end tell

I selected the table, with the item tool, and executed the code.
The variable theColumnWithPreisIn returned the number 4, the column that would contain the prices.
With that you could, in theory, get the values for the other cells in that column and do the find/replace.

HTH

@TecNik: Thanks for your help. I get a little further with it.

I must correct my specification for the table. The “Preis” row is not the first one, but mostly the second (but it can differ!).
The right column is the one with the “Preis” in it!

Here is the Script so far (at the moment I try it in a separate script, but finally I want to add it to my script of my initial post):


tell application "QuarkXPress"
	set tbCount to count of every table box of document 1
	
	repeat with t from 1 to tbCount
		set rowCount to count of table rows of table box t of document 1
		set colCount to count of table columns of table box t of document 1
		
		set theTableCells to every text cell of table box t of document 1
		repeat with i from 1 to colCount
			if story 1 of item i of theTableCells = "Preis" then
				set theColumnWithPreisIn to i
			end if
		end repeat
	end repeat
end tell

With this Part I have a problem. I try to make it more general, so it searches also in row 2 or 3 etc., but with no luck.


repeat with i from 1 to colCount
    if story 1 of item i of theTableCells = "Preis" then
	 set theColumnWithPreisIn to i
    end if
end repeat

Thanks in advance for any help.

This searches every cell and returns the column that ‘Preis’ exists in.


tell application "QuarkXPress"
	
	set theSelectedTable to selection
	
	set rowCount to count of table rows of theSelectedTable
	set colCount to count of table columns of theSelectedTable
	
	set theTableCells to every text cell of theSelectedTable
	
	set theTableCellCount to count of theTableCells
	
	repeat with i from 1 to theTableCellCount
		if story 1 of item i of theTableCells = "Preis" then
			set theColumnWithPreisIn to i mod colCount
			if theColumnWithPreisIn = 0 then set theColumnWithPreisIn to colCount
		end if
	end repeat
	
end tell

It assumes that there is only 1 column with ‘Preis’ in it.
I’ve not looked into whether this could be done with some sort of find/search.

This is a little more simplified and seems to work.
It cuts out looping through all the cells in the table.


tell application "QuarkXPress"
	
	set theSelectedTable to selection
	
	set colCount to count of table columns of theSelectedTable
	
	set thisList to every text cell of theSelectedTable where story 1 = "Preis"
	
	set theCellIndex to index of item 1 of thisList
	
	set theColumnWithPreisIn to theCellIndex mod colCount
	
	if theColumnWithPreisIn = 0 then set theColumnWithPreisIn to colCount

end tell

HTH

@TecNik: Thanks again for your help!

I will test it, but I can give feedback at Monday at the earliest.

I hope I could make it clearly enough.

I have a few QuarkXpress documents with a lot of tables in each of them.

I have to go through every table in these documents and find the one an only column in each table which contains “Preis” in one of the rows of this column. In this column there is a price tag like “19,99 €” which i have to find an replace.

So you are right. One table have only one column with “Preis”. But each table in each Quark document have this column!

Sorry if I confused you in some way.

I really, really appreciate your help!

Thanks in advance for any help.

Hi there,

The code below loops through the tables in a Quark doc and displays which columns the word ‘Preis’ exists in.


tell application "QuarkXPress"
	
	set theTablesInDoc to every table box of document 1
	display dialog "Table Count: " & (count of theTablesInDoc)
	
	set theTablesInDoc to reverse of theTablesInDoc
	
	
	repeat with theSelectedTable in theTablesInDoc
		
		set colCount to count of table columns of theSelectedTable
		
		set thisList to (every text cell of theSelectedTable where story 1 = "Preis")
		
		set theCellIndex to index of item 1 of thisList
		
		set theColumnWithPreisIn to theCellIndex mod colCount
		
		if theColumnWithPreisIn = 0 then set theColumnWithPreisIn to colCount
		
		display dialog "Preis found in column: " & theColumnWithPreisIn
		
	end repeat
	
	
end tell

You should be able to tie this up, with some of the code you’ve listed earlier in the post, to do the replacing of the copy as required.

HTH

Sorry for the late response on this TecNik. I have been ill for the past few days, that’s why I haven’t been able to reply until now.

This works almost perfectly. I have to catch the case that a table contains no column with “Preis” in it at all. Only just for the case.

Now the script looks like this:


set theFolder to (choose folder) as string
set theFiles to paragraphs of (do shell script "ls " & quoted form of POSIX path of theFolder)
repeat with i from 1 to count theFiles
	repeat 1 times --to simulate continue
		set theFile to theFolder & item i of theFiles as string
		if theFile does not end with ".qxd" then
			exit repeat -- simulate a continue command
		end if
		with timeout of 300 seconds
			openFile(theFile)
		end timeout
		-- if number of document = 0 the file isn't opened
		if nrOfOpenDocuments() is 0 then
			exit repeat -- simulate a continue command
		end if
		-- processOpenDocument()
		processTblColumnDocument()
		processColoredText()
		closeAndSaveDocument(theFile)
	end repeat
end repeat


on processTblColumnDocument()
	tell application "QuarkXPress"
		set theTablesInDoc to every table box of document 1
		display dialog "Table Count: " & (count of theTablesInDoc)
		
		set theTablesInDoc to reverse of theTablesInDoc
		
		
		repeat with theSelectedTable in theTablesInDoc
			
			set colCount to count of table columns of theSelectedTable
			set thisList to (every text cell of theSelectedTable where story 1 = "Preis")
			if thisList is not {} then
				set theCellIndex to index of item 1 of thisList
				set theColumnWithPreisIn to theCellIndex mod colCount
				
				if theColumnWithPreisIn = 0 then set theColumnWithPreisIn to colCount
				
				display dialog "Preis found in column: " & theColumnWithPreisIn
			end if
		end repeat
	end tell
end processTblColumnDocument

on processOpenDocument()
	repeat with storyIndex from 1 to countStories()
		set theStory to getStoryContents(storyIndex)
		set offString to AST find regex "[0-9]{0,1}[.]{0,1}[0-9X]{1,3}[,]{1}[0-9X]{2}[ ]{1}€" in string theStory
		set priceOffsets to AST find regex "[0-9]{0,1}[.]{0,1}[0-9X]{1,3}[,]{1}[0-9X]{2}[ ]{1}€" in string theStory with returning offsets
		if offString contains "€" then
			set rm_eo to rm_eo - 1
		end if
		if priceOffsets is not missing value then
			-- reverse the list, edit from end to front so the offsets stays correct
			set priceOffsets to reverse of priceOffsets
			repeat with priceRange in priceOffsets
				set foundString to getRangeOfStory(storyIndex, rm_so of priceRange, rm_eo of priceRange)
				try
					setRangeOfStory(storyIndex, rm_so of priceRange, rm_eo of priceRange, "xx,xx €")
				end try
			end repeat
		end if
	end repeat
end processOpenDocument

on processColoredText()
	tell application "QuarkXPress"
		tell document 1
			if not (exists (color spec "preisgruen")) then
				make color spec at end with properties {name:"preisgruen", CMYK color value:{45875, 0, 65535, 0}, separation:true}
				-- 0% = 0 | 100% = 65535
			end if
			do updates
			set cstyles to name of every character spec
			if "7 TEXT 4 PREIS" is in cstyles then
				set color of character spec "7 TEXT 4 PREIS" to "preisgruen"
			end if
			if "5,5 Text 4 PREIS " is in cstyles then
				set color of character spec "5,5 Text 4 PREIS " to "preisgruen"
			end if
		end tell
	end tell
end processColoredText
-------------------------------------------------------------
-- QXP HANDLERS
-------------------------------------------------------------

on nrOfOpenDocuments()
	tell application "QuarkXPress" to return count of documents
end nrOfOpenDocuments

on openFile(thePath)
	tell application "QuarkXPress" to open alias thePath with Suppress All Warnings
end openFile

on closeAndSaveDocument(thePath)
	tell application "QuarkXPress" to close document 1 with saving
end closeAndSaveDocument

on countStories()
	tell application "QuarkXPress"
		tell document 1 to return count of stories
	end tell
end countStories

on getStoryContents(i)
	tell application "QuarkXPress"
		tell document 1 to return contents of story i
	end tell
end getStoryContents

on setRangeOfStory(i, s, e, t) -- index, start range, end range, text
	tell application "QuarkXPress"
		tell document 1 to set text of characters s thru e of story i to t
		-- tell document 1 to set characters s thru e of story i to t
	end tell
end setRangeOfStory

on getRangeOfStory(i, s, e) -- index, start range, end range
	tell application "QuarkXPress"
		try
			tell document 1 to return characters s thru e of story i
		end try
	end tell
end getRangeOfStory

-------------------------------------------------------------
-- MISCELLANEOUS HANDLERS
-------------------------------------------------------------
on getPathsFromFolder()
	set rawData to do shell script "ls " & quoted form of POSIX path of inPath
	set theFileNames to AST find regex "([^" & return & "]+)($|" & return & ")" in string rawData regex group 2
	
	repeat with x from 1 to count theFileNames
		set item x of theFileNames to inPath & item x of theFileNames
	end repeat
	return theFileNames
end getPathsFromFolder

I am not able at the moment to combine the search w/ regex (see below) for the real price tag in the right column (the column with the “Preis” in it!). My problem is how to search the rows of this column with the regex to find and replace the price tag.


repeat with storyIndex from 1 to countStories()
		set theStory to getStoryContents(storyIndex)
		set offString to AST find regex "[0-9]{0,1}[.]{0,1}[0-9X]{1,3}[,]{1}[0-9X]{2}[ ]{1}€" in string theStory
		set priceOffsets to AST find regex "[0-9]{0,1}[.]{0,1}[0-9X]{1,3}[,]{1}[0-9X]{2}[ ]{1}€" in string theStory with returning offsets
		if offString contains "€" then
			set rm_eo to rm_eo - 1
		end if
		if priceOffsets is not missing value then
			-- reverse the list, edit from end to front so the offsets stays correct
			set priceOffsets to reverse of priceOffsets
			repeat with priceRange in priceOffsets
				set foundString to getRangeOfStory(storyIndex, rm_so of priceRange, rm_eo of priceRange)
				try
					setRangeOfStory(storyIndex, rm_so of priceRange, rm_eo of priceRange, "xx,xx €")
				end try
			end repeat
		end if
	end repeat

Big thanks to TecNik for his help. I really, really appreciate it. :slight_smile:

I am thankful for any help!

I tried this but with no result in the offset. :frowning:

Here is the script snippet:


set thisListText to (every text cell of theSelectedTable)
		repeat with t from 1 to count (thisListText)
			display dialog (t)
			set theStory to content of text cell t of theSelectedTable
			set offString to AST find regex "[0-9]{0,1}[.]{0,1}[0-9X]{1,3}[,]{1}[0-9X]{2}[ ]{1}€" in string theStory
			set priceOffsets to AST find regex "[0-9]{0,1}[.]{0,1}[0-9X]{1,3}[,]{1}[0-9X]{2}[ ]{1}€" in string theStory with returning offsets
			
		end repeat


Is there only one cell in the table that contains the Euro symbol?

If so, give this a try:


tell application "QuarkXPress"
	
	set theTablesInDoc to every table box of document 1
	
	set theTablesInDoc to reverse of theTablesInDoc
	
	repeat with theSelectedTable in theTablesInDoc
		
		set thisList to (every text cell of theSelectedTable where story 1 contains "€")
		
		set theCellIndex to index of item 1 of thisList
		
		set story 1 of text cell theCellIndex of theSelectedTable to "ThisPrice"
		
	end repeat
	
end tell

Unfortunately not! The tables can contain more Euro Signs and I need the offset of the numbers of the price because I have to “xx,xx €” them.

Here a few examples screens of the tables:

Hope you can help. I tried a few things which comes in my mind, but I get not the possibility to change the prices to xx,xx €.

Thanks a lot for your help so far TecNik. THANK YOU… I really appreciate it! :smiley:

Hi helpit,

Think I misunderstood you’re original post.
Does this do the table part ok?


tell application "QuarkXPress"
	
	set theTablesInDoc to every table box of document 1
	
	set theTablesInDoc to reverse of theTablesInDoc
	
	repeat with thisTable in theTablesInDoc
		
		set theEuroCellList to (every text cell of thisTable where story 1 contains "€" and story 1 contains ",")
		
		repeat with thisCell in theEuroCellList
			
			set story 1 of thisCell to "xx,xx €"
			
		end repeat
		
	end repeat
	
end tell

Thanks a lot… this does what it has to do with the first or 1 document :smiley: :smiley:

I paste it in to my script which works with the textfields, so the script could handle textfields and tables.

[b]But I got a huge problem!

If it comes to open the second document in the folder I get the error message (-609):

error “žQuarkXPress” hat einen Fehler erhalten: Die Verbindung ist ungültig." number -609
[/b]

The Script works without the table part without a problem, but as soon as I paste the table script in it, the error appears.

Any idea what is the cause of this error? Opening the document manually and start the script works. It also works to open the document via script and save it, but only with the first document. It doesn’t matter which document is the second one, QuarkXpress crashes. This happen only with the table script part.


set theFolder to (choose folder) as string
set theFiles to paragraphs of (do shell script "ls " & quoted form of POSIX path of theFolder)
repeat with i from 1 to count theFiles
	repeat 1 times --to simulate continue
		set theFile to theFolder & item i of theFiles as string
		if theFile does not end with ".qxd" then
			exit repeat -- simulate a continue command
		end if
		with timeout of 300 seconds
			openFile(theFile)
		end timeout
		-- if number of document = 0 the file isn't opened
		if nrOfOpenDocuments() is 0 then
			exit repeat -- simulate a continue command
		end if
		processOpenDocument()
		processTblsDocument()
		processColoredText()
		closeAndSaveDocument(theFile)
	end repeat
end repeat


on processTblsDocument()
	
	set theTablesInDoc to evryTbl()
	set theTablesInDoc to reverse of theTablesInDoc
	
	repeat with thisTable in theTablesInDoc
		set theEuroCellList to txtCellPrice(thisTable)
		repeat with thisCell in theEuroCellList
			changeTxtCell(thisCell)
		end repeat
	end repeat
	
end processTblsDocument

on processOpenDocument()
	repeat with storyIndex from 1 to countStories()
		set theStory to getStoryContents(storyIndex)
		set offString to AST find regex "[0-9]{0,1}[.]{0,1}[0-9X]{1,3}[,]{1}[0-9X]{2}[ ]{1}€" in string theStory
		set priceOffsets to AST find regex "[0-9]{0,1}[.]{0,1}[0-9X]{1,3}[,]{1}[0-9X]{2}[ ]{1}€" in string theStory with returning offsets
		if offString contains "€" then
			set rm_eo to rm_eo - 1
		end if
		if priceOffsets is not missing value then
			-- reverse the list, edit from end to front so the offsets stays correct
			set priceOffsets to reverse of priceOffsets
			repeat with priceRange in priceOffsets
				set foundString to getRangeOfStory(storyIndex, rm_so of priceRange, rm_eo of priceRange)
				try
					setRangeOfStory(storyIndex, rm_so of priceRange, rm_eo of priceRange, "xx,xx €")
				end try
			end repeat
		end if
	end repeat
end processOpenDocument

on processColoredText()
	tell application "QuarkXPress"
		tell document 1
			if not (exists (color spec "preisgruen")) then
				make color spec at end with properties {name:"preisgruen", CMYK color value:{45875, 0, 65535, 0}, separation:true}
				-- 0% = 0 | 100% = 65535
			end if
			do updates
			set cstyles to name of every character spec
			if "7 TEXT 4 PREIS" is in cstyles then
				set color of character spec "7 TEXT 4 PREIS" to "preisgruen"
			end if
			if "5,5 Text 4 PREIS " is in cstyles then
				set color of character spec "5,5 Text 4 PREIS " to "preisgruen"
			end if
			if "9 NAME 2 ZUSATZ" is in cstyles then
				set color of character spec "5,5 Text 4 PREIS " to "preisgruen"
			end if
		end tell
	end tell
end processColoredText
-------------------------------------------------------------
-- QXP HANDLERS
-------------------------------------------------------------

on nrOfOpenDocuments()
	tell application "QuarkXPress" to return count of documents
end nrOfOpenDocuments

on openFile(thePath)
	tell application "QuarkXPress" to open alias thePath with Suppress All Warnings
end openFile

on closeAndSaveDocument(thePath)
	tell application "QuarkXPress" to close document 1 with saving
end closeAndSaveDocument

on evryTbl()
	tell application "QuarkXPress"
		tell document 1 to return every table box
	end tell
end evryTbl

on txtCellPrice(thisTable_)
	tell application "QuarkXPress"
		tell document 1 to return (every text cell of thisTable_ where story 1 contains "€" and story 1 contains ",")
	end tell
end txtCellPrice

on changeTxtCell(thisCell_)
	tell application "QuarkXPress"
		tell document 1 to set story 1 of thisCell_ to "xx,xx €"
	end tell
end changeTxtCell

on countStories()
	tell application "QuarkXPress"
		tell document 1 to return count of stories
	end tell
end countStories

on getStoryContents(i)
	tell application "QuarkXPress"
		tell document 1 to return contents of story i
	end tell
end getStoryContents

on setRangeOfStory(i, s, e, t) -- index, start range, end range, text
	tell application "QuarkXPress"
		tell document 1 to set text of characters s thru e of story i to t
		-- tell document 1 to set characters s thru e of story i to t
	end tell
end setRangeOfStory

on getRangeOfStory(i, s, e) -- index, start range, end range
	tell application "QuarkXPress"
		try
			tell document 1 to return characters s thru e of story i
		end try
	end tell
end getRangeOfStory

-------------------------------------------------------------
-- MISCELLANEOUS HANDLERS
-------------------------------------------------------------
on getPathsFromFolder()
	set rawData to do shell script "ls " & quoted form of POSIX path of inPath
	set theFileNames to AST find regex "([^" & return & "]+)($|" & return & ")" in string rawData regex group 2
	
	repeat with x from 1 to count theFileNames
		set item x of theFileNames to inPath & item x of theFileNames
	end repeat
	return theFileNames
end getPathsFromFolder

Hi helpit,

Can you PM me with a link to a few sample files?

Thanks.