Solved

How to convert .xlsb to .xlsx or .xls using PowerShell

Posted on 2013-12-09
6
6,668 Views
Last Modified: 2013-12-09
I have binary .xlsb file and is there any way could we convert to .xlsx or .xls using PowerShell script.
0
Comment
Question by:SURESH0518
  • 3
  • 2
6 Comments
 
LVL 69

Expert Comment

by:Qlemo
ID: 39706303
No, you can only do that with Excel. But you can automate Excel with PowerShell, if that is what you are asking for.

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?
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39706369
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()

Open in new window

0
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39706375
Hi,
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)

Open in new window


HTH
Rainer
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:SURESH0518
ID: 39706407
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_sheet2.csv and Test_sheet3.csv. Is it possible?
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39706502
Yes, it is, but that needs some more work then.
$file = 'C:\temp\EE\Test'   # xls, xlsx, xlsb or whatever file would be possible here.

$xlApp = New-Object -Com Excel.Application
$xlApp.Visible = $false
$wb = $xlApp.Workbooks.Open($file + '.xlsb')

foreach ($ws in $wb.Worksheets)
{
  $ws.SaveAs($file + '_' + $ws.Name, [Microsoft.Office.Interop.Excel.xlFileFormat]::xlCSVWindows)
}
$wb.Close(0)
$xlApp.Quit()

Open in new window

0
 

Author Closing Comment

by:SURESH0518
ID: 39706546
Thanks a lot it works perfectly
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Synchronize a new Active Directory domain with an existing Office 365 tenant
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question