cannot call a method on a null-valued expression

PowerShell Help
1

I’m running a Powershell script to take some data from a csv file and enter it into an Excel spreadsheet. I am then running a small script inside excel to alter that data. I want to put what I’m executing in Excel into the original Powershell script so it is all one process but am getting the error: You cannot call a method on a null-valued expression.

This is the Powershell script:

Get info from CSV and put into csv

Select-String -Path c:*-Live -AllMatches | Export-CSV C:\xxxx.csv
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Excel.DisplayAlerts = $false
#build spread sheet and import data
$Excel.Workbooks.Open(“C:\xxxx.csv”)
$Excel.Worksheets.Item(1).name=“Inventory”
$Excel.Worksheets.Item(“Inventory”).activate()
$Excel.Cells.Item(1,3) = “Serial Number”
$Excel.Cells.Item(1,4) = “Hostname”
$Excel.Cells.Item(1,6) = “Model”

#delete columns
[void]$Excel.Cells.Item(1,1).EntireColumn.Delete()
[void]$Excel.Cells.Item(1,1).EntireColumn.Delete()
[void]$Excel.Cells.Item(1,6).EntireColumn.Delete()
[void]$Excel.Cells.Item(1,5).EntireColumn.Delete()
[void]$Excel.Cells.Item(1,3).EntireColumn.Delete()
[void]$Excel.Cells.Item(2,1).EntireRow.Delete()
#bold and font 16
$Excel.Cells.Item(1,1).Font.Bold=$True
$Excel.Cells.Item(1,2).Font.Bold=$True
$Excel.Cells.Item(1,3).Font.Bold=$True
$Excel.Cells.Item(1,1).Font.size=16
$Excel.Cells.Item(1,2).Font.size=16
$Excel.Cells.Item(1,3).Font.size=16
$Excel.WorkSheets.item(“Inventory”).UsedRange.Columns.Autofit() | Out-Null
[void]$Excel.Cells.Item(2,1).EntireRow.Delete()

#Excel Formula
$ExcelWorkSheet.Cells.Item(2,4).Value2 = ‘=RIGHT(A2,LEN(A2)-FIND(“K”,A2)-1)’
$ExcelWorkSheet.Cells.Item(2,5).Value2 = ‘=LEFT(B2,LEN(B2)-FIND(“v”,B2)-3)’

and the error report:

You cannot call a method on a null-valued expression.
At line:32 char:1

  • $ExcelWorkSheet.Cells.Item(2,4).Value2 = ‘=RIGHT(A2,LEN(A2)-FIND(“K”,A2)-1)’
  •   + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
  + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At line:33 char:1

  • $ExcelWorkSheet.Cells.Item(2,5).Value2 = ‘=LEFT(B2,LEN(B2)-FIND(“v”,B2)-3)’
  •   + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
  + FullyQualifiedErrorId : InvokeMethodOnNull

I am very new to both Powershell and scripting/programming so please explain this to me as if I was your grandma!

Any help much appreciated!

Rob

2

Doesn’t look like you’ve assigned anything to the $ExcelWorkSheet variable before you start trying to access properties and methods on it.

3

Ah, I’ve changed it and it works, thankyou!