Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Inserting Comment to Cell in Excel
#1
Hi All,
Just starting to get into the Excel functions. I can do the basics (set cell, get cell, etc) but I would like to set a comment onto a specific cell. I am looking into the VB script in how to do this but not sure how to translate that into QMscript.

I am looking at this example and still confused:
Macro Macro
Code:
Copy      Help
;select range of cells
ExcelSheet es2.Init
es2.ws.Range("A1:B2").Select
;save all files and close Excel
Excel.Application ea=es2.ws.Application
Excel.Workbook wb; foreach(wb ea.Workbooks) wb.Save
ea.Quit

I see a ws.Comments option but not sure how to syntax it for a specific string insert and cell address.

Thanks for any help!

Stuart
#2
Macro Macro1548
Code:
Copy      Help
ExcelSheet es.Init
es.ws.Range("A1").AddComment("xxxxxxxxx")
;or
Excel.Comment c=es.ws.Range("A2").AddComment
c.Text("yyyyyyyyy")
c.Visible=TRUE
#3
Member function ExcelSheet.SetComment
Code:
Copy      Help
function $cell $text

;Adds or changes cell comment.

;cell - cell, such as "A1". Use "sel" for current selection.
;text - comment text.

;EXAMPLE
;ExcelSheet es.Init
;es.SetComment("A2" "bbbbbb")


Excel.Range r
if(!StrCompareN(cell "sel" 3 1)) r=ws.Application.Selection
else r=ws.Range(cell)

r.AddComment(text)
err ;;error if comment already exists
,Excel.Comment c=r.Comment
,c.Text(text)
#4
Thanks!!!! Amazing as always!!!!
S
#5
Hi Gintaras,
Thanks again for the Comment Insert function. Working more with the Excel function, I see the need for a function to convert column numbers (QM excel functions) to column names (for interaction with VB scripts refernces in QM). I looked around and found some examples on websites. I was able to almost convert one of the examples into QM but couldn't find a QM equivalent/import of Convert.ToChar

See examples at http://stackoverflow.com/questions/18159...lumn-eg-aa

Example code:
Code:
Copy      Help
private string GetExcelColumnName(int columnNumber)
{
    int dividend = columnNumber;
    string columnName = String.Empty;
    int modulo;

    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;
        columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
        dividend = (int)((dividend - modulo) / 26);
    }

    return columnName;
}

Is this an easy thing to get into QM? This is not essential for my current project because I won't go beyond the first alphabet letters and I can manually map those but in the future, I can see the utility of this.

Thanks for any thoughts!
S
#6
Lucas created function for it: Excel cell reference


Forum Jump:


Users browsing this thread: 1 Guest(s)