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
55
56
57
58
59
60
Function ConvertTo-ExcelXlsx {
    <#
        .SYNOPSIS
            Converts an Excel xls to a xlsx using -ComObject
    #>

    [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()
    }
}