Migrations… always fun to do when you can automate them.
Let’s say that you migrate from Office 2003 to Office 2010. You may want to convert your ‘old’ XLS files to the new XLSX format.
But you would you do that? I believe there is a tool for that, but my customer asked me to script everything.
So, here’s a script that covers two migration scenario’s:
1) Create the new XLSX files next to the XLS files.
2) Create the new XLSX files in a path that is defined when calling the function.
I also found that hiding the Excel GUI will still convert some files with curruptions in them, when the GUI would provide an error… so I added the -Visible parameter 🙂
Have fun migrating!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
function ConvertTo-XLSX { <# .SYNOPSIS XLS files within a provided path are recursively enumerated and convert to XLSX files. .DESCRIPTION XLS files within a provided path are recursively enumerated and convert to XLSX files. The original XLS files remain intact, a new XLSX file will be created. .PARAMETER Path This parameter takes the input of the path where the XLS files are located. .PARAMETER Visible Using the parameter will show you how Excel does the work. Not using the parameter will enable Excel to accomplish its tasks in the background. Note: Bu not using this parameter you will be able to convert some XLS files which have corruptions in them, when using the parameter and therefor the Excel GUI will give you an error. .PARAMETER ToFolder This parameter enables you to provide a location where the file is saved. When this parameter is not used, the file will be saved as an XLS file in the same location as where the original XLS file is located. .EXAMPLE ConvertTo-XLSX -Path 'D:\Data\2012' .EXAMPLE ConvertTo-XLSX -Path 'D:\Data\2012' -Visible .EXAMPLE ConvertTo-XLSX -Path 'D:\Data\2012' -ToFolder 'D:\Data\2012XLSX' .EXAMPLE ConvertTo-XLSX -Path 'D:\Data\2012' -Visible -ToFolder 'D:\Data\2012XLSX' #> [cmdletbinding()] param ( [parameter(mandatory=$true)][string]$Path, [parameter(mandatory=$false)][switch]$Visible, [parameter(mandatory=$false)][string]$ToFolder ) begin { $xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault $Excel = New-Object -ComObject excel.application if ($Visible -eq $true) { $Excel.visible = $true } else { $Excel.visible = $false } $filetype = "*xls" } process { if (Test-Path -Path $Path) { Get-ChildItem -Path $Path -Include '*.xls' -recurse | ForEach-Object { if ($ToFolder -ne '') { $FilePath = Join-Path $ToFolder $_.BaseName $FilePath += ".xlsx" } else { $FilePath = ($_.fullname).substring(0, ($_.FullName).lastindexOf(".")) $FilePath += ".xlsx" } $WorkBook = $Excel.workbooks.open($_.fullname) $WorkBook.saveas($FilePath, $xlFixedFormat) $WorkBook.close() $OldFolder = $Path.substring(0, $Path.lastIndexOf("\")) + "\old" } } else { return 'No path provided or access has been denied.' } } end { $Excel.Quit() $Excel = $null [gc]::collect() [gc]::WaitForPendingFinalizers() } } |
Your script is not working for me?!
what ive done:
saved it as .ps1 file.
Executed it with admin privs – powershell opens and closed again.
What im doing wrong?
Powershell Permissions are set to “unrestricted”.
Please provide me with some more details to get it fixed 🙂
Thanks in Advance!
Try executing the script from a command prompt like so: .\Script.ps1
Add the appropriate parameters.
If there’s still an error, please post it.