I am currently working on a powershell script that converts excel files from .xls to .xlsx

To be precise, I need this to work in some ways:

1. I need to catch the .xls files FROM a folder and make's a copy to a backup folder

2. converts them to .xlsx and uploads them to upload folder

Converting them from a folder and uploading them to another folder work's fine, but I tried to add some features and now I'm stuck.

This is the error when I try to run:

At C:\Users\Test\Conv_XLS_V2.ps1:40 char:2
  }
   ~ The Try statement is missing its Catch or Finally block. At C:\Users\Test\Conv_XLS_V2.ps1:20 char:16
  ForEach-Object { ~

      
Missing closing '}' in statement block or type definition.
      CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
      FullyQualifiedErrorId : MissingCatchOrFinally

My code:

# set folders
$downloadfolder = "C:\Users\Test"
#$downloadfolder = "folder that gets the .xls files"
$uploadfolder = "C:\Users\Test\Upload"
#$uploadfolder = "folder that uploads the .xlsx files"
$backupfolder = "C:\Users\Test\Backup"
#$backupfolder = "folder that has .xls files as backup"

#open and convert xls to xlsx
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
write-host $xlFixedFormat
$excel = New-Object -ComObject excel.application
$excel.visible = $true
$filetype ="*xls"
Get-ChildItem -Path $folderpath -Include $filetype -recurse | 
ForEach-Object {
    try {
        $xlsfilename = $_.fullname
        #copy file to backup folder
        Copy-Item  -Path $xlsfilename -Destination $backupfolder
        # open the xls
        Write-Output "Converting $xlsfilename"
        $workbook = $excel.workbooks.open($xlsfilename)
        # save converted file (as xlsx)
        $xlsxfilename = $xlsfilename   "x"
        $workbook.saveas($xlsxfilename, $xlFixedFormat)
        $workbook.close()
        #remove old file
        Write-Output "delete & move file(s)"
        Remove-Item -Path $xlsfilename -Force
        Move-Item -Path $xlsxfilename -Destination $uploadfolder -Force

    # garbage collection
    [gc]::collect()
    [gc]::WaitForPendingFinalizers()
}
# close excel
$excel.Quit()
$excel = $null

Can someone have a look please?

CodePudding user response：

The error describe a syntax issue. You have included a try { statement without closing it with a } catch {} block. That's all.

CodePudding user response：

The error message is clear. You forgot to close the try{..} block with an ending bracket } and a try{..} should be followed up by either one or more catch{..} blocks and optionally a finally{..} block.
You can read about that on about Try Catch Finally.

Then, there are some other things wrong and/or can be improved upon in your code as well.

• $folderpath is not defined and should be the source folder $downloadfolder
• use -Filter instead of -Include as it is much faster. Also you have left out the dot in '*.xls'
• append switch -File to the Get-ChildItem cmdlet to make sure you will not receive and try to process directories as well
• you can save the converted .xlsx files directly to the uploadfolder, no need to create first and then move
• to remove the used COM objects, release them from memory first and then initiate the Garbage Collect.
  Do this after you have quit Excel.

# set folders
$downloadfolder = "C:\Users\Test"         # folder where the .xls files are
$uploadfolder   = "C:\Users\Test\Upload"  # folder that uploads the .xlsx files
$backupfolder   = "C:\Users\Test\Backup"  # folder that has .xls files as backup

# open and convert xls to xlsx
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false  # it is much faster if Excel is not visible

# loop through the .xls files and process them
Get-ChildItem -Path $downloadfolder -Filter '*.xls' -Recurse -File | 
ForEach-Object {
    try {
        $xlsfilename = $_.FullName
        #copy file to backup folder
        Copy-Item -Path $xlsfilename -Destination $backupfolder -Force
        # open the xls
        Write-Host "Converting $xlsfilename"
        $workbook = $excel.Workbooks.Open($xlsfilename)
        # save converted file (as xlsx) directly to the upload folder
        $newfilename = Join-Path -Path $uploadfolder -ChildPath ('{0}.xlsx' -f $_.BaseName)
        $workbook.SaveAs($newfilename, $xlFixedFormat)
        $workbook.Close()
        #remove old file
        Write-Host "Delete old file '$xlsfilename'"
        Remove-Item -Path $xlsfilename -Force
    }
    catch {
        # write out a warning as to why something went wrong
        Write-Warning "Could not convert '$xlsfilename':`r`n$($_.Exception.Message)"
    }
}
# close excel
$excel.Quit()
# garbage collection
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()