Excel Automation (VFP6) - Formatting Excel Comment Text

[KarenLloyd]

Hi

I'm working on a report output to Excel using VFP automation and would like to add comment text to some of the cells. I can do most of this from the Excel macro code.

My question is - how do I automate the formatting of the Font & Size of the text in the comment boxes? - as it is way too small in its original settings.

Thank you

Karen

 

[KarenLloyd]

...I decided to try various ways and found the solution - so not to worry. If anyone needs to do this in future you can do as follows:

oExcelSession.ActiveCell.AddComment
oExcelSession.ActiveCell.Comment.Shape.Select
oExcelSession.Selection.Text = "Add your text in here..."
oExcelSession.Selection.Font.Name = "Arial"
oExcelSession.Selection.Font.Size = 12

I know the selection method may not be the speediest - but it is the only solution I have so far

Karen

 

[Mike Lewis]

Hi Karen,

An interesting question.

As far as I can see, there's no way of doing this programmatically. There is a Comment object, which has a Text property, but no formatting properties.

That's odd, because you can certainly format a comment interactively within Excel.

Perhaps someone else knows the answer.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Mike Lewis]

Karen,

You beat me to it.

I said perhaps someone else knew the answer. I was right <g>. Well done.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[KarenLloyd]

Hi Mike

Thanks for replying.

I thought I had it cracked then. From the command window I got it to work but now its giving Unspecified Error.

Ah well - back to the drawing board...

Karen

 

[KarenLloyd]

I have got it...

The above code does work - but only when Comments are visible on the Worksheet. (If not you get Unspecified Error)

So if you make Comments visible first and at the end set DisplayCommentIndicator to -1 (xlCommentIndicatorOnly)

Good stuff.

 

[Mike Lewis]

Ah, yes. I see what you mean.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business