Solved

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

Posted on 2013-12-09
6
6,970 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

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…
A procedure for exporting installed hotfix details of remote computers using powershell
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

726 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