# cannot call a method on a null-valued expression

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

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

Ah, I’ve changed it and it works, thankyou!
{"ft_lang_label":"__label__en","ft_lang_prob":0.5532916,"math_prob":0.8365634,"size":2396,"snap":"2021-21-2021-25","text_gpt3_token_len":733,"char_repetition_ratio":0.19732441,"word_repetition_ratio":0.15283842,"special_character_ratio":0.2854758,"punctuation_ratio":0.26530612,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.98305076,"pos_list":[0,1,2],"im_url_duplicate_count":[null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2021-05-11T22:50:55Z\",\"WARC-Record-ID\":\"<urn:uuid:f15ad54b-04d0-43b4-9d17-822e3ee1ab8c>\",\"Content-Length\":\"18866\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:0805a1d8-222b-4434-bbfa-a980a7ca5389>\",\"WARC-Concurrent-To\":\"<urn:uuid:7a0e41b1-246d-48d8-b93d-48e1d2ff4f7f>\",\"WARC-IP-Address\":\"72.52.80.20\",\"WARC-Target-URI\":\"https://forums.powershell.org/t/cannot-call-a-method-on-a-null-valued-expression/3333\",\"WARC-Payload-Digest\":\"sha1:ANPGORBM7OZGCD6KTZ3SX4MWOOEGZR6T\",\"WARC-Block-Digest\":\"sha1:FCZHNHJQSHL4U4Q2AKATBG4KVWQ757NI\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2021/CC-MAIN-2021-21/CC-MAIN-2021-21_segments_1620243990419.12_warc_CC-MAIN-20210511214444-20210512004444-00551.warc.gz\"}"}