Powershell script errors first time but works on second attempt

Question

I have a powershell script that searches for xls files and converts them to xlsx. Every time I run it for the first time I will get the error

Unable to find type [Microsoft.Office.Interop.Excel.XlFileFormat].
At C:\Users\wpauling\Documents\Quarter Report Scripts\convert.ps1:2 char:18
+ ... xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpen ...
+                     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (Microsoft.Offic...el.XlFileFormat:TypeName) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

However the second time I run it everything works as expected, what is the problem I am going nuts. Here is my script

#Converts xls into xlsx
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
write-host $xlFixedFormat
$excel = New-Object -ComObject excel.application
$excel.visible = $false
$folderpath = "C:\Users\user1\Documents\Q4"
$filetype ="*xls"
Get-ChildItem -Path $folderpath -Include $filetype -recurse | 
ForEach-Object `
{
    $path = ($_.fullname).substring(0, ($_.FullName).lastindexOf("."))

    "Converting $path"
    $workbook = $excel.workbooks.open($_.fullname)

    $path += ".xlsx"
    $workbook.saveas($path, $xlFixedFormat)
    $workbook.close()

    remove-item $_.fullname

}
$excel.Quit()
$excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()

Theo · Accepted Answer · 2020-01-17 21:48:50Z

4

Start the script with the line

Add-Type -AssemblyName Microsoft.Office.Interop.Excel

Hope that helps.

p.s. Much less descriptive of course, but you can also use the numeric enum value 51 for the variable and not use the Add-Type.

See: XlFileFormat enumeration

edited Jan 17, 2020 at 21:48

answered Jan 17, 2020 at 21:32

Theo

61.9k8 gold badges28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powershell script errors first time but works on second attempt

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related