ConvertToExcelXlsx.ps1

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
Function ConvertTo-ExcelXlsx {
[CmdletBinding()]
    Param
    (
        [parameter(Mandatory=$true, ValueFromPipeline)]
        [string]$Path,
        [parameter(Mandatory=$false)]
        [switch]$Force
    )
    Process
    {
        if(-Not ($Path | Test-Path) ){
            throw "File not found" 
        }
        if(-Not ($Path | Test-Path -PathType Leaf) ){
            throw "Folder paths are not allowed"
        }

        $xlFixedFormat = 51 #Constant for XLSX Workbook
        $xlsFile = Get-Item -Path $Path
        $xlsxPath = "{0}x" -f $xlsFile.FullName

        if($xlsFile.Extension -ne ".xls"){
            throw "Expected .xls extension"
        }

        if(Test-Path -Path $xlsxPath){
            if($Force){
                try {
                    Remove-Item $xlsxPath -Force
                } catch {
                    throw "{0} already exists and cannot be removed. The file may be locked by another application." -f $xlsxPath
                }
                Write-Verbose $("Removed {0}" -f $xlsxPath)
            } else {
                throw "{0} already exists!" -f $xlsxPath
            }
        }

        try{    
            $Excel = New-Object -ComObject "Excel.Application"
        } catch {
            throw "Could not create Excel.Application ComObject. Please verify that Excel is installed."
        }

        $Excel.Visible = $false
        $Excel.Workbooks.Open($xlsFile.FullName) | Out-Null
        $Excel.ActiveWorkbook.SaveAs($xlsxPath, $xlFixedFormat)
        $Excel.ActiveWorkbook.Close()
        $Excel.Quit()
    }
}