Resize the comments in Excel with Access

[FLabrecque]

Hi everyone!

I'm using Access to generate an Excel file. I'm exporting my query in Excel format. Then I open it in Access and do a bunch of cosmetic changes (such as making lines, bold titles, etc).

I'm also creating comments inside the Excel file based on information stored in a table. The comments appears fine (although they are always in bold character, but I can live with that). My problem comes with the default size of the comment block. They are too small. I need to make them bigger with code from Access.

The code I'm using to create the comment in Excel is this:

xlsSheet.Range(xlsSheet.Cells(1, 1), xlsSheet.Cells(1, 1)).AddComment.Text strCOMMENT

I can't find anything in Access or this forum to help me. I've tried to create a macro in Excel to see how it works. Here it is:

    Range("A5").Comment.Text Text:="labref01:" & Chr(10) & "This is my comment!" & Chr(10) & ""
    Selection.ShapeRange.ScaleWidth 1.82, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.58, msoFalse, msoScaleFromTopLeft

For some obscure reason, when I try to execute the macro again, it stops at the first shaperange with a "object doesn't support this property or method".

With all this said, my question is this: How can I resize the comments in an Excel file through Access coding?

Thanks!

 

[Bat17]

Just guessing, as I can't test it at the moment but you probably need to set a reference to it so that you can refer to it again.

dim obj as object
set obj = xlsSheet.Range(xlsSheet.Cells(1, 1), xlsSheet.Cells(1, 1)).AddComment.Text strCOMMENT
obj.ShapeRange.ScaleWidth 1.82, msoFalse, msoScaleFromTopLeft
obj.ShapeRange.ScaleHeight 1.58, msoFalse, msoScaleFromTopLeft

You will probably need a reference set to Excel for msoFalse to work.

HTH

Peter

 

[FLabrecque]

I'll try that, thanks! I'm quite willing to work hard to figure this one out.

 

[FLabrecque]

Sometimes, the solution is so simple! Here is the solution that I found, based on your help:

xlsSheet.Range(xlsSheet.Cells(1, 1), xlsSheet.Cells(1, 1)).AddComment.Text strTMP
xlsSheet.Range(xlsSheet.Cells(1, 1), xlsSheet.Cells(1, 1)).Comment.Shape.ScaleWidth 1.75, False

This code enlarge the width of the comment by another 75%. I need to figure out how to calculate my % to fit every comments, but that's trial and error.