ConvertTo-DataRange.ps1
|
<#PSScriptInfo
.VERSION 1.1.3 .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 SaveAs parameter added so that the original file can be kept untouched. Help information updated with minor corrections Verbose statments added. #> <# .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. .PARAMETER SaveAs Specifies an alternate path to save the Excel file to in case the original should not be changed. .INPUTS System.String You can pipe a string that contains a path to ConvertTo-DataRange .OUTPUTS None. ConvertTo-DataRange 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, and save the changes to the file. .EXAMPLE PS C:\> ConvertTo-DataRange -Path '<path to file>\Example.xlsx' -SaveAs '<new path to file>\NewBook.xlsx' This command will convert all of the tables in all of the sheets of the workbook Example.xlsx to data ranges, and then save the workbook as NewBook.xlsx while Example.xlsx remains unchanged. .LINK New-Object -ComObject #> [CmdletBinding(SupportsShouldProcess=$true)] Param( [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)] [Alias("FullName")] [String[]]$Path, [Parameter(Position=1)] [Alias("Sa")] [String]$SaveAs ) Begin { try { #Open Excel Write-Verbose "Opening Excel..." $Excel = New-Object -ComObject 'Excel.Application' Write-Verbose "Excel opened." } catch { Write-Error "An error occurred while trying to load Excel. Check that Excel is installed on the computer before trying again." return } #Helper function that releases the COM object until the reference number reaches 0 Function Remove-ComObject { Param([Parameter(ValueFromPipeline=$true)][System.Object]$InputObject) Process { try { #Releases the COM object $r = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($InputObject) } catch { Write-Error "An error occurred trying to remove the COM object."; return } if ($r -gt 0) { #If references are still extant, call recursively. Remove-ComObject -InputObject $InputObject } } } } Process { #handle arrays foreach ($p in $Path) { try { #Open the file Write-Verbose "Opening the file at $p..." $Workbook = $Excel.Workbooks.Open($p) Write-Verbose "$p opened." } 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 } #Go through each sheet in the workbook foreach ($Sheet in $Workbook.Worksheets) { Write-Verbose "Checking $($Sheet.Name) for tables..." Write-Verbose "Found $($Sheet.ListObjects.Count) table(s)" $i = 0 #Note: Tables in Excel used to be call 'lists'. #Go through each table in the sheet foreach ($List in $Sheet.ListObjects) { $i++ if ($PSCmdlet.ShouldProcess("Table $i in sheet $($Sheet.Name)", "Convert to data range ")) { Write-Verbose "Converting table $i to data range..." #Convert each table to a data range $List.Unlist() if ($?) { Write-Verbose "Table converted to data range." } else { Write-Error "There was an error converting the table to a data range." } } } Write-Verbose "Done with $($Sheet.Name)." } #Save under an alternate name if specified... if ($SaveAs) { #Test directory and filename of SaveAs if ((Test-Path (Split-Path $SaveAs -Parent)) -and ((Split-Path $SaveAs -Leaf) -match '\.xls$|\.xlsx$')) { #If the directory is valid, save under the alternate name and close if ($PSCmdlet.ShouldProcess($Workbook.Name, "Save As $(Split-Path $SaveAs -Leaf)")) { Write-Verbose "Saving workbook as $SaveAs..." $Workbook.SaveAs($SaveAs) Write-Verbose "Saved" } Write-Verbose "Closing file..." $Workbook.Close($false) Write-Verbose "File Closed." } else { Write-Error "The path supplied to the SaveAs parameter is not in a valid directory or is not the correct extension of an Excel file. Please, check the path and try again." $Workbook.Close($false) continue } } #...or just save and close else { if ($PSCmdlet.ShouldProcess($Workbook.Name, "Save")) { Write-Verbose "Saving and closing $($Workbook.Name)..." $Workbook.Close($true) Write-Verbose "File saved and closed." } } } } End { #Quit Excel, and then send to function to release COM object Write-Verbose "Closing Excel..." $Excel.Quit() $Excel | Remove-ComObject #The below two statements are to help remove the excel.exe process which can be stubbornly held onto. #These do not always work. [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() Write-Verbose "Excel Closed." } |