ConvertTo-DataRange

1.0

ConvertTo-DataRange.ps1

<#PSScriptInfo
 
.VERSION 1.0
 
.GUID 1806fcb1-4fb4-45d6-ba11-d70231936654
 
.AUTHOR Chris Carter
 
.COMPANYNAME
 
.COPYRIGHT 2017 Chris Carter
 
.TAGS Excel ComObject ExcelTables ShareWorkbook
 
.LICENSEURI http://creativecommons.org/licenses/by-sa/4.0/
 
.PROJECTURI https://gallery.technet.microsoft.com/Convert-Tables-to-Data-0b89924a
 
.ICONURI
 
.EXTERNALMODULEDEPENDENCIES
 
.REQUIREDSCRIPTS
 
.EXTERNALSCRIPTDEPENDENCIES
 
.RELEASENOTES Initial Release
 
 
#> 

<#
.SYNOPSIS
Converts tables in an Excel workbook to data ranges
 
.DESCRIPTION
ConvertTo-DataRange converts tables in all sheets of an Excel workbook to data ranges, and then saves the changes. The path to the workbook to convert is supplied to the Path parameter.
 
Workbooks with tables cannot be shared, so this command is useful in preparing a workbook to be shared.
.PARAMETER Path
Specifies the path of the Excel file to be converted.
.INPUTS
System.String
     
    You can pipe a string that contains a path to ConvertTo-DataRange
.OUTPUTS
None.
     
    Start-Display does not generate any output.
.EXAMPLE
PS C:\> ConvertTo-DataRange -Path '<path to file>\Example.xlsx'
 
This command will convert all of the tables in all of the sheets of the workbook Example.xlsx to data ranges.
.LINK
New-Object -ComObject
#>


[CmdletBinding()]

Param(
    [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
        [Alias("FullName")]
        [String[]]$Path
)

Begin {
    try {
        $Excel = New-Object -ComObject 'Excel.Application'
    }
    catch {
        Write-Error "An error occurred while trying to load Excel. Check that Excel is installed on the computer before trying again."
        return
    }

    Function Remove-ComObject  {
        Param([Parameter(ValueFromPipeline=$true)][System.Object]$InputObject)

        Process {
            try {
                $r = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($InputObject)
            }
            catch { Write-Error "An error occurred trying to remove the COM object."; return }

            if ($r -ne 0) {
                Remove-ComObject -InputObject $InputObject
            }
        }
    }
}

Process {
    foreach ($p in $Path) {
        try {
            $Workbook = $Excel.Workbooks.Open($p)
        }
        catch {
            Write-Error "There was an error opening the excel file at $p. Make sure the path is valid, or the file is a valid excel file before trying again."
            continue
        }

        foreach ($Sheet in $Workbook.Worksheets) {
            foreach ($ListObject in $Sheet.ListObjects) {
                $ListObject.Unlist()
            }
        }

        $Workbook.Close($true)
    }
}
    
End {
    $Excel.Quit()
    $Excel | Remove-ComObject
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
}