CODEWITHSUNDEEP
powershell
ThisGuyUsesPowershell
ThisGuyUsesPowershell

Reputation: 61

Powershell script errors first time but works on second attempt

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

Upvotes: 1

Views: 510

Answers (1)

Theo
Theo

Reputation: 61028

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

Upvotes: 4

Related Questions