Sign in to follow this  
Followers 0
Ram

Getting values from excel column's Comment.

7 posts in this topic

Hi,

I am looking for an example where I need to read the comments on a column in an excel file. Is this possible? if so how do I go about doing it??

Awaiting your response!

Thanks!

Share this post


Link to post
Share on other sites



Hi,

I am looking for an example where I need to read the comments on a column in an excel file. Is this possible? if so how do I go about doing it??

Awaiting your response!

Thanks!

Start by doing a search in the example scripts forum for Excel

There are at least two UDF's but I'm not sure if they have anything for comments or not. I do know that they can be read by creating a COM object.


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

Start by doing a search in the example scripts forum for Excel

There are at least two UDF's but I'm not sure if they have anything for comments or not. I do know that they can be read by creating a COM object.

Yep did search through all the UDF's example but didn't get hold of any example for my issue hence posting this issue..!!

Share this post


Link to post
Share on other sites

The UDF's will help you get a cell reference. Once you have that, it will have a .Comment property, which in turn has a .text property

A full reference in VBScript (taken from Excel Help) can look like:

Worksheets(1).Range("E5").Comment.Text

This may still not be clear, so I suggest you take a shot at it and post some code and you'll get the rest of the help you need.

Dale


Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl

MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model

Automate input type=file (Related)

Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded  Better Better?

IE.au3 issues with Vista - Workarounds

SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead?

Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble

Share this post


Link to post
Share on other sites

Using my collection of functions, found at the link in my signature:

; Load the comments of the first 20 lines of column 1 into an array

#include <ExcelCOM_UDF.au3>
#include <Array.au3>

Global $aComments[21]

$oExcel = _ExcelBookOpen("C:\Path\YourExcelFile.xls")

For $xx = 1 To 20
    $aComments[$xx] = _ExcelCommentRead($oExcel, $xx, 1)
Next

_ArrayDisplay($aComments, "Here Are The Comments")

Good luck in your Excel endeavors!

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

Hello,

If anyone has a spare moment a bit of guidance would be greatly appreciated.

I have been looking for this solution when I came across this thread. I have been able to successfully use Locodarwin's UDF when I know that there is a comment in the cell, however when I try to use it on a cell without a comment it breaks my script. I was hoping someone may be able to assist me in solving this problem:

#include <Include\ExcelCOM_UDF.au3>
#include <Include\Array.au3>
$oExcel = _ExcelBookOpen("\\192.168.1.10\path\to\excel.xlsm")
$aComments = _ExcelCommentRead($oExcel, "AV144")

ConsoleWrite($aComments & " <<Here Are The Comments" & @LF)
$aComments = _ExcelCommentRead($oExcel, "AV143")
ConsoleWrite($aComments & " <<Here Are The Comments" & @LF)

The output I get is:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "\\192.168.1.10\f\path\to\au3testfiles\test.au3"

me:

3827 This is the comment I put in this cell <<Here Are The Comments

\\192.168.1.10\f\path\to\au3testfiles\Include\ExcelCOM_UDF.au3 (1525) : ==> Variable must be of type "Object".:

Return $oExcel.Range($sRangeOrRow).Comment.Text

Return $oExcel.Range($sRangeOrRow).Comment^ ERROR

>Exit code: 1 Time: 13.862

I have tried a various number of different things such as wrapping an if else statement around IsObj to determine if it was an object or not as well as if/then's around @error checking but I can't seem to get it to not break if there is no cell comment present, and I am uncertain how else to look for a comment.

-K

Share this post


Link to post
Share on other sites

Keltset,

this thread is 5 1/2 years old. It would be best to open a new one.

Your script needs some error checking. Was _Excelbookopen successfull and did it return an object?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0