Solved

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

Posted on 2013-12-09
6
7,461 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 70

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 70

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
Technology Partners: 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 70

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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

688 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