Parsing Excel files for Comments
Parsing Excel files for Comments
- Subject: Parsing Excel files for Comments
- From: Patrik <email@hidden>
- Date: Tue, 17 Apr 2012 14:49:34 -0700
Hi, I just started fours days ago scripting Excel 2011 and found some tips
on line but ran into a speed bug when parsing an Excel file for comments.
I basically use the Comment field as a label and want to replace out the
content of a cell based on the comment label so to speak.
All works fine but the parsing of a file with more than 3600 cells takes
over 10 minutes and longer files don't even finish. Any ideas on how I can
either modify the script to run faster or is there some other way of
labeling a cell with a unique "tag" similar to Indesigns XML tags. Again I
am totally new to scripting excel.
Here is my parser along with a timer:
property mycounter : 0
set starttime to my gettime()
my ParseExcellforComments()
set endtime to my gettime()
set totaltime to endtime - starttime
tell application "Microsoft Excel"
activate
display dialog "Script finished running. It took " & totaltime & "
seconds. " & mycounter & " cells checked." buttons {"OK"} default button 1
end tell
----------------------------gets time as string
on gettime()
tell AppleScript
set mytime to time of (current date)
end tell
return mytime
end gettime
----------------------------parses all cell for comments and then makes a
list of cells which have a comment
on ParseExcellforComments()
tell application "Microsoft Excel"
set MyCell to {}
set mycount to count of every worksheet in active workbook
repeat with mynumber from 1 to (mycount)
set ur to used range of worksheet mynumber
repeat with i from 1 to count of cells of ur
set mycounter to mycounter + 1
tell active workbook to activate object worksheet mynumber
set cl to cell i of ur
try
set cmt to Excel comment text Excel comment of cl
set end of MyCell to {cell i of ur, cmt}
end try
end repeat
end repeat
end tell
return MyCell
end ParseExcellforComments
Any ideas or suggestions are appreciated.
Best, Patrik
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden