Read Excel file fail

[Twenty94470]

Version

5.0.8

Severity

Quality-of-life

Environment

msi

Steps to Reproduce

Hello,

I've created a simple excel file, A1 = Test When I run the script into the ise program I can see the cell content "Test" when I run the same script into Powershell Universal, I got an error : **"Microsoft Excel cannot access the file ' C:\Sources\Classeur1.xlsx'

• The file name or path does not exist
• The file is being used by another program
• The workbook you are trying to same has the same name as a currently open workbook You cannot call a method on a null-valued expression. Unable to find type [Microsoft.Office.Interop.Excel.XlSearchOrder]."**

There is no excel process in background, I've checked the excel file is not already opened.

# Script contents
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

$workbook = $excel.Workbooks.Open("C:\Sources\Classeur1.xlsx")
$sheet = $workbook.Sheets.Item(1)
$lastRow = $sheet.Cells.Find("*", [System.Reflection.Missing]::Value,
                                [System.Reflection.Missing]::Value,
                                [System.Reflection.Missing]::Value,
                                [Microsoft.Office.Interop.Excel.XlSearchOrder]::xlByRows,
                                [Microsoft.Office.Interop.Excel.XlSearchDirection]::xlPrevious,
                                $false, [System.Reflection.Missing]::Value,
                                [System.Reflection.Missing]::Value).Row

for ($row = 1; $row -le $lastRow; $row++) {
$sheet.Cells.Item($row, 1).Text
}

Expected behavior

Read excel file line by line

Actual behavior

error during openning excel file

Additional Environment data

Office 2021 LTSC

Screenshots/Animations

[rstolpe]

Have you made sure that PSU service account have permission to the file? Also made sure to unblock it if it's blocked?

Unblock-File -Path "path to excel file"

I'm pretty sure it's unblock-file, im on my phone now. But it's a PowerShell function so just google it if it won't work.

[Twenty94470]

Hello thanks for your reply,

PSU is installed with system account, so yes, on the security tab, the system account as full access, The file was created on local, so there is not "Unsecure" like if I copy paste the file from network share,

I try your command with verbose, but I still have the error on the excel file :

I check on google but all solution didn't works.

I don't know if on another environnement, you have the same result ?

Regards

[rstolpe]

Hello thanks for your reply,

PSU is installed with system account, so yes, on the security tab, the system account as full access,

The file was created on local, so there is not "Unsecure" like if I copy paste the file from network share,

I try your command with verbose, but I still have the error on the excel file :

I check on google but all solution didn't works.

I don't know if on another environnement, you have the same result ?

Regards

I have not tried it, the things I did write above was how I did solve it with word files. Unblock-file did solve it for me.

Or wait, I needed to call on a script from the endpoint. So I did never figure this out. But did a work a round.

Create a script file with your code then execute ps with that file from psu.

[Twenty94470]

Hello,

I got the same error, but I fix my error by using the import-excel module. https://github.com/dfinke/ImportExcel I let the issue opened if the COM object can be fixed.

Regards

[adamdriscoll]

COM is going to be very problematic in a service like this. I would highly recommend just using the ImportExcel module to avoid it.