SURESH0518
asked on
How to convert .xlsb to .xlsx or .xls using PowerShell
I have binary .xlsb file and is there any way could we convert to .xlsx or .xls using PowerShell script.
BTW, the code to save as XLSX is
$file = 'C:\temp\EE\Example'
$xlApp = New-Object -Com Excel.Application
$xlApp.Visible = $false
$xlApp.Workbooks.Open($file + '.xlsb').SaveAs($file, [Microsoft.Office.Interop.Excel.xlFileFormat]::xlOpenXMLWorkbook))
$xlApp.Quit()
Hi,
as Qlemo already mentioned, there is no OOTB way. You would have to utilize Excel COM to automate this like
HTH
Rainer
as Qlemo already mentioned, there is no OOTB way. You would have to utilize Excel COM to automate this like
# Global objects
$scriptPath = (Split-Path -Parent $MyInvocation.MyCommand.Path)
#-----------------------------------------------------
# Helper function to release unmanaged COM objects
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
#-----------------------------------------------------
# Base variables
$basePath="F:\EE\"
$importFile="TestExcelBinary.xlsb"
$exportFile="TestNonBinary.xlsx"
$xlFileFormat = "Microsoft.Office.Interop.Excel.XlFileFormat" -as [type]
# Needs to be set if Office Install is different than regional settings
$newci = [System.Globalization.CultureInfo]"en-US"
[system.threading.Thread]::CurrentThread.CurrentCulture = $newci
# Lets begin
$now = Get-Date
Write-Host "Started execution: $now"
# Initialize Excel COM
$objExcel = New-Object -ComObject Excel.Application
$objExcel.visible = $false
$wb = Get-Item("$basePath$importFile")
$workbook = $objExcel.workbooks.open($wb.fullname, 3)
# Close the workbook - not saving changes (print areas)
$workbook.SaveAs("$basePath$exportFile",$xlFileFormat::xlOpenXMLWorkbook)
$objExcel.Workbooks.close()
$objExcel.Quit()
# Now cleanup COM objects
$a = Release-Ref($workbook)
$a = Release-Ref($objExcel)
HTH
Rainer
ASKER
Sorry now the requirement has changed we have .xlsb file with three tabs and we want to convert to .csv format.
Example: if i have .xlsb file say Test.xlsb with three sheets called sheet1,sheet2 and sheet3 and we would like to convert to Test_sheet1.csv,Test_sheet 2.csv and Test_sheet3.csv. Is it possible?
Example: if i have .xlsb file say Test.xlsb with three sheets called sheet1,sheet2 and sheet3 and we would like to convert to Test_sheet1.csv,Test_sheet
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot it works perfectly
However, that the file is XLSB certainly has good reasons, mainly because it is much smaller. Are you certain you want to save as XLSX or XLS?