Page 1 of 1
Forum

Welcome to the Tweaking4All community forums!
When participating, please keep the Forum Rules in mind!

Topics for particular software or systems: Start your topic link with the name of the application or system.
For example “MacOS X – Your question“, or “MS Word – Your Tip or Trick“.

Please note that switching to another language when reading a post will not bring you to the same post, in Dutch, as there is no translation for that post!



Read and write to a...
 
Share:
Notifications
Clear all

Read and write to a spreadsheet using AppleScript and Excel - Emmanuel Katto Uganda

2 Posts
2 Users
0 Reactions
266 Views
(@emmanuelkatto)
New Member
Joined: 2 weeks ago
Posts: 1
Topic starter  

Hello All, I am Emmanuel Katto. I'm trying to create a script that can read and write data to an Excel spreadsheet using AppleScript. I've managed to get the script to open the spreadsheet and select the correct worksheet, but I'm having trouble figuring out how to actually read and write data to the cells.

I've tried using the tell application "Microsoft Excel" command to interact with the Excel application, but I'm not sure how to use it to access specific cells or ranges. I've also tried using the get and set statements, but they don't seem to be working as expected.

 

Can anyone help me with this?

Thanks!

Emmanuel

This topic was modified 2 weeks ago by Hans

   
ReplyQuote
 Hans
(@hans)
Famed Member Admin
Joined: 11 years ago
Posts: 2724
 

Hi Emmanuel,

It's been a while that I even touched Excel, but this example (found here) works like a charm for me on my Mac:

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

 Make sure Excel is open and has an active Excel file / Sheet open.

 From this we can learn how to set a cell. Short version:

tell application "Microsoft Excel"
  tell active sheet
    set value of cell "A2" to "Some random text"
  end tell
end tell

Both worked on my Mac with Excel that comes with Office 265 (Version 16.86 (24060916) - 2024).

Hope this helps 😊 


   
ReplyQuote
Share: