convert .xls files in a folder to .xlsx file

i getting file client extension .xls can't read in ssis or openrowset function. believe file has .xls extension not excel file. can open file manually, looking powershell script open , save file .xlsx.

i tried use following code not working. on appreciated.

#################################

     $xlfixedformat = [microsoft.office.interop.excel.xlfileformat]::xlworkbookdefault

·          

·         $excel = new-object -comobject excel.application

·         $excel.visible = $true

·         $folderpath = "c:\test\*"

·         $filetype ="*xls"

·         get-childitem -path $folderpath -include $filetype |

·         foreach-object `

·         {

·         $path = ($_.fullname).substring(0,($_.fullname).lastindexof("."))

·         "converting $path $filetype..."

·         $workbook = $excel.workbooks.open($_.fullname)

·          

·         $workbook.saveas($path, $xlfixedformat)

·         $workbook.close()

·         }

·         $excel.quit()

·         $excel = $null

·         [gc]::collect()

·         [gc]::waitforpendingfinalizers()

#########################################################

---

mohammedu

remove-comobject function wrote clean leftover com objects. recommend use instead of methods used in initial post because not clean well. use u need define in current powershell session; you’re welcome here.

anyway, remove line calls remove-comobject , run code again.

note when converting file code in initial post, converted xlsx file new file xlsx extension, if problem file badfile.xls, converted file badfile.xlsx.

---

  robert robelo  

Windows Server  >  Windows PowerShell