Sunday, December 17, 2017

#1 2017-11-17 10:45:05 am

Black Panther
Member
Registered: 2016-03-14
Posts: 1

Applescript Command(s) To Correctly Capture Excel Comments Of Cell

What is the correct code to get an existing cell comment?

The following block of code "seems to work" to access desired cell values from an Excel file.  I note, "seems to work" only because while I'm certain of the employee name match and the other values (i.e. found row, hire date and affiliated company), I can't tell if I've successfully captured the comment which holds the employee's position title associated with cell "B:found row".

tell application "Microsoft Excel"
   set searchRange to range (("B" & Brunswick_Start as text) & ":B" & Ranger_End as text)
   set foundRange to find searchRange what PAF_Employee with match case
   set fRow to first row index of foundRange
   set rDate to value of range ("L" & fRow as text)
   set rCompany to value of range ("C" & fRow as text)

    set rTitle to its Excel comment of range ("B" & fRow as text)

    select foundRange
end tell

After numerous searches, I've only found the 2007 code below at MacTECH to set a cell comment and I'm not sure it correctly gets the comment of a cell as I've tried to use above.  I get the "Can’t make «class X229» of «class ccel» "B27" of application "Microsoft Excel" into type text" error when attempting to use the comment variable rTitle as text.

In AppleScript:
tell application "Microsoft Excel"
     tell range "A10" of active sheet
          set cmt to its Excel comment -- no error if nothing (dummy comment)
          set vis to visible of cmt --get any property , returns missing value if empty
          if vis is missing value then
              set cmt to add comment
          end if
     end tell
   
     tell cmt
          Excel comment text text "My comment" --overwrites, but omit 'over write'!
          set visible to true
          select its shape object -- to allow editing
     end tell
end tell


Filed under: excel

Offline

 

#2 2017-11-17 12:56:01 pm

DJ Bazzie Wazzie
Member
From:: the Netherlands
Registered: 2004-10-20
Posts: 2727
Website

Re: Applescript Command(s) To Correctly Capture Excel Comments Of Cell

You could use python and the xlsxwriter module to write comments to cells.

Offline

 

#3 2017-11-17 04:12:37 pm

haolesurferdude
Member
Registered: 2010-02-19
Posts: 109

Re: Applescript Command(s) To Correctly Capture Excel Comments Of Cell

Try changing your line

Applescript:

set vis to visible of cmt --get any property , returns missing value if empty

to

Applescript:

set vis to visible of its Excel comment --get any property , returns missing value if empty

As it is now, an error is thrown when the cell has no comment.  This avoids that and causes your comment to be created when the cell has no comment.

Hope this is helpful and I didn't miss the point...

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)