Re: Excel & AppleScripting
Re: Excel & AppleScripting
- Subject: Re: Excel & AppleScripting
- From: "Mark J. Reed" <email@hidden>
- Date: Mon, 1 Feb 2010 19:37:16 -0500
Do you have the Applescript Reference for Excel? You can download it
from Microsoft; the 2004 version is here:
http://download.microsoft.com/download/3/A/7/3A70FB4B-0C6A-43E3-AAB7-AC9166B25632/Excel2004AppleScriptRef.pdf
Looking it over, I don't see a "value" property of the "interior"
class. What if you just retrieve the value of the cell directly? Or
the formula?
On Mon, Feb 1, 2010 at 5:48 PM, Dan Black <email@hidden> wrote:
> Hi All!
>
> Hopefully not everyone on this list (is there anyone?) is on here just
> trolling for trouble-shooting, but unfortunately that’s the reason I’m
> joining. I like applescripting and have used it before a few times for some
> really simple stuff. But now I’ve got a task that is a bit more complex.
>
> I want to take an Excel cell’s value which is formatted as Currency and
> convert the number to plain-English. That is to say “$54.32” would be
> converted to “Fifty Four Dollars and Thirty Two Cents.” I have a VB Macro
> that was built in to the Excel file, which appears like it would work if it
> were on Windows.
>
> While working on translating the code from VB to AppleScript I’ve
> encountered a curious problem. I cannot seem to grab the value of any cell
> that has Currency formatting. For example if I had four cells, A1, A2, B1
> and B2 with respective values of [$100, null, 100, null] then the following
> script will (obviously) work:
>
> tell application "Microsoft Excel"
> tell active sheet
> set temp to value of cell "B1" as text
> set value of cell "B2" to temp
> end tell
> end tell
>
> Whereas the following will set the value of cell B2 to ““missing value””:
>
> tell application "Microsoft Excel"
> tell active sheet
> set temp to value of interior object of cell "A1"
> set value of cell "A2" to temp
> end tell
> end tell
>
> How can I change the format and/or data type (from currency to number or
> real) of the contents of a single cell so that I may get down to the real
> nitty-gritty of the script?
>
> Thank you so much to anyone who took the time to read this over and give it
> some thought!
>
> Dan Black
> Design Matters Inc!
>
>
> For reference, here is the vbs I’ll be translating once I can figure out the
> currency stuff:
>
> Option Explicit
>
> '****************' Main Function *'****************
> Function SpellNumber(ByVal MyNumber)
> Dim Dollars, Cents, Temp
> Dim DecimalPlace, Count
> ReDim Place(9) As String
> Place(2) = " Thousand "
> Place(3) = " Million "
> Place(4) = " Billion "
> Place(5) = " Trillion " ' String representation of amount
> MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none
> DecimalPlace = InStr(MyNumber, ".")
> 'Convert cents and set MyNumber to dollar amount
> If DecimalPlace > 0 Then
> Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
> MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
> End If
> Count = 1
> Do While MyNumber <> ""
> Temp = GetHundreds(Right(MyNumber, 3))
> If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
> If Len(MyNumber) > 3 Then
> MyNumber = Left(MyNumber, Len(MyNumber) - 3)
> Else
> MyNumber = ""
> End If
> Count = Count + 1
> Loop
> Select Case Dollars
> Case ""
> Dollars = "No Dollars"
> Case "One"
> Dollars = "One Dollar"
> Case Else
> Dollars = Dollars & " Dollars"
> End Select
> Select Case Cents
> Case ""
> Cents = " and No Cents"
> Case "One"
> Cents = " and One Cent"
> Case Else
> Cents = " and " & Cents & " Cents"
> End Select
> SpellNumber = Dollars & Cents
> End Function
> '*******************************************
> ' Converts a number from 100-999 into text *
> '*******************************************
> Function GetHundreds(ByVal MyNumber)
> Dim Result As String
> If Val(MyNumber) = 0 Then Exit Function
> MyNumber = Right("000" & MyNumber, 3) 'Convert the hundreds place
> If Mid(MyNumber, 1, 1) <> "0" Then
> Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
> End If
> 'Convert the tens and ones place
> If Mid(MyNumber, 2, 1) <> "0" Then
> Result = Result & GetTens(Mid(MyNumber, 2))
> Else
> Result = Result & GetDigit(Mid(MyNumber, 3))
> End If
> GetHundreds = Result
> End Function
> '*********************************************
> ' Converts a number from 10 to 99 into text. *
> '*********************************************
> Function GetTens(TensText)
> Dim Result As String
> Result = "" 'null out the temporary function value
> If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19
> Select Case Val(TensText)
> Case 10: Result = "Ten"
> Case 11: Result = "Eleven"
> Case 12: Result = "Twelve"
> Case 13: Result = "Thirteen"
> Case 14: Result = "Fourteen"
> Case 15: Result = "Fifteen"
> Case 16: Result = "Sixteen"
> Case 17: Result = "Seventeen"
> Case 18: Result = "Eighteen"
> Case 19: Result = "Nineteen"
> Case Else
> End Select
> Else ' If value between 20-99
> Select Case Val(Left(TensText, 1))
> Case 2: Result = "Twenty "
> Case 3: Result = "Thirty "
> Case 4: Result = "Forty "
> Case 5: Result = "Fifty "
> Case 6: Result = "Sixty "
> Case 7: Result = "Seventy "
> Case 8: Result = "Eighty "
> Case 9: Result = "Ninety "
> Case Else
> End Select
> Result = Result & GetDigit _
> (Right(TensText, 1)) 'Retrieve ones place
> End If
> GetTens = Result
> End Function
> '*******************************************
> ' Converts a number from 1 to 9 into text. *
> '*******************************************
> Function GetDigit(Digit)
> Select Case Val(Digit)
> Case 1: GetDigit = "One"
> Case 2: GetDigit = "Two"
> Case 3: GetDigit = "Three"
> Case 4: GetDigit = "Four"
> Case 5: GetDigit = "Five"
> Case 6: GetDigit = "Six"
> Case 7: GetDigit = "Seven"
> Case 8: GetDigit = "Eight"
> Case 9: GetDigit = "Nine"
> Case Else: GetDigit = ""
> End Select
> End Function
>
>
>
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> AppleScript-Users mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
> Archives: http://lists.apple.com/archives/applescript-users
>
> This email sent to email@hidden
>
--
Mark J. Reed <email@hidden>
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden