PowerShell Excel Addcomment Object

[j***@gmail.com]

Okay, I've done some programming in the past and now I'm using
powershell.
It's a great Scripting language, and I'm working on some things. But
where I am at now is that I have a lot of fields/information I want to
get onto one table. I would like to use the comment field in excel.
Thus someone would hang their mouse over a cell of info and it'll give
them more info in the comment field without having another whole
column for it (as it would get pretty ugly fast).

[OldDog]

Post by j***@gmail.com
Okay, I've done some programming in the past and now I'm using
powershell.
It's a great Scripting language, and I'm working on some things. But
where I am at now is that I have a lot of fields/information I want to
get onto one table. I would like to use the comment field in excel.
Thus someone would hang their mouse over a cell of info and it'll give
them more info in the comment field without having another whole
column for it (as it would get pretty ugly fast).

Hi,

Well, this will add a comment. But It needs a line feed after the
commnents author.
It's an ascii Char10 in VBA, not sure how to do that in PowerShell.

$objExcel = New-Object -com Excel.Application
$objExcel.visible = $True
$objWorkbook = $objExcel.Workbooks.Add()
$objWorksheet = $objWorkbook.Worksheets.Item(1)
$objWorksheet.Cells.Item(1,1) = “A value in cell A1.”
$objWorksheet.Range("A1").AddComment()
$objWorksheet.Range("A1").comment.Visible = $False
$objWorksheet.Range("A1").comment.Text = ("OldDog: " "Char10" "this is
a comment") <---- needs a LF here
$objWorksheet.Range("A2").Select

[Shay Levy [MVP]]

Hello OldDog,


Try with "`r", it is the PowerShell equivalent to LF:


$objWorksheet.Range("A1").AddComment("OldDog: `rthis is a comment")


---
Shay Levy
Windows PowerShell MVP
http://blogs.microsoft.co.il/blogs/ScriptFanatic

O> On Aug 24, 7:11 am, ***@gmail.com wrote:
O>

Post by j***@gmail.com
Okay, I've done some programming in the past and now I'm using
powershell.
It's a great Scripting language, and I'm working on some things. But
where I am at now is that I have a lot of fields/information I want to
get onto one table. I would like to use the comment field in excel.
Thus someone would hang their mouse over a cell of info and it'll give
them more info in the comment field without having another whole
column for it (as it would get pretty ugly fast).

O> Hi,
O>
O> Well, this will add a comment. But It needs a line feed after the
O> commnents author.
O> It's an ascii Char10 in VBA, not sure how to do that in PowerShell.
O> $objExcel = New-Object -com Excel.Application
O> $objExcel.visible = $True
O> $objWorkbook = $objExcel.Workbooks.Add()
O> $objWorksheet = $objWorkbook.Worksheets.Item(1)
O> $objWorksheet.Cells.Item(1,1) = “A value in cell A1.”
O> $objWorksheet.Range("A1").AddComment()
O> $objWorksheet.Range("A1").comment.Visible = $False
O> $objWorksheet.Range("A1").comment.Text = ("OldDog: " "Char10" "this
O> is
O> a comment") <---- needs a LF here
O> $objWorksheet.Range("A2").Selec

[OldDog]

Post by Shay Levy [MVP]
Hello OldDog,
$objWorksheet.Range("A1").AddComment("OldDog: `rthis is a comment")
---
Shay Levy
Windows PowerShell MVPhttp://blogs.microsoft.co.il/blogs/ScriptFanatic
O>>> Okay, I've done some programming in the past and now I'm using

Post by j***@gmail.com
powershell.
It's a great Scripting language, and I'm working on some things. But
where I am at now is that I have a lot of fields/information I want to
get onto one table. I would like to use the comment field in excel.
Thus someone would hang their mouse over a cell of info and it'll give
them more info in the comment field without having another whole
column for it (as it would get pretty ugly fast).

O> Hi,
O>
O> Well, this will add a comment. But It needs a line feed after the
O> commnents author.
O> It's an ascii Char10 in VBA, not sure how to do that in PowerShell.
O> $objExcel = New-Object -com Excel.Application
O> $objExcel.visible = $True
O> $objWorkbook = $objExcel.Workbooks.Add()
O> $objWorksheet = $objWorkbook.Worksheets.Item(1)
O> $objWorksheet.Cells.Item(1,1) = “A value in cell A1.”
O> $objWorksheet.Range("A1").AddComment()
O> $objWorksheet.Range("A1").comment.Visible = $False
O> $objWorksheet.Range("A1").comment.Text = ("OldDog: " "Char10" "this
O> is
O> a comment") <---- needs a LF here
O> $objWorksheet.Range("A2").Select

OK, this works;

$objExcel = New-Object -com Excel.Application
$objExcel.visible = $True
$objWorkbook = $objExcel.Workbooks.Add()
$objWorksheet = $objWorkbook.Worksheets.Item(1)
$objWorksheet.Cells.Item(1,1) = “A value in cell A1.”
[void]$objWorksheet.Range("A1").AddComment()
[void]$objWorksheet.Range("A1").comment.Visible = $False
[void]$objWorksheet.Range("A1").Comment.text("OldDog: `rthis is a
comment")
[void]$objWorksheet.Range("A2").Select


The [void] keeps it from writing out extranious stuff to the screen.

OldDog

[e***@gmail.com]

Post by Shay Levy [MVP]
Hello OldDog,
$objWorksheet.Range("A1").AddComment("OldDog: `rthis is a comment")
---
Shay Levy
Windows PowerShell MVP
http://blogs.microsoft.co.il/blogs/ScriptFanatic
O>

Post by j***@gmail.com
Okay, I've done some programming in the past and now I'm using
powershell.
It's a great Scripting language, and I'm working on some things. But
where I am at now is that I have a lot of fields/information I want to
get onto one table. I would like to use the comment field in excel.
Thus someone would hang their mouse over a cell of info and it'll give
them more info in the comment field without having another whole
column for it (as it would get pretty ugly fast).

O> Hi,
O>
O> Well, this will add a comment. But It needs a line feed after the
O> commnents author.
O> It's an ascii Char10 in VBA, not sure how to do that in PowerShell.
O> $objExcel = New-Object -com Excel.Application
O> $objExcel.visible = $True
O> $objWorkbook = $objExcel.Workbooks.Add()
O> $objWorksheet = $objWorkbook.Worksheets.Item(1)
O> $objWorksheet.Cells.Item(1,1) = “A value in cell A1.”
O> $objWorksheet.Range("A1").AddComment()
O> $objWorksheet.Range("A1").comment.Visible = $False
O> $objWorksheet.Range("A1").comment.Text = ("OldDog: " "Char10" "this
O> is
O> a comment") <---- needs a LF here
O> $objWorksheet.Range("A2").Select

Is it possible to AddComment() for a range of cells?
ie: $objWorksheet.Range("A1:A12").AddComment()

Because when I try that I get an error: Value does not fall within the expected range.

Is this doable?
thanks