Wednesday, September 22, 2021

#1 2021-09-14 09:22:42 pm

ts_antares
Member
Registered: 2021-09-13
Posts: 3

Help with an Applescript to get string value of currency

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:

Item A | 4 | 3.00 €

and I run this script:

Applescript:

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:

"Item A,4,3"

but I need this:

"Item A,4,3.00 €"

How do I get the string value of the currency?


Filed under: excel, Format, currency

Offline

 

#2 2021-09-15 02:47:54 am

Otto
Member
Registered: 2020-09-18
Posts: 5

Re: Help with an Applescript to get string value of currency

Instead of:

Applescript:

set str to str & (theRow as string) & "|"

try:

Applescript:

set str to str & (string value of theRow) & "|"

The property "string value “ should return what you need.

Offline

 

#3 2021-09-15 02:50:40 am

Fredrik71
Member
Registered: 2019-10-23
Posts: 880

Re: Help with an Applescript to get string value of currency

Hi... maybe this is what you are looking for. I'm from Sweden so the result of theNumber
on my Mojave will return a string --> 1 000,00 kr 

Applescript:

use framework "Foundation"
use scripting additions

set inputNumber to 1000

set formatter to current application's NSNumberFormatter's alloc()'s init()
formatter's setNumberStyle:(current application's NSNumberFormatterCurrencyStyle)
set theNumber to (formatter's stringFromNumber:inputNumber) as string


if you are the expert, who will you call if its not your imagination.

Offline

 

#4 2021-09-15 12:04:58 pm

ts_antares
Member
Registered: 2021-09-13
Posts: 3

Re: Help with an Applescript to get string value of currency

I tried changing to:

Applescript:

set str to str & (string value of theRow) & "|"

and now I'm getting an error that

Error string: Can't make 《class XRgt》 of {"Description","Quantity","Price"} into  type Unicode text

When I tried changing

Applescript:

repeat with countRows from 1 to theRowCount

to

Applescript:

repeat with countRows from 2 to theRowCount

I got the same error, but with my cell values:

Error string: Can't make 《class XRgt》 of {"Item A","4","3"} into  type Unicode text

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.

Last edited by ts_antares (2021-09-15 12:12:19 pm)

Offline

 

#5 2021-09-15 01:18:28 pm

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 105

Re: Help with an Applescript to get string value of currency

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:

Applescript:

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.

Item A     4    3.00 €
Item B     18    $4.25



Applescript:

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

Last edited by Mockman (2021-09-15 02:03:47 pm)


Filed under: excel, currency

Offline

 

#6 2021-09-15 04:01:37 pm

ts_antares
Member
Registered: 2021-09-13
Posts: 3

Re: Help with an Applescript to get string value of currency

Thanks for the reply @Mockman, I was able to get it working by taking this part of your code:

Applescript:

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:

Applescript:


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.


Filed under: excel, currency

Offline

 

#7 2021-09-15 06:19:15 pm

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 105

Re: Help with an Applescript to get string value of currency

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.

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

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:

set xxy to string value of range "B2:D2"
item 1 of xxy
--> {"Item A ", "4", "3.00 €"}

Filed under: excel, currency

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)