Inserting Comment to Cell in Excel

[stupomer]

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

[Gintaras]

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

[Gintaras]

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)

[stupomer]

Thanks!!!! Amazing as always!!!!
S

[stupomer]

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

[Gintaras]

Lucas created function for it: Excel cell reference