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

Answer 1

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()
    }
}

Answer 2

You need to actually return the value from the function.

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