none
cannot call a method on a null-valued expression RRS feed

  • Question

  • 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:34 char:1
    + $ExcelWorkSheet.Cells.Item(2,4).Value2 = "HN"
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At line:35 char:1
    + $ExcelWorkSheet.Cells.Item(2,5).Value2 = "SN"
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + 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

    Wednesday, October 29, 2014 1:49 PM

Answers

  • You use $ExcelWorkSheet as a variable but it seems to come from nowhere, does this work - 

    # 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
    $Excel.Cells.Item(2,4).Value2 = '=RIGHT(A2,LEN(A2)-FIND("K",A2)-1)'
    $Excel.Cells.Item(2,5).Value2 = '=LEFT(B2,LEN(B2)-FIND("v",B2)-3)'

    • Marked as answer by rmphoenix Wednesday, October 29, 2014 2:18 PM
    Wednesday, October 29, 2014 2:02 PM

All replies

  • Sorry, this is the correct 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

    Wednesday, October 29, 2014 1:59 PM
  • You use $ExcelWorkSheet as a variable but it seems to come from nowhere, does this work - 

    # 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
    $Excel.Cells.Item(2,4).Value2 = '=RIGHT(A2,LEN(A2)-FIND("K",A2)-1)'
    $Excel.Cells.Item(2,5).Value2 = '=LEFT(B2,LEN(B2)-FIND("v",B2)-3)'

    • Marked as answer by rmphoenix Wednesday, October 29, 2014 2:18 PM
    Wednesday, October 29, 2014 2:02 PM
  • Yes! Amazing, thankyou.  I see my mistake as well, always learning.

    Wednesday, October 29, 2014 2:18 PM