I’m stuck trying to get this script to write to excel. I have the header rows working but I cant figure out how to write to the column heading and to find the next empty row. I tried searching the dictionary but I don’t know what to search for. Any help would be great.
tell application "Microsoft Excel"
make new document
set today to (current date)
set workbookName to ("Excel AppleScript Input.xlsx") as string
set destinationPath to (path to desktop as text) & workbookName
save active workbook in destinationPath
set value of range "A1:F1" to {{"Date", "Weight", "Steps", "Miles", "Shoes", "Comments"}}
tell application "Microsoft Excel"
tell used range of active sheet to set {maxRows, maxColumns} to {count rows, count columns}
end tell
-- ? How do I write to the cells under the column headings?
-- ? How do I code it to find the next blank row so that I can run this script and add data to the column headings?
set WalkDate to the text returned of (display dialog "Enter date" default answer "12/10/21")
end tell
````Preformatted text`
Not really sure what you’re trying to do in terms of entering new data but this script assumes that you want to enter rows of data into columns A:F. Basically, using intersect, it constructs ranges based on first blank row, the date, and other fields, and then adds new data to the new ranges.
tell application "Microsoft Excel"
tell active sheet
set headRange to range "A1:F1"
set value of headRange to {{"Date", "Weight", "Steps", "Miles", "Shoes", "Comments"}}
set keyRange to "A1"
set curr to current region of cell "A1"
set {maxRows, maxColumns} to {count rows, count columns} of curr
set moreData to {{2, 3, 4, 5, 6}, {2, 3, 4, 5, 6}}
repeat with newData in moreData
-- next new row
set maxRows to maxRows + 1
-- specify date column, other data columns
set colDate to "A:A"
set colRange to "B:F"
set rowRow to maxRows & ":" & maxRows as text
set WalkDate to the text returned of (display dialog "Enter date" default answer "12/10/21")
-- enter date into A of new row
set value of (intersect range1 colDate range2 rowRow) to WalkDate
-- select B:F of new row
-- select (intersect range1 colRange range2 rowRow)
set value of (intersect range1 colRange range2 rowRow) to newData
end repeat
end tell
end tell
This is better than what I had, and I thank you for your help.
What I’d like the end result to be would have a dialog prompt for Date, Weight, Steps, Miles and Shoes (with a drop down list of “A”, “B”, “C”) and pass results from ALL dialog prompts to the next open row in the spreadsheet. Any guidance would be most helpful. Thanks
Thanks Chris. You’re right, of course. I think it was just copy/paste from the original post. In one of my intermediary scripts I got rid of it but it crept back in. :
First, the spreadsheet layout is made. Note that if you run this on an existing document, it will work (and not overwrite data) but obviously if that document has different field names then they will be overwritten.
Next, it figures out the current date. As an aside, I detest this date format. In this context it means that you’ll have to fiddle with the date with every entry.
In the third section, it collects the data into variables
Finally, it finds the first empty row and deposits the data there.
tell application "Microsoft Excel"
tell active sheet
set headRange to range "A1:F1"
set value of headRange to {"Date", "Weight", "Steps", "Miles", "Shoes", "Comments"}
set curr to current region of cell "A1"
set {maxRows, maxColumns} to {count rows, count columns} of curr
set cd to (get current date)
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 currDate to {m, dd, yr} as text
set AppleScript's text item delimiters to ""
--> "1/17/23"
set walkDate to the text returned of (display dialog "Enter date" default answer currDate)
set listLists to {"Weight", "Steps", "Miles", "Shoes"}
set cellData to {}
repeat with cl in listLists
choose from list {"A", "B", "C"} with title "Choose " & cl default items "B"
set end of cellData to item 1 of result
end repeat
set maxRows to maxRows + 1
set colDate to "A:A"
set colRange to "B:E" -- does not include comments
set rowRow to maxRows & ":" & maxRows as text
set value of (intersect range1 colDate range2 rowRow) to walkDate
set value of (intersect range1 colRange range2 rowRow) to cellData
end tell
end tell
I should note that the script uses current region rather than used range. This is because the latter can generate false positives and resetting it without the use of VBA is awkward. What this means in a practical sense is that if additional stuff is added to the spreadsheet, as long as it is away from the current region (which begins at cell A1) then it will not affect data placement.
Ok, I gave this a go. First let me explain what the intent of this sheet is for; To track my walking steps on a daily basis and to see how many miles I put on my walking shoes. I change the shoes every QTR and I like to see how many miles I put on them.
I do appreciate what you have produced thus far. Very slick indeed.
The only column that requires a drop down list box is the shoe column.
If there was a data entry dialog box for the steps, weight & miles prompting me for that info, that would be perfect.
The A, B, C drop down was intended for the shoes only because I rotate shoes and want to be able to track miles on each set.
Hope this helps you see my end goal. Again, this is an excellent solution and I thank you for you help!!!
Thanks for the tips, I can make this work for what I intend to use it for. I have heard of Keyboard Maestro but never used it, I’ll check it out. Likewise with the other items.
One thing I always do when copying code from this site is to copy the URL and paste in top row as a comment. That way if I have to look back at the code it serves as point of reference. Thanks again!
Here’s something you can try. I’ve separated the shoes from the other data so it should work as you suggest.
To enter the data for ‘weight steps miles’, I’ve added another dialogue. Enter the values all at once, separated by spaces, e.g. 200 7000 3.2. The script will break that into three ‘words’ and insert them into the appropriate cell. I’ve added timeouts for the dialogues along with a check to see if the date dialogue was accepted before proceeding to the other data dialogue.
A couple of other additions:
Check cell A1… if its value is “Date” then don’t overwrite the header cells.
For the date dialogue, there are two ways to enter a date: a string like the one you have, or an integer that moves that date that many days from the current date.
For example, -2 would set it to 1/16/23 or two days ago. A positive integer would put it into the future so there’s probably not much use in this scenario. You could make positive integers go backwards (to save the keystroke) by reversing the polarity of the if…then statement.
A note on this, there may be an issue depending upon your System Preferences date format. I don’t think it will affect you but it breaks on days under 13 on my system because my date format is yyyy-mm-dd. If the middle digits are under 13 then it assumes that they’re the month and converts to my format of date. If higher than 12 it leaves as text. I’m guessing that your system is configured differently.
Anyway, here is the script:
tell application "Microsoft Excel"
-- activate
tell active sheet
set headRange to range "A1:F1"
if value of cell "A1" is not "Date" then
set value of headRange to {"Date", "Weight", "Steps", "Miles", "Shoes", "Comments"}
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 8)
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
if button returned of enterWalkDate is "OK" then
set shoeList to item 1 of (choose from list {"A", "B", "C"} with title "Choose shoes" default items "C")
-- enter 3 numbers separated by space
set enterDataString to display dialog "Enter 'weight steps miles'" default answer "weight steps miles" giving up after 16
set dataString to text returned of enterDataString
set dataWords to words of dataString
end if
set maxRows to maxRows + 1
set colDate to "A:A"
set colRange to "B:D" -- weight steps miles
set colShoes 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 colShoes range2 rowRow) to shoeList
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
This works very well. I can use this as is. The only tweak I made to to increase the wait from 16 to 30. Pretty slick little piece of code. I appreciate your programming skills and willingness to help. Thank you!
Glad that it’s useful. I often find it awkward to deal with dates so this was a good experiment.
I should add that I often do the same with putting a link/title into a script’s comment. I was concerned that the links wouldn’t work when they migrated the forum to discourse but it was handled pretty smoothly.
I have another question; I found another use for this and I set it up based on what I thought would work but the text input on my adjusted field input is only about 3 charcters long. How would I go about making a tweak to get it to take my input…See below.
I also can’t recall how to show the code in here, did it once my my mind is a blank
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
and down here...
-- enter 3 numbers separated by space
set enterDataString to display dialog "Enter 'Cost Title Band Location'" default answer "Cost Title Band Location" giving up after 30
set dataString to text returned of enterDataString
set dataWords to words of dataString
end if
Select the code and click the ‘preformatted text’ button (or type command-e)
Prefix each line with 4 spaces
Type 3 back-ticks, e.g. ```, on the line before and the line after the code
As to the excel entry… what did you enter into the dialogue for ‘cost title band’? And what are you getting in excel?
If you are going to enter four fields of data, then you need to modify the colRange variable earlier, e.g.
set colRange to "B:E"
Then delete the subsequent line, set colShoes to "E:E" as it will overwrite the use of column E. If you’re not going to use the choose from list, i.e. shoes, then comment out the shoeList intersect line.
In essence, you set colRange to the cells/columns you want to deposit the data in. Then, you provide that data —separated by spaces— in the dialogue.
The intent of my repurposed script is to track CD’s that I buy. I changed to shoe drop down to a Location drop down and it works just like the shoe drop down did. I also changed the column names and those work as intended. New column names are Date, Cost, Title, Band & Location. It all works just like the Shoe Tracker.
The problem is that when I enter a title, its more than one word and the overflow spills into the next column. Likewise for the band. In the example below, I entered text as it relates to the new field names. Note the band and title were entered like I did for the shoe tracker. Notice that I entered The (space) Who (space) and the data moved as in row 2. Is there a way to let me enter the Title like “Who’s Next” (space) “The Who” as in row 3 shown below.
1/21/23 3.99 TheWho TheWho Location A
1/21/23 3.99 The Who Location A
1/21/23 3.99 Who’s Next The Who Location B
Obviously, with multi-word band names and titles, the dirt simple approach won’t work. I stopped thinking there for a bit. Probably changing the delimiter to another character (e.g. from space to something like ‘^’) and using text items would work. Let me think about it for a bit.
If you separate the texts with a unique character then you should be able to parse the string easily. I’d suggest using a character that isn’t likely to come up in the name of a band or album, for example ‘^’. I’ve also included a space before the caret, so either ^The Who ^Who's Next or ^The Who^Who's Next would work. Adjust according to your needs.
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"}
One note, you can use the mac’s text shortcuts in a dialogue text field. So you can set up an easy to type shortcut (e.g. ‘[[[’ and have it turn into ‘^^’) and then set your delimiters to ‘^^’ which is really unlikely to be in a name. I guess that you could even set the delimiters to space space and use that to divide the items.
I did a search of textdelimeter and got it working somewhat, I noticed that when you set the delimeters to something, you need to set them back. I added that to the code and it s working but I still have a small issue that I’m working thru…
I figured it out…It took a while but I got it working. Very cool trick using those textdelimeters. I thank you once again, I learned a lot from this exercise!
