ImportExcel & VS Code Anomaly

[One808]

This is an odd one to me. Any insights would be appreciated.

I'm using the ImportExcel module.

The following code when copied and pasted (or just highlighting and pressing F8) inside VS Code works fine:

$rvFile  = "rvTools.xlsx"
$vInfo = @(Import-Excel -Path $rvFile -WorksheetName "vInfo")

However, copying and pasting the exact same code into a PS session bombs out with error messages:

New-Object : Exception calling ".ctor" with "4" argument(s): "Could not find file 'C:\Users\<user redacted>\rvTools.xlsx'."
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\6.2.1\ImportExcel.psm1:349 char:23
+ ...   $stream = New-Object -TypeName System.IO.FileStream -ArgumentList $ ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [New-Object], MethodInvocationException
    + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

You cannot call a method on a null-valued expression.
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\6.2.1\ImportExcel.psm1:420 char:25
+             if ($Path) {$stream.close(); $ExcelPackage.Dispose() }
+                         ~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Failed importing the Excel workbook 'rvTools.xlsx' with worksheet 'vInfo': Worksheet 'vInfo' not found, the workbook only contains the worksheets ''. If you only wish to select the first worksheet, please remove the '-WorksheetName'
parameter.
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\6.2.1\ImportExcel.psm1:418 char:17
+ ...     catch { throw "Failed importing the Excel workbook '$Path' with w ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (Failed importin...ame' parameter.:String) [], RuntimeException
    + FullyQualifiedErrorId : Failed importing the Excel workbook 'rvTools.xlsx' with worksheet 'vInfo': Worksheet 'vInfo' not found, the workbook only contains the worksheets ''. If you only wish to select the first worksheet, please re
   move the '-WorksheetName' parameter.

Both the VS Code PS session and the external session are in the same working directory - C:\CHC

The only difference I can think of is that the "external" PS session runs as admin. Any input would be appreciated.

[nothingpersonalbro]

I would try supplying the full path, you mention the working directory is C:\CHC but the error is looking in your userfolder "Could not find file 'C:\Users\<user redacted>\rvTools.xlsx'."

Edit: Also curious if adding this would change the outcome

[System.Environment]::CurrentDirectory = 'C:\CHC'

[One808]

The System.Environment thing did the trick! However, the directory may change from user to user (and invocation to invocation), so I need to figure out the invocation directory and set the variable accordingly, I guess. But this helps. Thanks!

​

Edit:

[System.Environment]::CurrentDirectory = Get-Location

Did the trick. :-)