Powershell script to convert all xls documents to xlsx in a folder recursively

ConvertXLS.ps1

$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()

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?

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

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

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`

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``

Hi is there any way to add a step where the script verify if the xls file is password protected ?

regards

Thanks for the script!

I've done small modifications to make it work in 2021 (to solve aforementioned SaveAs exception issue).
https://gist.github.com/riskeez/096f3ee6bc23d35ed7730bbd36b33c44

Thanks for the script!

I've done small modifications to make it work in 2021 (to solve aforementioned SaveAs exception issue). https://gist.github.com/RichMcLaren/096f3ee6bc23d35ed7730bbd36b33c44

That link seems dead, do you have another link to help get the saveas working?

That link seems dead, do you have another link to help get the saveas working?

@ace080 I've fixed the link in my original message now.