Tip: Out-ExcelClipboard in PowerShell

From time to time I want to get some result from a PowerShell command into Excel. Unless I’m missing something, there isn’t a great option to do this in standard PowerShell.

I know that you can use Export-Csv or Export-Clixml and then open the file in Excel. I know you can use Out-GridView and then copy and paste from there into Excel (albeit without the column headings). You can also use .Net types but I just want to pipe my result to a function and then paste into Excel. I want <some output of previous functions> | Out-ToExcel

function Out-ExcelClipboard {
    param(
        [Parameter(ValueFromPipelineByPropertyName, ValueFromPipeline)]
        $InputObject
    )
    begin {
        $Objects = @()
    }
    process {
        $Objects += $InputObject
    }
    end {
        $Objects | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Set-Clipboard
    }
}

Export-ModuleMember -Function Out-ExcelClipboard

I’ve added this function to my module which I load by default in my PowerShell profile. This took more code than I was expecting. For the curious, ValueFromPipeline tells PowerShell to bind the output from the pipeline to this parameter. It then calls the process block for each value in the pipeline.

You can add begin and end blocks to do extra stuff before and after all the values have been processed. In my case, stuff them all into a new collection, convert to tab-delimited text without including any type information and then pass that value to the clipboard. Lovely.

Check james’s original post https://jpearson.blog/2023/05/16/tip-out-excelclipboard-in-powershell/ on jpearson.blog which was published 2023-05-16 18:10:00