Solved

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

Posted on 2013-12-09
6
6,009 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 68

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 68

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 68

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 will help you understand what HashTables are and how to use them in PowerShell.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now