I’ve been spending the past few days scouring forums trying to figure out how to do this, and I’m not getting anywhere, so I figured I’d post a new topic. I know almost nothing about Applescript, but I need to use it as it’s the only way I can access both Excel and InDesign.
Here’s my situation: I have an Excel spreadsheet that has item descriptions, quantities, and prices. The descriptions are text, the quantities are numbers, and the prices are currencies. I’ll be getting these documents from all over the world, so the price column is in true currency format with various currency symbols.
I was able to find an Applescript that converts the full sheet into a text array for InDesign to read, but the price cells only give me the value, not the formatted value.
So when my row looks like this:
[format]Item A | 4 | 3.00 €[/format]
and I run this script:
tell application "Microsoft Excel"
open file "excelFilePath"
set theWorkbook to active workbook
set theSheet to sheet 1 of theWorkbook
set theMatrix to value of used range of theSheet
set theRowCount to count theMatrix
set str to ""
set oldDelimiters to AppleScript's text item delimiters
repeat with countRows from 1 to theRowCount
set theRow to item countRows of theMatrix
set AppleScript's text item delimiters to ";"
set str to str & (theRow as string) & "|"
end repeat
set AppleScript's text item delimiters to oldDelimiters
close theWorkbook saving no
end tell
tell application id "com.adobe.InDesign"
tell script args
set value name "excelData" value str
end tell
end tell
then I get this:
[format]“Item A,4,3”[/format]
but I need this:
[format]“Item A,4,3.00 €”[/format]
and now I’m getting an error that
[format]Error string: Can’t make 《class XRgt》 of {“Description”,“Quantity”,“Price”} into type Unicode text[/format]
When I tried changing
repeat with countRows from 1 to theRowCount
to
repeat with countRows from 2 to theRowCount
I got the same error, but with my cell values:
[format]Error string: Can’t make 《class XRgt》 of {“Item A”,“4”,“3”} into type Unicode text[/format]
I also noticed that the “3.00 €” is still displaying as “3” in the error, but I don’t know if that’s relevant.
Is there a way to test if the cell is formatted as currency and only convert those values to strings?
I’m also wondering if it may be easier to have the script convert all cells to text strings before trying to convert it into an array, but I have no idea how to do that.
Your xrgt error is likely related with attempting to express something range-related as text.
But I think where your problem really lies is that you aren’t getting the currency symbol out of ‘thematrix’.
Assuming that you have ‘Item A | 4 | 3.00 €’ in cell “A1”, try something like this:
tell application "Microsoft Excel"
tell workbook 1
tell active sheet
set xy to string value of range "A1"
--> "Item A | 4 | 3.00 €"
set AppleScript's text item delimiters to " | "
set tixy to text items of xy
--> {"Item A", "4", "3.00 €"}
set AppleScript's text item delimiters to ","
tixy as text
--> "Item A,4,3.00 €"
end tell
end tell
end tell
In case I misunderstood the actual question, here is a way to get the currency format included when column 4 of each row contains a currency amount. Also, a command to get the number format for a cell. Basically, what this does is create a list of the values of each row’s three cells and then re-form them as a string with comma-delimited fields and pipe-delimited records.
This assumes that the items below represent range B2:D3.
tell application "Microsoft Excel"
tell workbook 1
tell active sheet
number format of range "D2"
--> "#,##0.00 [$€-1];[Red]-#,##0.00 [$€-1]"
set str to ""
repeat with r from 2 to 3 -- rows to process
set str0 to {} -- reset for each row
set AppleScript's text item delimiters to ","
set str0 to str0 & value of cell 2 of row r & value of cell 3 of row r & string value of cell 4 of row r
--> {"Item A ", 4.0, "3.00 €"} -- first time through
--> {"Item B ", 18.0, "$4.25"} -- second time through
set str to str & str0 & "|" as text
--> "Item A ,4.0,3.00 €|" -- first time through
end repeat
--> "Item A ,4.0,3.00 €|Item B ,18.0,$4.25|"
end tell
end tell
end tell
Thanks for the reply @Mockman, I was able to get it working by taking this part of your code:
set str to ""
repeat with r from 2 to 3 -- rows to process
set str0 to {} -- reset for each row
set AppleScript's text item delimiters to ","
set str0 to str0 & value of cell 2 of row r & value of cell 3 of row r & string value of cell 4 of row r
--> {"Item A ", 4.0, "3.00 €"} -- first time through
--> {"Item B ", 18.0, "$4.25"} -- second time through
set str to str & str0 & "|" as text
--> "Item A ,4.0,3.00 €|" -- first time through
end repeat
and adding it to mine to get this:
tell application "Microsoft Excel"
open file "excelFilePath"
set theWorkbook to active workbook
set theSheet to sheet 1 of theWorkbook
set theMatrix to value of used range of theSheet
set theRowCount to count theMatrix
set str to ""
set oldDelimiters to AppleScript's text item delimiters
repeat with countRows from 1 to theRowCount
set str0 to {}
set AppleScript's text item delimiters to ";"
set str0 to str0 & value of cell 2 of row countRows & value of cell 3 of row countRows & string value of cell 4 of row countRows
set str to str & str0 & "|"
end repeat
set AppleScript's text item delimiters to oldDelimiters
close theWorkbook saving no
end tell
tell application id "com.adobe.InDesign"
tell script args
set value name "excelData" value str
end tell
end tell
This give me the values I need, so thank you so much!
Follow up question:
Is there a way of doing this without specifying each cell in the row?
I used only 3 columns in my example, but my actual spreadsheet has about 10 columns. I don’t mind having a long line of code in the script, but for future maintenance it would be nice to have it pick out the currency cell dynamically instead of having to specify the exact column that I need to convert to the string value.
Glad that it answered at least part of your problem. As to the currency item… there are probably a few approaches you could take.
But first, if you have many columns, you could put another repeat loop there to handle the columns. That way, you wouldn’t require a long line of code.
As to isolating the currency column for each row… well, it depends on what is in all the cells.
An approach might be to cycle through each cell and compare some of its properties. For example, try testing whether the formula matches the string value. That seems like a pairing of properties that should be fairly reliable.
[format]get formula of range “C2”
→ “4”
get string value of range “C2”
→ “4”
get formula of range “D2”
→ “3”
get string value of range “D2”
→ “3.00 €”
set notCurrency to formula of range “C2” is string value of range “C2”
→ true
set notCurrency to formula of range “D2” is string value of range “D2”
→ false[/format]
Then, if a cell produces a false here, you would get its string value. Otherwise you would just get its value.
Of course, maybe you could just get string value for every cell. Dunno if that would create other issues though (e.g. with real numbers). This would work for the entire range:
[format]set xxy to string value of range “B2:D2”
item 1 of xxy
→ {"Item A ", “4”, “3.00 €”}
[/format]