Finding the word False in an Excel Worksheet

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
0
OldDog
5/11/2010 3:34:41 PM
windows.powershell 690 articles. 0 followers. Follow

6 Replies
1431 Views

Similar Articles

[PageSpeed] 36

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
0
Larry__Weiss
5/11/2010 3:40:00 PM
On May 11, 10:40=A0am, Larry__Weiss <l...@airmail.net> wrote:
> Try coding it as
>
> =A0 =A0If ($ws1.Cell.Value($i, 4) -eq "FALSE") {
>
> =A0 - 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 =3D New-Object -comobject Excel.Application
> > $xl.Visible =3D $true
> > $xl.DisplayAlerts =3D $False
> > $xlCellTypeLastCell =3D 11
> > #Open spreadsheet
> > $wb =3D $xl.workbooks.open("C:\Temp\test.xls")
> > # rename the Worksheet. 30 charactor limit
> > $ws1 =3D $wb.worksheets | where {$_.name -eq "xml_source"}
> > $ws2 =3D $wb.worksheets | where {$_.name -eq "Report"}
> > $ws3 =3D $wb.worksheets | where {$_.name -eq "sheet3"}
> > # Activate sheet 1
> > [void]$ws1.activate()
> > #Start Test
> > $i =3D 2
> > For ($i =3D $zRow; $i -eq $R; $i++) {
> > =A0 =A0 If ($ws1.Cell.Value($i, 4) =3D "FALSE") {
> > =A0 =A0$range4=3D$ws1.range("${i}:$i")
> > =A0 =A0$range4.Copy()
> > =A0 =A0$ws2.Activate
> > =A0 =A0$ws2.Cells.Item($i,1).PasteSpecial(-4163)
> > =A0 =A0}
> > =A0 }
>
> > 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: + =A0 =A0If ($ws1.Cell.Value(<<<< =A0$i, 4) =3D "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 -

Thanks, same error:

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

*** PowerShell Script finished. ***

0
OldDog
5/11/2010 5:39:03 PM
On May 11, 10:40=A0am, Larry__Weiss <l...@airmail.net> wrote:
> Try coding it as
>
> =A0 =A0If ($ws1.Cell.Value($i, 4) -eq "FALSE") {
>
> =A0 - 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 =3D New-Object -comobject Excel.Application
> > $xl.Visible =3D $true
> > $xl.DisplayAlerts =3D $False
> > $xlCellTypeLastCell =3D 11
> > #Open spreadsheet
> > $wb =3D $xl.workbooks.open("C:\Temp\test.xls")
> > # rename the Worksheet. 30 charactor limit
> > $ws1 =3D $wb.worksheets | where {$_.name -eq "xml_source"}
> > $ws2 =3D $wb.worksheets | where {$_.name -eq "Report"}
> > $ws3 =3D $wb.worksheets | where {$_.name -eq "sheet3"}
> > # Activate sheet 1
> > [void]$ws1.activate()
> > #Start Test
> > $i =3D 2
> > For ($i =3D $zRow; $i -eq $R; $i++) {
> > =A0 =A0 If ($ws1.Cell.Value($i, 4) =3D "FALSE") {
> > =A0 =A0$range4=3D$ws1.range("${i}:$i")
> > =A0 =A0$range4.Copy()
> > =A0 =A0$ws2.Activate
> > =A0 =A0$ws2.Cells.Item($i,1).PasteSpecial(-4163)
> > =A0 =A0}
> > =A0 }
>
> > 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: + =A0 =A0If ($ws1.Cell.Value(<<<< =A0$i, 4) =3D "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 =3D 2
$xRow =3D 2
$zRow =3D 2
$xl =3D New-Object -comobject Excel.Application
$xl.Visible =3D $true
$xl.DisplayAlerts =3D $False
$xlCellTypeLastCell =3D 11
#Create spreadsheet
$wb =3D $xl.workbooks.open("C:\Temp\test.xls")
# rename the Worksheet. 30 charactor limit
$ws1 =3D $wb.worksheets | where {$_.name -eq "xml_source"} #<-------
activate()s sheet 1
$ws2 =3D $wb.worksheets | where {$_.name -eq "Report"} #<-------
activate()s sheet 2
$ws3 =3D $wb.worksheets | where {$_.name -eq "sheet3"} #<-------
activate()s sheet 3
# Activate sheet 1
[void]$ws1.activate()
#Start Test

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

For ($i =3D $zRow; $i -lt $R; $i++) {
   Write-Host $i $R
    If ($ws1.Cell.Value($i, 4) -eq "FALSE") {
	$range4=3D$ws1.range("${i}:$i")
	$range4.Copy()
	$ws2.Activate
	$ws2.Cells.Item($i,1).PasteSpecial(-4163)
 	}
 }
0
OldDog
5/11/2010 5:50:04 PM
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/powershell-retrieving-data-from-excel

  - Larry


0
Larry__Weiss
5/11/2010 6:59:08 PM
On May 11, 1:59=A0pm, Larry__Weiss <l...@airmail.net> wrote:
> OldDog wrote:
>
> =A0>>> ERROR: You cannot call a method on a null-valued expression.
> =A0>>> ERROR: At line:29 char:23
> =A0>>> ERROR: + =A0 =A0If ($ws1.Cell.Value(<<<< =A0 =A0$i, 4) =3D "FALSE"=
) {
> =A0>>
>
> What does this do? =A0 What documentation did you see it in?
>
> =A0 =A0 =A0If ($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/pow=
ershell-retrieving-data-from-excel
>
> =A0 - Larry

This works:

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

Thanks for the help
0
OldDog
5/11/2010 8:21:12 PM

"OldDog" <mikef2691@comcast.net> wrote in message 
news:b45f069a-fc92-4bef-aa94-ec492367c086@a21g2000yqn.googlegroups.com...

> 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
---  

0
Robert
5/11/2010 9:13:31 PM
Reply:

Similar Artilces:

Excel, date
I have on cell that has an original manfg. date 1/1/90 (this date will change) the next hydro date is either 3 or 5 years depending on the date. prior to 1/1/91 cylinder must be done every three years and after 1/1/91 they must be done every 5 years. I am stuck on the formula asd I have it now =IF($D$3<B16,1095+D3,1825+D3) any assitance would be of great help. Thnaks Using named cells: Manfg_Date: [any date] Ref_Date: 1-Jan-91 Interval: =IF(Manfg_Date<Ref_Date,3,5) Last_Hydro: [any date] Next_Hydro: =Date(Year(Last_Hydro)+Interval, Month(Last_Hydro), D...

Can't Find a Public Folder Address
I have a number of public folder redirects in the Exchange 2003 network that I recently inherited. There is one public folder redirect name in the Address Book named "This Folder", there is yet another public folder redirect named "ThisFolder", and there is an actual public folder named "ThisFolder". There is also a functional team at work named "ThisFolder". The problem is most people who intend to send mail to the "ThisFolder" team - email address thisfolder@domain.com - select the wrong object from the Address Book and send mail to the &q...

Excel Footers #3
Can anyone tell me how to add more than 255 characters to an Excel Footer please? Hi AFAIK you can't -- Regards Frank Kabel Frankfurt, Germany "Anne" <Anne@discussions.microsoft.com> schrieb im Newsbeitrag news:9D0D1493-F79F-4EA6-8A0C-F6904FD11EA4@microsoft.com... > Can anyone tell me how to add more than 255 characters to an Excel Footer > please? ...

Reset Bottom of an Excel spreadhseet
Is there a way to reset the end of a spreadsheet to the last cell that has information in it? When I press End Home, I am landing on row 6,448 even though I selected rows 500 - 6448 and hit delete. Also, another basic question. Sometimes a range of cells will increment to the next value and sometimes they won't. Is this just due to formatting differences in the cell? Thanks. Ken K. - 2191 -- akkrug Visit Debra's site at http://www.contextures.com/ She tells how to do just that best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "akkru...

Partially hidden Excel formula bar
In Mac OS 10.5, Excel 2008 won't show a complete formula bar. The left side is cut off. I've tried adjusting my display settings, deleting plist files, and uninstalling/reinstalling Office 2008. IF I log in as another user, I get a clear view of the whole formula bar. Any help with this will be greatly appreciated. Thx. In article <65B2FAAD-8DD3-4D0C-8AF6-DC5EF62C71E6@microsoft.com>, Eqbal00 <Eqbal00@discussions.microsoft.com> wrote: > In Mac OS 10.5, Excel 2008 won't show a complete formula bar. The left side > is cut off. I've tried ad...

How do I get both horizontal and vertical frozen panes in Excell
How does one get both horizontal and vertical frozen panes in Excell spreadsheets? Tried split as described by Microsoft and it does not operate as desired. Am looking to have the first row be 'frozen' at the top when scrolling down and the first 3 columns 'frozen' at the left when scrolling across. You should be able to select cell D2 and select freeze pane and have it do what you want. "CraigScherer" wrote: > How does one get both horizontal and vertical frozen panes in Excell > spreadsheets? Tried split as described by Microsoft and it does not oper...

PowerPoint 2007 to Word 2007
Can anyone tell me why when I want to publish a PowerPoint presentation to Word why a box pops up that states; "PowerPoint couldn't write to Microsoft Office Word" Please email me at carljazz@ymail.com Thank you! I was having the same problem. The clipboard utility I was using, ClipX, was interfering. "carljazz" wrote: > Can anyone tell me why when I want to publish a PowerPoint presentation to > Word why a box pops up that states; > > "PowerPoint couldn't write to Microsoft Office Word" > > Please email me ...

How do I create a Gantt Chart in Excel 2003?
Greetings. I vaguely remember being in a training class that showed how to make a Gantt Chart in Excel. Alas, I've lost those notes eons ago. Does anyone know how to create a Gantt chart in Excel? Hi, A good place to start looking from. http://peltiertech.com/Excel/Charts/GanttLinks.html Cheers Andy Bewildered KG wrote: > Greetings. > I vaguely remember being in a training class that showed how to make a Gantt > Chart in Excel. Alas, I've lost those notes eons ago. Does anyone know how > to create a Gantt chart in Excel? -- Andy Pope, Microsoft MVP - Excel ht...

Find window
hey people, can anybody tell me why in the find screen some information is red and other black Hi Shoby. The information in red contains the characters you ran the find on. Kerri "Shoby" wrote: > hey people, > can anybody tell me why in the find screen some information is red and > other black ...

Making Rectangles Visible when Publishing in MS Word
Hi I am new to reports and have what is probably a very basic problem. I have set up my text boxes with their relevant control sources and framed each text box and label using the rectangle tool with a thickness of 2 point. When I run the report the frames appear and the same if I create a pdf but if I use the Publish with MS Word option (Tools>> Office Links) then the rectangles disappear. Does anyone know how to solve this? The Visible property is set to 'Yes' and I am using MS Access 2003. Many thanks David The export to Word is in RTF (Rich Text Format), and it lo...

Excel 4 macro to VBA translation
Where can I find a good reference source where I can lookup and translate Excel 4 macro codes to their VBA equivalence? Thanks for any help. Tom Tom, My .hlp version of the following has a section titled... "Visual Basic Equivalents for Macro Functions and Commands " I assume the .chm download version here will have the same... http://makeashorterlink.com/?K68332CBB Excel 2000 Help File: Running Excel 4.0 Macros Jim Cone San Francisco, USA "Tom" <tclim36@zip.com.au> wrote in message news:BFLSe.28299$Le2.254141@nasal.pacific.net.au... Where can I find a g...

How do I automatically show all comments on a seperate worksheet?
How do I automatically show all comments on a seperate worksheet? I can select the option to print the comments out on seperate worksheet or "at the end" they call it. But can I put them, on a sererate worksheet for me to see and review in a list. They offer to review each comment one at a time and shuffle through them, but that takes a while and its nice to see a suammry of all of them. You can use programming to copy comments to a separate sheet, where you could print them. There's sample code here: http://www.contextures.com/xlcomments03.html newmember wrote: >...

MS Office 4.3 Pro with Excel 5.0c
Hi All, This is probably a strange question Is there ANY possibility that I can still download this software anywhere. I've an old (but very good) 50000 lines Macro 4 program causing my Excel 2000 to crash. My old disks aren't trustworthy anymore. The main thing is the Excel 5.0c but the office 4.3 pro should be better. Indeed I'm an antiquity :):):) Nevertheless for me important. Hoping in a solution for which I'm grateful. Kind regards Eric Does Microsoft store such archive things on the net in FTP directories or something? I tried ebay.com and searched for "mic...

Openeing Excel File in New Window
I am having a Form in my Excel sheet which runs all the time. whenever I try to open a new Excel sheet. that Forms popup and restricts Excel file from openeing si I have to open a new window and then open that file in new window. Please help with HOW TO OPEN THE EXCEL FIEL IN A NEW WINDOW EVERY TIME. Do you mean a new instance of excel--running excel 2 times--not just having another window in the same instance? If yes, try: tools|options|general|Check the "ignore other applications" box Then open the second file by either double clicking it in windows explorer or by opening t...

Pivot tables Excel vs Access
I am trying to create a pivot table in Excel to assign exam grades in particular subjects to pupils. If I do this in Excel all the grades are changed to the value 1. If I do it in Access the grades are maintained - does anyone have any idea why this should be handled differently in these two office programs? At present I have a table in excel which I have to export to access to create the pivot table and then copy it back. Surely I shouldnt need to do this every time? Help!!!!!!!! If you format the grades as Percent, do they display correctly? Mike Eyre wrote: > I am trying to c...

subtotal by page in excel
how can you subtotal at the end of each page in excel? Nothing in common in order to utilize the subtotal menu option. Can you elaborate on your situation. Do you need subtotals within a page or just a total at the end of each page? Are you linking formulas from one sheet to other sheets to capture "subtotals"? "michelle scott" <anonymous@discussions.microsoft.com> wrote in message news:c97401c48a09$71e3f4c0$a601280a@phx.gbl... > how can you subtotal at the end of each page in excel? > Nothing in common in order to utilize the subtotal menu > option. ...

creating WMF files in Excel
I have a picture in my worksheet and want to save it as WMF file. Is it possible to do it ? Any chance you could copy and paste into your favorite graphics editor and then save from there? GeorgeM wrote: > > I have a picture in my worksheet and want to save it as WMF file. > Is it possible to do it ? -- Dave Peterson ec35720@msn.com ...

Outlook 2002--cannot find contact in Contacts
This has happened time to time, where I seem to "lose" a contact. Any info on how to get it back?? Why does it do this? ...

How to paste Xcel tables legibly into Word . Help!!
Hallo! I have been trying for the whole day to put an Excel-Sheet containing different tables ( each one of different breadth) into Word. Unfortunately I didn t succeed in creating a Word document, which shows all the Excel tables in a legible way. Most of the tables that are wider than a normal Word document are being cut off . I even did not succeed in changing the size of the tables in Word, so that I cannot see the part that was cut off after being transformt into Word. It would be great if someone could help me!!! Thanks Nimo from Frankfurt (Germany) You can co...

Excel 2002 Security Issue
When a user opens an Excel file that has macros, and if there security is set to medium or high, they get that dialog box that asks them whether or not to enable macros. With macro security set to Medium in Excle 2002, is there a way to disable the request. I have tried checking both check boxes in Trusted Sources, to no avail. Thanks for any guidance. The trusted sources approach will work, but only if the macros have a digital signature (indicating a source whom you can trust). The cheap way to create those signatures is for the macro author to find and run the selfcert.exe file...

Copy &amp; Paste table from Excel to Word
Folks, When I copy and paste an Excel table into Word the formatting goe awry. Yet this happens only occasionally. It's obviously something I' doing/not doing! I would be grateful for any assistance in this matter. Thanks very much Johnn -- Johnny37 Hi Johnny If formatting is a problem, one way to handle it is to copy/paste as picture rather than table. 1. select the range of cells in Excel that you want to copy. 2. Whle holding down the SHIFT key, pull-down: Edit > Copy Picture and do the copy 3. go to your word document and paste Since the object is a picture rather...

Excel 2007 Password Recovery
Hi, im in a fix as an employee has placed a password on open on one of my documents, she does not remember what the password is, can you help me to figure out how to recover this password. I have tried numerous recovery tools and still no luck. This document contains vital information and starting a fresh is not a option. Thanks Hi, You can buy a password cracking utility on the web, google Excel Password Cracker. This should tell you that passwords are not total security. -- Thanks, Shane Devenshire "OS360_ITMAN" wrote: > Hi, im in a fix as an employee has placed a...

Find a link to an external file
Hello, I'm working on a excel file constructed bij a person that doesn't work here any more. The problem with this excelfile is that there is a link to an external file wich excel can't find. I already tried to search for the filename he asks for in the equations and cels. But it doesnt result in any hits. How can I find this link so I can remove/change this cel. Does anyone has an idea? Tadashii. -- Tadashii ------------------------------------------------------------------------ Tadashii's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=239...

Pdf File To Excel #2
How To Convert Pdf File Into Excel File. I Am Having Pdf File Which Are Created From Graphs & Want To Covert I Excel Pls Suggest Me Any Link Or Addins Thanks In Advance -- NITESH ----------------------------------------------------------------------- NITESH G's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1759 View this thread: http://www.excelforum.com/showthread.php?threadid=49602 You need to have one of the following, Adobe Acrobat Exchange (5.0) Acrobat Professional (6.0) or Acrobat 7.0. There are some other vendor who make programs which will...

Code for emailing Excel workbooks will only attach activeworkbook
Hi, Ok so when a non-programmer tries to take code and "tweak it" for a different use...here's what happens....it doesn't work. Can anyone help? What I've got is an Excel file I'm using as a template. I run a macro (not the one below) to create a report for Zone 1. I save the file with a name like "December report - Zone 1". Then I run the macro for Zone 2 and save the file with a name like "December report - Zone 2" and so on for many zones. Within the Excel file there is a sheet called Email. On the Email sheet, starting wit...