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

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

CodePudding user response：

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

CodePudding user response：

You need to actually return the value from the function.

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