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
Ashu
  • 37
  • 3
  • 9
  • 1
    The issue you have seems similar to this https://stackoverflow.com/questions/29772500/powershell-cannot-create-outlook-com-object-from-command-prompt – BeMayer Aug 09 '19 at 12:04
  • @BenoîtMayer- In my system neither Outlook nor MS office is installed. Its just a server where file op is coming from Application and going to other location through Perl Scripts. – Ashu Aug 12 '19 at 09:25
  • This is the issue: you cannot use ComObjects without having MS Office installed... – BeMayer Aug 12 '19 at 09:39
  • Is there any way I can convert file without having MS Office. BatchScript, ShellScript, perlscript anything will work. requirement is it should be proper conversion and not just extension rename/change. – Ashu Aug 12 '19 at 11:34
  • To my knowledge it is not possible to convert xlsx files to xls without Excel installed, at least using Powershell, and I have been looking around for a long time. – BeMayer Aug 14 '19 at 08:17
  • You can convert from xlsx to csv using a module called ImportExcel though: https://stackoverflow.com/questions/27293481/convert-multiple-xls-to-csv-using-powershell/56073900#56073900 – BeMayer Aug 14 '19 at 08:18
  • Also if you just change the extension of a file from "csv" to "xls" (without any conversion), Excel will show an error message but will nonetheless open it correctly. It is not optimal but maybe it can be a good-enough solution. – BeMayer Aug 14 '19 at 08:35
  • Ok so instead of powershell can I use Perl? but I do not have MS Office or SpreadSheet installed on my Server. – Ashu Sep 16 '19 at 07:29

1 Answers1

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

koppa
  • 13
  • 4
  • [Type]::Missing,$true) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At D:\Batch Script\Convert.ps1:10 char:1 + $Workbook.SaveAs($NewFilepath,56) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull – Ashu Aug 09 '19 at 12:26