How to convert Excel xlsx file to xls file in batch by PowerShell

0

I am having multiple .xlsx file genrated from SAP BO4.2 But user reads .xls only so wanted to write some script which will convert .xlsx to .xls

Referred- https://gallery.technet.microsoft.com/How-to-convert-Excel-xlsx-d9521619 and tried to use same for .xls

$ErrorActionPreference = 'Stop'

Function Convert-xlsInBatch
{
    [CmdletBinding()]
    Param
    (
        [Parameter(Mandatory=$true)][String]$Folder
    )
    $ExcelFiles = Get-ChildItem -Path $Folder -Filter *.xlsx -Recurse

    $excelApp = New-Object -ComObject Excel.Application
    $excelApp.DisplayAlerts = $false

    $ExcelFiles | ForEach-Object {
        $workbook = $excelApp.Workbooks.Open($_.FullName)
        $xlsFilePath = $_.FullName -replace "\.xlsx$", ".xls"
        $workbook.SaveAs($xlsFilePath, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlExcel7)
        $workbook.Close()
    }

    # Release Excel Com Object resource
    $excelApp.Workbooks.Close()
    $excelApp.Visible = $true
    Start-Sleep 5
    $excelApp.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelApp)     | Out-Null
}

#
# 0. Prepare the folder path which contains all excel files
$FolderPath = "D:\XXX\AA\BB\Apr-2018"

Convert-XlsInBatch -Folder $FolderPath

Error I am getting-

PS D:\Batch Script> D:\Batch Script\ConvertExcelToXlsInBatch.ps1

New-Object : Retrieving the COM class factory for component with CLSID {00000000-0000-0000-0000-000000000000} failed 
due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).
At D:\Batch Script\ConvertExcelToXlsInBatch.ps1:27 char:14
+     $excelApp = New-Object -ComObject Excel.Application
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : ResourceUnavailable: (:) [New-Object], COMException
+ FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Commands.NewObjectCommand
excel
powershell
asked on Stack Overflow Aug 9, 2019 by Ashu

1 Answer

0

Not sure if you've seen this solution. It's 4 years old but it seems to work.

https://superuser.com/questions/875831/using-powershell-is-it-possible-to-convert-an-xlsx-file-to-xls

answered on Stack Overflow Aug 9, 2019 by koppa

User contributions licensed under CC BY-SA 3.0