Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Powershell script to convert all xls documents to xlsx in a folder recursively
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
write-host $xlFixedFormat
$excel = New-Object -ComObject excel.application
$excel.visible = $true
$folderpath = "C:\Users\gabceb\Documents\testXLS"
$filetype ="*xls"
Get-ChildItem -Path $folderpath -Include $filetype -recurse |
ForEach-Object `
{
$path = ($_.fullname).substring(0, ($_.FullName).lastindexOf("."))
"Converting $path"
$workbook = $excel.workbooks.open($_.fullname)
$path += ".xlsx"
$workbook.saveas($path, $xlFixedFormat)
$workbook.close()
$oldFolder = $path.substring(0, $path.lastIndexOf("\")) + "\old"
write-host $oldFolder
if(-not (test-path $oldFolder))
{
new-item $oldFolder -type directory
}
move-item $_.fullname $oldFolder
}
$excel.Quit()
$excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()
@dlairman

This comment has been minimized.

Copy link

commented Jan 9, 2013

If you don't have admin rights on your system, or you are using Windows 2008 Server you may recieve an error when running this script.

Be sure you have either access to or create the folder
C:\Windows\SysWOW64\config\systemprofile\Desktop

See this post:
http://www.techdecode.com/wp/?p=58

This is the error:
Exception calling "SaveAs" with "2" argument(s): "SaveAs method of Workbook class failed"
At ConvertXLS.ps1:16 char:18

  • $workbook.saveas <<<< ($path, $xlFixedFormat)
    
    • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    • FullyQualifiedErrorId : ComMethodTargetInvocation

I got the error on my desktop system. The resolution for me was granting my non-admin self access to the folder using an admin-level account.

Thanks for the script!

@knxroot

This comment has been minimized.

Copy link

commented Mar 25, 2013

If you use $excel.visible = $false you can transform files with structure problems (p.e File with no compatible elements) (I test this using MSExcel 2007). Of course with this variable the process run in background.

@bklaas

This comment has been minimized.

Copy link

commented Jul 29, 2014

Tip of the cap to you gabceb. I'm involved in a project that requires the recursive updates of > 150,000 xls files to xlsx, your gist is incredibly helpful as an initial template for what I need to do.

I also had the same error as dlairman mentions above, and also cleared the issue with the folder creation step as described.

@slankas

This comment has been minimized.

Copy link

commented Aug 2, 2014

I needed to add this line at the start of the script. (Win7 64bit, Office 2013) to correct an error in not finding the format.
Add-Type -AssemblyName Microsoft.Office.Interop.Excel

Also, is there any way to default the answers on any pop-up dialogs?

@xfirebg

This comment has been minimized.

Copy link

commented May 12, 2017

Hello thanks for the script. I'm wondering how to move the new file after covert?

@mahaboob

This comment has been minimized.

Copy link

commented Sep 28, 2017

Unable to get the SaveAs property of the Workbook class
At C:\Users\Aish\Downloads\954418-300dc3ab8f59e9e2fb3724d94fc7ff50fed8046e\ConvertXLS.ps1:16 char:2

  • $workbook.saveas($path, $xlFixedFormat)
    
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    • CategoryInfo : OperationStopped: (:) [], COMException
    • FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Getting this exception in my Powershell

@tsvikrn

This comment has been minimized.

Copy link

commented May 30, 2018

my edition with macro handling and graphic folder selection 👍
`<#************************************************************************************
*a script to convert xls files to xlsx\xlsm. *
*steps: *

  • -creates backup in defined backup directory preserving directory structure *
  • depending on $backupfiles value                                                *
    
  • (will backup xls* due to copy-item filter limitations)                         *
    
  • -finds files by filter in a given directory tree (xls only) *
  • -loops though all found files (xls only) *
  •    +open each file in excel                                                    *
    
  •    +convert the file to xlsx\xlsm (depending on macros) using excel's save as  *
    
  •    +close the file                                                             *
    
  •    +delete the original file depending on $delfiles value                      *
    

*MS excel is needed for this script to work *
*Tsvi Keren *
*************************************************************************************#>

#set variables**********************************
$starttime = Get-Date -Format T
$filefilter = ".xls"
$delfiles = 1
$backupfiles = 1
$totalfiles = 0
$curfile = 0
$filepath = "C:\temp"
$backuppath = "C:\temp\backup"

#set folders graphically***********************
#minimize all windows to make dialog in front
$shell = New-Object -ComObject "Shell.Application"
$shell.minimizeall()
#load assembly
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
#root folder****************
$rootfoldername = New-Object System.Windows.Forms.FolderBrowserDialog
$rootfoldername.rootfolder = "MyComputer"
$rootfoldername.Description = "Root Folder"
$rootfoldername.Multiselect = $False
#show selection dialog
if($rootfoldername.ShowDialog()-eq "OK")
{
$filepath = $rootfoldername.SelectedPath
}
else
{
#undo minimizing and exit
$shell = New-Object -ComObject "Shell.Application"
$shell.undominimizeall()
Exit
}
#backup folder**************
$backupfoldername = New-Object System.Windows.Forms.FolderBrowserDialog
$backupfoldername.rootfolder = "MyComputer"
$backupfoldername.Description = "Backup Folder"
#show selection dialog
if($backupfoldername.ShowDialog() -eq "OK")
{
$backuppath = $backupfoldername.SelectedPath
}
else
{
#undo minimizing and exit
$shell = New-Object -ComObject "Shell.Application"
$shell.undominimizeall()
Exit
}
#undo minimizing************
$shell = New-Object -ComObject "Shell.Application"
$shell.undominimizeall()

#create backup*********************************
if ($backupfiles = 1)
{
write-host "creating backup in $backuppath"
copy-item $filepath -Destination $backuppath -Filter "*$filefilter" -Force -Recurse
write-host "backup done"
}

#excel object********************************
#load dotnet assembly
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
#create excel object
$excel = New-Object -ComObject excel.application
$excel.visible = $true
$excel.DisplayAlerts = $False
$excel.WarnOnFunctionNameConflict = $False
$excel.AskToUpdateLinks = $False

#loop through files****************************
write-host "converting files..."
$files = Get-ChildItem -Recurse -Force $filepath -ErrorAction SilentlyContinue | Where-Object { ($.PSIsContainer -eq $false) -and ( $.Name -like "$filefilter") }
$totalfiles = $files.Count
foreach ($file in $files)
{
#convert file
**********
$curfile = $curfile + 1
write-host "converting " $file.fullname "file $curfile of $totalfiles"
#open file
$workbook = $excel.workbooks.open($file.fullname)
#check for macros in in file
if ($workbook.HasVBProject)
{
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbookMacroEnabled
$newfile = ($file.fullname).substring(0, ($file.FullName).lastindexOf("."))
$newfile += ".xlsm"
}
else
{
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
$newfile = ($file.fullname).substring(0, ($file.FullName).lastindexOf("."))
$newfile += ".xlsx"
}
#convert
$workbook.saveas($newfile, $xlFixedFormat)
#close file
$workbook.close()
write-host "done"

#**********delete file**********************
if ($delfiles = 1)
    {
    write-host "deleting " $file.fullname
    remove-item -literalpath $file.FullName
    write-host "file deleted"
    }
}

#cleanup***************************************
$excel.Quit()
$excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()

#execution time********************************
$endtime = Get-Date -Format T
$minute = (New-TimeSpan -Start $starttime -End $endtime).TotalMinutes
Write-Host Start at $starttime, End At $endtime, Took About $minute Minutes`

@stephannn

This comment has been minimized.

Copy link

commented Jul 3, 2018

Hi tsvikrn,
I modified your script a little bit. I got an error with the PSIsContainer Command and recreated the folder structure in the backup location

Edit: I just see that the line $_.PSIsContainer gets always translated to $.PSIsContainer

Config
<XmlConvert> <Config> <filefilter>*.xls</filefilter> <delfiles>1</delfiles> <backupfiles>1</backupfiles> <totalfiles>0</totalfiles> <curfile>0</curfile> <filepath>C:\Users\user\Desktop\test</filepath> <backuppath>C:\temp\backup</backuppath> <gui>0</gui> </Config> </XmlConvert>

PS
`<#************************************************************************************
*a script to convert xls files to xlsx\xlsm. *
*steps: *

-creates backup in defined backup directory preserving directory structure *

depending on $backupfiles value                                                *

(will backup xls* due to copy-item filter limitations)                         *

-finds files by filter in a given directory tree (xls only) *
-loops though all found files (xls only) *

   +open each file in excel                                                    *

   +convert the file to xlsx\xlsm (depending on macros) using excel's save as  *

   +close the file                                                             *

   +delete the original file depending on $delfiles value                      *

MS excel is needed for this script to work *
Tsvi Keren *
*************************************************************************************#>

$scriptpath = Split-Path -parent $MyInvocation.MyCommand.Definition

#set variables**********************************

[xml]$ConfigAttribute = Get-Content ($scriptpath + "\convert_xls_xlsx.config.xml")
$listService = New-Object System.Collections.ArrayList

If ($ConfigAttribute -ne $null){
$starttime = Get-Date -Format T
$filefilter = $ConfigAttribute.XmlConvert.Config.filefilter
$delfiles = $ConfigAttribute.XmlConvert.Config.delfiles
$backupfiles = $ConfigAttribute.XmlConvert.Config.backupfiles
$totalfiles = $ConfigAttribute.XmlConvert.Config.totalfiles
$curfile = $ConfigAttribute.XmlConvert.Config.curfile
$filepath = $ConfigAttribute.XmlConvert.Config.filepath
$backuppath = $ConfigAttribute.XmlConvert.Config.backuppath
$gui = $ConfigAttribute.XmlConvert.Config.gui
}

if($gui -eq 1){
#set folders graphically***********************
#minimize all windows to make dialog in front
$shell = New-Object -ComObject "Shell.Application"
$shell.minimizeall()
#load assembly
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
#root folder****************
$rootfoldername = New-Object System.Windows.Forms.FolderBrowserDialog
$rootfoldername.rootfolder = "MyComputer"
$rootfoldername.Description = "Root Folder"
$rootfoldername.Multiselect = $False
#show selection dialog
if($rootfoldername.ShowDialog()-eq "OK") {
$filepath = $rootfoldername.SelectedPath
} else {
#undo minimizing and exit
$shell = New-Object -ComObject "Shell.Application"
$shell.undominimizeall()
Exit
}
#backup folder**************
$backupfoldername = New-Object System.Windows.Forms.FolderBrowserDialog
$backupfoldername.rootfolder = "MyComputer"
$backupfoldername.Description = "Backup Folder"
#show selection dialog
if($backupfoldername.ShowDialog() -eq "OK") {
$backuppath = $backupfoldername.SelectedPath
} else {
#undo minimizing and exit
$shell = New-Object -ComObject "Shell.Application"
$shell.undominimizeall()
Exit
}
#undo minimizing************
$shell = New-Object -ComObject "Shell.Application"
$shell.undominimizeall()
}

#create backup*********************************
if ($backupfiles = 1) {
write-host "creating backup in $backuppath"
#copy-item $filepath -Destination $backuppath -Include $filefilter -Force -Recurse
Get-ChildItem $filepath -Include "*$filefilter" -recurse | `
foreach{
$targetFile = $backuppath + $.FullName.SubString($filepath.Length);
New-Item -ItemType File -Path $targetFile -Force;
Copy-Item $
.FullName -destination $targetFile
}
write-host "backup done" -ForegroundColor Green
}

#excel object********************************
#load dotnet assembly
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
#create excel object
$excel = New-Object -ComObject excel.application
$excel.visible = $true
$excel.DisplayAlerts = $False
$excel.WarnOnFunctionNameConflict = $False
$excel.AskToUpdateLinks = $False

#loop through files****************************
$files = Get-ChildItem -Recurse -Force $filepath -ErrorAction SilentlyContinue | Where-Object { ($.PSIsContainer -eq $false) -and ( $.Extension -like "$filefilter") }
$totalfiles = $files.Count
write-host "converting files... [$totalfiles]"
foreach ($file in $files)
{
#convert file**********
[int]$curfile = [int]$curfile + 1
write-host "converting " $file.fullname "file $curfile of $totalfiles" -ForegroundColor DarkGreen
#open file
$workbook = $excel.workbooks.open($file.fullname)
#check for macros in in file
if ($workbook.HasVBProject) {
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbookMacroEnabled
$newfile = ($file.fullname).substring(0, ($file.FullName).lastindexOf("."))
$newfile += ".xlsm"
} else {
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
$newfile = ($file.fullname).substring(0, ($file.FullName).lastindexOf("."))
$newfile += ".xlsx"
}
#convert
$workbook.saveas($newfile, $xlFixedFormat)
#close file
$workbook.close()
write-host "done" -ForegroundColor DarkGreen

#delete file************
if ($delfiles = 1)
{
write-host "deleting " $file.fullname
remove-item -literalpath $file.FullName
write-host "file deleted"
}
}

#cleanup***************************************
$excel.Quit()
$excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()

#execution time********************************
$endtime = Get-Date -Format T
$minute = [math]::Round((New-TimeSpan -Start $starttime -End $endtime).TotalMinutes,3)
Write-Host Start at $starttime, End At $endtime, Took About $minute Minutes``

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.