How to retrieve cell comment

[Hawkman0313]

1 - I am familiar with creating a link between a cell in FileA and another cell in FileB:

='\\MyServer\MyPath\MyFolder\[FileB.xlsm]MyWorksheet'!F6

In this case, the value of cell F6 in FileB is returned to the cell in FileA. FileB does not to be open for this to work well.

2 - If a file is open, one can collect the cell comment:

Dim txt as string
txt = Cells(6, 1).Comment.Text

My question: Is there a way to collect the comment using a link similar to #1?
I am hoping for something faster than opening and closing hundreds of file to collect each comment.

Thank you!

[Pepe Le Mokko]

Hi perhpas have a look at this page under "Copy comments form all sheets to another worksheet"

[Hawkman0313]

This location has some good stuff, but not what I'm looking for.

This link in a cell will return a value:

='\\MyServer\MyPath\MyFolder\[FileB.xlsm]MyWorksheet'!F6

This works well because I do not need to open the file. There are hundreds of files and each is large.

So ... can I modify the link to return a comment rather than the value?

Thank you.