3

Be warned, I am a very new to AppleScript and most programming in general. The basic purpose of the script I've been working on is to read information from Excel files and put it into a FileMaker Pro database. Everything is working well so far, except when attempting to get the value of cells containing dates. Since the dates in the Excel file are already formatted correctly (mm/dd/yy) for the database they are to be put in, I would like to simply read and write them as strings.

The problem arises here, where AppleScript outputs the values as dates in the format (day of the week, month dd, yyyy at 12:00:00 AM) despite my attempt to read them as strings. Below is a shortened version of the script that shows where the problem is occurring.

    tell application "Finder"
    activate application "Microsoft Excel"
    open the first file in folder "Macintosh HD⁩:⁨Users:⁨theUser⁩:Documents⁩:⁨attachments test"
    tell application "Microsoft Excel"
        tell active sheet
            tell used range
                set rowCount to count of rows

                repeat with i from 2 to rowCount

                    set colA to "A" & i & ":A" & i
                    set theDate to get value of range colA as string

                    set rowB to "B" & i & ":B" & i
                    set theTime to get value of range rowB as real

                    set colC to "C" & i & ":C" & i
                    set serialNo to get value of range colC as integer

                    set colD to "D" & i & ":D" & i
                    set pH to get value of range colD as real

                    set colE to "E" & i & ":E" & i
                    set theVolume to get value of range colE as real


                    display dialog "The serial number of row " & i & " is: " & serialNo & " and the pH value is: " & pH & " and the volume is: " & theVolume & " and the date is: " & theDate & " and the time is: " & theTime



                end repeat

            end tell
        end tell
    end tell
end tell

I also tried another method to deal with the problem by setting a variable each for day, month, and year, but ran into another issue where month is not recognized as a purple word (whatever that should be called) and is instead blue (again, I cannot find what these terms are called anywhere). In the image below are three different ways I attempted to get these values from the date in format (day of the week, month dd, yyyy at 12:00:00 AM) so that they could then be reorganized manually.

blue is a good color, but I wanted purple My only guess for the initial problem is that 'as string' does not work when placed after the other parts of the line, but I have no clue why month is refusing to cooperate. Any guidance would be greatly appreciated!

  • 1
    So just a couple of comments about your script. You don't need to wrap everything in that "tell Finder" block. You should start the script with "Tell Application "Microsoft Excel" then on the next line "Activate" and then the rest of your code. Your code to get the data from the cell can be simplified down to "set theDate to value of cell (("$A$" & i) as string)" – Chris Norman Sep 19 at 22:18
  • I appreciate the response, @ChrisNorman but I am not sure that removing the tell finder block is a good idea. It gives a syntax error when it gets to the 3rd line: open first file in folder "Macintosh HD:Users:........" Expected end of line, etc. but found “"”. – Glab Sep 19 at 22:30
  • Ok, so simplify the s script so the finder tell block looks like this: "tell application "Finder" open the first file in folder "Macintosh HD⁩:⁨Users:⁨theUser⁩:Documents⁩:⁨attachments test" end tell " – Chris Norman Sep 19 at 22:44
  • @ChrisNorman is right: you shouldn't nest tell application blocks inside each other, and in this case, the Finder tell block is unnecessary. open the first file in folder "blah" is, indeed, Finder-specific. But Excel has it's own open workbook command, and you should read its terminology dictionary to find out how to tell Excel to open one of its own files (here's a guide that pertains to Excel 2004, but might still have relevance). – CJK Sep 20 at 5:14
  • Regarding the actual issue, you seem to imply that the value being stored in the variable theDate in your script above is not a text (or string) value; however, your illustrated attempts below that all involve you applying the date object specifier to theDate (i.e. date theDate), which can only be done if theDate is a string. Therefore, something is amiss here. Can you be explicitly clear in what exactly gets stored in the variable theDate ? I suggest adding immediately before display dialog the line return theDate, and copying exactly what gets returned so we can see. – CJK Sep 20 at 5:24
1

This may not be a direct solution or quite the answer you are looking for, but this following AppleScript code provide examples of how to convert a given short date string into an actual date, how to extract the elements of the date, edit and set date and time values, and to create your own custom date and time as a string. Hope this helps.

set dateAsText to "9/23/16"

-- converts dateAsText into an actual date
set someDate to date dateAsText -- date "Friday, September 23, 2016 at 12:00 AM"

-- extract the short date from someDate... as a string
set shortDate to short date string of someDate -- "09/23/16"

-- change the time value of someDate from "12:00 AM" to "5:00 PM"
set adjustedDate to date "5:00 PM" of someDate -- date "Friday, September 23, 2016 at 5:00 PM"


-- extract elements from adjustedDate... as strings
set theTime to time string of adjustedDate -- "5:00 PM"

set theWeekday to weekday of adjustedDate -- Friday
set theWeekday to (weekday of adjustedDate) as string -- "Friday"

set theDay to day of adjustedDate -- 23
set theDay to (day of adjustedDate) as string -- "23"

set theMonth to month of adjustedDate -- September
set theMonth to (month of adjustedDate) as string -- "September"

-- edit values of adjustedDate
set day of adjustedDate to 14 -- date "Wednesday, September 14, 2016 at 5:00 PM"
set year of adjustedDate to 1978 -- date "Thursday, September 14, 1978 at 5:00 PM"

------------
set customDate to short date string of adjustedDate & space & theTime -- "09/14/78 5:00 PM"
  • Of course, this will fail on every system that has a different format for dates and times specified in System Preferences. – CJK Sep 20 at 5:09
1

It seems string value (not value) is the term you're looking for. Excel stores cells in its raw format (e.g., date, number, etc.) but then has the separate ability to format those values. So, for example, you could store the number 2.33423 but display it as 2.3 showing just one decimal.

In Applescript, this is the difference between the term value (which is the raw value the way Excel stored it) and string value which is the way Excel displays it.

tell application "Microsoft Excel"
    get value of active cell
    -- date "Sunday, May 19, 2019 at 12:00:00 AM"

    get string value of active cell
    -- "5/19/19"
end tell

Now, provided you have already set the target formatting on the cell, you can set the cell contents simply, by something like this:

tell application "Microsoft Excel"
    set value of active cell to "6/18/2019"
end tell

Provided this cell is formatted as a date, it will interpreted as such.

Getting back to the original issue at hand, you can review AppleScript's class definitions for the class date -- scroll down until you find the entire description. The reason that month appears differently, is that it is a constant not an integer. Date is a class type inside AppleScript, which has associated properties you can get or manipulate.

For example: One could get the month as an integer in the following way (otherwise, it will be a constant).

tell application "Microsoft Excel"
    set theDate to value of active cell as date
    get (month of theDate) as integer
end tell

Additional references:

  • Excellent discussion on AppleScript time and date formatting.
  • Additional sample code on setting the date on an Excel cell. I'm not sure this is needed, but it shows you how you can do it.
0

Take the date that Excel is currently giving you and coerce it back into the format you need to put into Filemaker like this:

    tell theDate to set dateList to {its month as integer as text, day as text, year as text}
set {saveTID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, {"/"}}
set newDate to dateList as string
set AppleScript's text item delimiters to saveTID
  • That looks like some pretty nice code, but unfortunately it gives this error: Can’t make month of "Tuesday, September 17, 2019 at 12:00:00 AM" into type integer. On top of that, it gives the same error for text, string, date, and real. On a side note, how do you use an apostrophe S like that without it just considering it part of the variable's name? – Glab Sep 19 at 23:34
  • It should be noted that the "tell" block should not be nested in another tell block, so try the code at the bottom of the script, outside of all your other "tell" blocks. That might work better. Are you asking about "Applescript's"? That is just an AppleScript quirk I think. – Chris Norman Sep 20 at 0:00
  • Thanks, I will try that tomorrow when I'm back at work. About the apostrophe thing, yes, I am very curious about how people are able to use them without the script thinking that it's part of the variable. For example, when I try adding it to theDate, it would be read as a separate variable "theDate's" instead of being possessive like"theDate"'s. I've seen people use possessive 's all over the web in example code, but haven't seen a single note about how to make it work as intended. – Glab Sep 20 at 0:52
  • I think you also need to make theDate into a date object, as the error message you're getting shows that theDate is a string value. If you're lucky, you might be able to just put the date specifier before it as you were doing in your attempts you showed us, i.e. tell date theDate to.... If AppleScript is able to create a date object from that, then rather than involving text item delimiters, you can reformat it in one line like this: tell text 3 thru 10 of (date theDate as «class isot» as string) to set theDate to the contents of [word 2, "/", word 3, "/", word 1] as text – CJK Sep 20 at 5:47
  • ...Cont'd This has the benefit of giving you back the two-digit representations for months and days less than 10, and avoids dealing with text item delimiters. However, creating the date object from the string value you have depends entirely on whether your system can interpret that string as a valid date representation, which is dependent entirely on whatever you have set up in System Preferences as your date and time formatting preferences. Otherwise, you'll have to manipulate theDate as a string, which isn't difficult but just slightly annoying. – CJK Sep 20 at 5:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .