PowerShell Excel Addcomment Object

[jkw...@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]

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, jkw...@gmail.com wrote:
O>
>> 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

[OldDog]

On Aug 25, 11:25 am, Shay Levy [MVP] <n...@addre.ss> wrote:
> 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 MVPhttp://blogs.microsoft.co.il/blogs/ScriptFanatic

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

[eric.f...@gmail.com]

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