cannot call a method on a null-valued expression

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Rob Phoenix Rob Phoenix 2 years, 1 month ago.

  • Author
    Posts
  • #20131
    Profile photo of Rob Phoenix
    Rob Phoenix
    Participant

    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

  • #20132
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

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

  • #20136
    Profile photo of Rob Phoenix
    Rob Phoenix
    Participant

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

You must be logged in to reply to this topic.