How to declare a variable in Powershell based on a function in order to pass it as an argument in another function?

Question

I am trying to declare $workbook1 as the current workbook in order to pass it as an argument in another function, but it is not working. Here is my code:

Here is my code:

Function Open-Workbook($path, $file, $excel) {
    try {
        $wb = $excel.Workbooks.Open("${path}${file}")
    }catch{
        echo $error
        $key = 'Open-Workbook';
        $date = Get-Date -Format 'yyyy-MM-dd';
        Log-Error $key $date;
        $error.Clear()
    }
}

Function xlRefresh($wb){
    try{
    $wb.RefreshAll()
    }catch{
         echo $error
         $key = 'Run-xlRefresh';
         $date = Get-Date -Format 'yyyy-MM-dd';
         Log-Error $key $date;
         $error.Clear()
    }
}

$paths = "C:\Users\"
$files = "Detect Scripts.xlsm" 

try{
    $setexcel = New-Object -ComObject Excel.Application
    $setexcel.Visible = $true
    }catch{
        echo $error
        $key = 'Open-Excel';
        $date = Get-Date -Format 'yyyy-MM-dd';
        Log-Error $key $date;
        $error.Clear()
}


$workbook1 = Open-Workbook $paths $files $setexcel
xlRefresh $workbook1

When I run this, I get this error:

You cannot call a method on a null-valued expression.

Thank you

Doug Maurer · Answer 1 · 2022-04-05T22:01:27.240

You didn't actually output the workbook from your first function, therefore you catch nothing in your variable. Just write it out at the end.

Function Open-Workbook($path, $file, $excel) {
    try {
        $wb = $excel.Workbooks.Open("${path}${file}")
    }catch{
        echo $error
        $key = 'Open-Workbook';
        $date = Get-Date -Format 'yyyy-MM-dd';
        Log-Error $key $date;
        $error.Clear()
    }
    $wb
}

Or as zett42 commented, you can simply not capture it and write it implicitly

Function Open-Workbook($path, $file, $excel) {
    try {
        $excel.Workbooks.Open("${path}${file}")
    }catch{
        echo $error
        $key = 'Open-Workbook';
        $date = Get-Date -Format 'yyyy-MM-dd';
        Log-Error $key $date;
        $error.Clear()
    }
}

... or just omit `$wb =` altogether and let `$excel.Workbooks.Open()` pass its return value through as output of the `Open-Workbook` function. — zett42, Apr 05 '22 at 20:58

score 0 · Answer 2 · answered Apr 05 '22 at 20:20

0

You need to actually return the value from the function.

e.g. return $excel.Workbooks.Open("${path}${file}")

answered Apr 05 '22 at 20:20

Eric

11
2

While this surely works, note that in PowerShell you don't need the `return` statement in most cases. Just writing out `$excel.Workbooks.Open("${path}${file}")` on its own line would be sufficient. See https://stackoverflow.com/q/10286164/7571258 – zett42 Apr 05 '22 at 20:56

How to declare a variable in Powershell based on a function in order to pass it as an argument in another function?

2 Answers2