Here’s the complete code.
--Added to library on 01/19/2023
--This script takes my CD purchase data from the script and writes to excel
--https://www.macscripter.net/t/applescript-to-excel-help/74054/20
--Change file name as needed
set CDDoc to (path to desktop as text) & "Excel CD Input.xlsx"
set resultList to {}
tell application "Microsoft Excel"
-- opens the file CDDoc
open file CDDoc
-- activate
tell active sheet
--this sets up column headings, so ensure the range A1:E1 and the number of headrange values are in alignment.
--If you need more col headings expand range as needed and be sure to add to values inside{}
set headRange to range "A1:E1"
if value of cell "A1" is not "Date" then
set value of headRange to {"Date", "Cost", "Title", "Band", "Location"}
set bold of font object of headRange to true
end if
set curr to current region of cell "A1"
set {maxRows, maxColumns} to {count rows, count columns} of curr
end tell
end tell
-- enter either string e.g. "1/14/23" …
-- or integer, with negative optional e.g. 5, -4
-- Will return date string as entered or modified by days entered
set enterWalkDate to (display dialog "Enter date" default answer dateHand(get current date) giving up after 10)
set testDate to the text returned of enterWalkDate
if testDate contains "/" then -- is it already a date string
set walkDate to testDate
--> "1/17/23"
else
set newDate to (get current date) + ((testDate as integer) * days)
set walkDate to dateHand(newDate)
end if
--Change vales to your drop down list here...{}
if button returned of enterWalkDate is "OK" then
set CDList to item 1 of (choose from list {"Loc A", "Loc B", "Loc C"} with title "Choose Location" default items "Loc A")
-- enter data like the "^"
set enterDataString to display dialog "Cost, Band, Album" default answer "4.99 ^The Who ^Who's Next"
-- or
--set enterDataString to display dialog "Cost, Band, Album" default answer "4.99 ^The Who ^Live at Leeds"
set AppleScript's text item delimiters to {" ^"}
set dataWords to text items of text returned of enterDataString
--> {"4.99", "The Who", "Who's Next"}
--> {"4.99", "The Who", "Live at Leeds"}
set AppleScript's text item delimiters to "" --had to add this bit to make it work
end if
set maxRows to maxRows + 1
set colDate to "A:A"
set colRange to "B:D" -- cost title band loc
set collocation to "E:E"
set rowRow to maxRows & ":" & maxRows as text
tell application "Microsoft Excel"
set value of (intersect range1 colDate range2 rowRow) to walkDate
set value of (intersect range1 colRange range2 rowRow) to dataWords
set value of (intersect range1 collocation range2 rowRow) to CDList
end tell
on dateHand(cd)
set {yr, m, dd} to {(year of cd) - 2000, month of cd as integer, day of cd}
set AppleScript's text item delimiters to "/"
set insertDate to {m, dd, yr} as text -- e.g. "1/18/23"
set AppleScript's text item delimiters to ""
return insertDate as text
end dateHand
-- handler accepts date object, e.g. date "Wednesday, January 18, 2023 at 20:55"
-- converts to string with format "1/14/23"
-- NB date format is m/d/yy
--saves workbook
tell application "Microsoft Excel"
save workbook
end tell
```