Hi, I'm back.

I am trying to find the word "FALSE" in Column D of my worksheet and
then copy the whole row to a new sheet.
Code so far:

$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$xlCellTypeLastCell = 11
#Open spreadsheet
$wb = $xl.workbooks.open("C:\Temp\test.xls")
# rename the Worksheet. 30 charactor limit
$ws1 = $wb.worksheets | where {$_.name -eq "xml_source"}
$ws2 = $wb.worksheets | where {$_.name -eq "Report"}
$ws3 = $wb.worksheets | where {$_.name -eq "sheet3"}
# Activate sheet 1
[void]$ws1.activate()
#Start Test
$i = 2
For ($i = $zRow; $i -eq $R; $i++) {
If ($ws1.Cell.Value($i, 4) = "FALSE") {
$range4=$ws1.range("${i}:$i")
$range4.Copy()
$ws2.Activate
$ws2.Cells.Item($i,1).PasteSpecial(-4163)
}
}

The problem is, I get this error:

ERROR: You cannot call a method on a null-valued expression.
ERROR: At line:29 char:23
ERROR: + If ($ws1.Cell.Value( <<<< $i, 4) = "FALSE") {

I am thinking that it does not like the word "FALSE" but i might be
overthinking.

Any ideas?

TIA,



OldDog

Try coding it as

If ($ws1.Cell.Value($i, 4) -eq "FALSE") {

- Larry


On 5/11/2010 10:34 AM, OldDog wrote:

> Hi, I'm back.
>
> I am trying to find the word "FALSE" in Column D of my worksheet and
> then copy the whole row to a new sheet.
> Code so far:
>
> $xl = New-Object -comobject Excel.Application
> $xl.Visible = $true
> $xl.DisplayAlerts = $False
> $xlCellTypeLastCell = 11
> #Open spreadsheet
> $wb = $xl.workbooks.open("C:\Temp\test.xls")
> # rename the Worksheet. 30 charactor limit
> $ws1 = $wb.worksheets | where {$_.name -eq "xml_source"}
> $ws2 = $wb.worksheets | where {$_.name -eq "Report"}
> $ws3 = $wb.worksheets | where {$_.name -eq "sheet3"}
> # Activate sheet 1
> [void]$ws1.activate()
> #Start Test
> $i = 2
> For ($i = $zRow; $i -eq $R; $i++) {
> If ($ws1.Cell.Value($i, 4) = "FALSE") {
> $range4=$ws1.range("${i}:$i")
> $range4.Copy()
> $ws2.Activate
> $ws2.Cells.Item($i,1).PasteSpecial(-4163)
> }
> }
>
> The problem is, I get this error:
>
> ERROR: You cannot call a method on a null-valued expression.
> ERROR: At line:29 char:23
> ERROR: + If ($ws1.Cell.Value(<<<< $i, 4) = "FALSE") {
>
> I am thinking that it does not like the word "FALSE" but i might be
> overthinking.
>
> Any ideas?
>
> TIA,
>
> OldDog

On May 11, 10:40*am, Larry__Weiss <l...@newsgroup> wrote:

> Try coding it as
>
> * *If ($ws1.Cell.Value($i, 4) -eq "FALSE") {
>
> * - Larry
>
> On 5/11/2010 10:34 AM, OldDog wrote:
>
>
>

> > Hi, I'm back.

>

> > I am trying to find the word "FALSE" in Column D of my worksheet and
> > then copy the whole row to a new sheet.
> > Code so far:

>

> > $xl = New-Object -comobject Excel.Application
> > $xl.Visible = $true
> > $xl.DisplayAlerts = $False
> > $xlCellTypeLastCell = 11
> > #Open spreadsheet
> > $wb = $xl.workbooks.open("C:\Temp\test.xls")
> > # rename the Worksheet. 30 charactor limit
> > $ws1 = $wb.worksheets | where {$_.name -eq "xml_source"}
> > $ws2 = $wb.worksheets | where {$_.name -eq "Report"}
> > $ws3 = $wb.worksheets | where {$_.name -eq "sheet3"}
> > # Activate sheet 1
> > [void]$ws1.activate()
> > #Start Test
> > $i = 2
> > For ($i = $zRow; $i -eq $R; $i++) {
> > * * If ($ws1.Cell.Value($i, 4) = "FALSE") {
> > * *$range4=$ws1.range("${i}:$i")
> > * *$range4.Copy()
> > * *$ws2.Activate
> > * *$ws2.Cells.Item($i,1).PasteSpecial(-4163)
> > * *}
> > * }

>

> > The problem is, I get this error:

>

> > ERROR: You cannot call a method on a null-valued expression.
> > ERROR: At line:29 char:23
> > ERROR: + * *If ($ws1.Cell.Value(<<<< *$i, 4) = "FALSE") {

>

> > I am thinking that it does not like the word "FALSE" but i might be
> > overthinking.

>

> > Any ideas?

>

> > TIA,

>

> > OldDog- Hide quoted text -

>
> - Show quoted text -

I left out the part where I defined $zRow and $R

$row = 2
$xRow = 2
$zRow = 2
$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$xlCellTypeLastCell = 11
#Create spreadsheet
$wb = $xl.workbooks.open("C:\Temp\test.xls")
# rename the Worksheet. 30 charactor limit
$ws1 = $wb.worksheets | where {$_.name -eq "xml_source"} #<-------
activate()s sheet 1
$ws2 = $wb.worksheets | where {$_.name -eq "Report"} #<-------
activate()s sheet 2
$ws3 = $wb.worksheets | where {$_.name -eq "sheet3"} #<-------
activate()s sheet 3
# Activate sheet 1
[void]$ws1.activate()
#Start Test

$mainRng = $ws1.UsedRange.Cells
$RowCount = $mainRng.Rows.Count
$R = $RowCount
$R = $R + 1
$yRange = ($zRow,1)
$yRange.Select

For ($i = $zRow; $i -lt $R; $i++) {
Write-Host $i $R
If ($ws1.Cell.Value($i, 4) -eq "FALSE") {
$range4=$ws1.range("${i}:$i")
$range4.Copy()
$ws2.Activate
$ws2.Cells.Item($i,1).PasteSpecial(-4163)
}
}

OldDog wrote:

>>> ERROR: You cannot call a method on a null-valued expression.
>>> ERROR: At line:29 char:23
>>> ERROR: + If ($ws1.Cell.Value(<<<< $i, 4) = "FALSE") {

>>

What does this do? What documentation did you see it in?

If ($ws1.Cell.Value($i, 4) -eq "FALSE") {

Something seems odd about indexing a value and not indexing a cell
and then asking for that value.

Perhaps this article could be helpful
http://www.wapshere.com/missmiis/pow...ata-from-excel

- Larry

On May 11, 1:59*pm, Larry__Weiss <l...@newsgroup> wrote:

> OldDog wrote:
>
> *>>> ERROR: You cannot call a method on a null-valued expression.
> *>>> ERROR: At line:29 char:23
> *>>> ERROR: + * *If ($ws1.Cell.Value(<<<< * *$i, 4) = "FALSE") {
> *>>
>
> What does this do? * What documentation did you see it in?
>
> * * *If ($ws1.Cell.Value($i, 4) -eq "FALSE") {
>
> Something seems odd about indexing a value and not indexing a cell
> and then asking for that value.
>
> Perhaps this article could be helpfulhttp://www.wapshere.com/missmiis/powershell-retrieving-data-from-excel
>
> * - Larry

This works:

If ($ws1.Cells.Item($i,4).Text -eq "FALSE") {

Thanks for the help

"OldDog" <mikef2691@newsgroup> wrote in message
news:b45f069a-fc92-4bef-aa94-ec492367c086@newsgroup

> The problem is, I get this error:
>
> ERROR: You cannot call a method on a null-valued expression.
> ERROR: At line:29 char:23
> ERROR: + If ($ws1.Cell.Value( <<<< $i, 4) = "FALSE") {
>
> I am thinking that it does not like the word "FALSE" but i might be
> overthinking.
>
> Any ideas?

What does the 4 mean? Also, have you tried single quotes instead of double?

E.g. maybe it's being evaluated and substituted unexpectedly?

PS C:\> dir variable:false

Name Value
---- -----
false False


Good luck

Robert
---