-
-
Save gabceb/954418 to your computer and use it in GitHub Desktop.
| $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() |
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.
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.
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
$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
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 =
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
$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 exceptionissue). 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.
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
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!