Set AutoSize for Cell Comment

Question:

Hi! I have a script that will put a list of formatted numbers into an Excel comment. I read the Tech data base article on this subject and it was very helpful, but I was wondering if any of ya'll know how to auto-size the comment field. I worked out a formula that did the job on one machine and then had to be edited to do the same on another. Basicly I want each line to consist of 13 characters (including spaces) and come out something like this:
0030_:_LQ01_ 0040_:_LQ05_
(the underscores are spaces). These alphanumerics are built into a string variable wcStr. The string does not have any CRLFs in it. Here is the code section with the formula for sizing the comment box:

objShape.ScaleWidth(0.65, msoFalse, msoScaleFromTopLeft) ht = (strLen(wcStr)/13) * .25 +.05 objShape.ScaleHeight(ht, msoFalse, msoScaleFromTopLeft)

I tried to get information from Excel macro recorder as suggested in the article, but 'Comments' seems to be one area it is limited on. I don't get any font object references at all. Any ideas will be appreciated!

Answer:

Excel comment example:


;--------------------------------------------------------------|
;********* Check to see if Excel & SAP files are Open *********|
If WinExist("Microsoft Excel") == @False
message("File Not Open","Please Restart Macro with Excel file OPEN")
exit
EndIf
WinActivate("Microsoft Excel")

row = AskLine("Excel File Info","Start on Row:","")

;--------------------------------------------------------------|
;********** Accessing the Excel file as an Object *************|

objXL = ObjectAccess("Excel.Application",@true)
objXL.Visible = @TRUE

objActiveWorkbook = objXL.ActiveWorkbook
if objActiveWorkbook == 0
	Message("","No active workbook")
	ObjectClose(objXL)
	exit
endif

objActiveSheet = objXL.ActiveSheet
if objActiveSheet == 0
	Message("","No active workSheet")
	ObjectClose(objActiveWorkbook)
	ObjectClose(objXL)
	exit
endif

;--------------------------------------------------------------|

workcntrs = "0030 : LB402 0030 : LB402 0030 : LB402 0030 : LB402 "
ht = (strLen(workcntrs)/13) * .19 +.15

objCell = objActiveSheet.Cells(row,2)
objCell.ClearComments
objCell.AddComment

objComment = objCell.Comment
objComment.Visible = @true
objComment.Text(:: Text= workcntrs)

msoFalse = 0
msoScaleFromTopLeft = 0
objShape = objComment.Shape
objShape.ScaleWidth(0.55, msoFalse, msoScaleFromTopLeft)
objShape.ScaleHeight(ht, msoFalse, msoScaleFromTopLeft)
;select the cell..
objShape.Select ;---------------- Select the item!!!!! 

;grab the selection 
objSelection = objXL.Selection ;-----Get selection reference 
objSelection.HorizontalAlignment = -4131 ;xlLeft
objSelection.VerticalAlignment = -4160 ;xlTop
objSelection.ReadingOrder = -5002 ;xlContext
objSelection.Orientation = -4128 ;xlHorizontal
objSelection.AutoSize = @True

;set the font. 
objFont = objSelection.Font
objFont.Name = "Courier New" 
objFont.Size = 10
objFont.Bold = @false
objFont.Italic = @false

Pause("Debugging","Check that the changes were made")

ObjectClose(objFont)
ObjectClose(objSelection)
ObjectClose(objShape)
ObjectClose(objComment)
ObjectClose(objCell)
ObjectClose(objActiveSheet)
ObjectClose(objActiveWorkbook)
ObjectClose(objXL)
exit

Article ID:   W16106

File Created: 2004:03:30:15:42:54

Last Updated: 2004:03:30:15:42:54

Database Search

TechHome