Set the First Row as Header in PowerShell Com-Object

Jen Bites 1 Reputation point
2022-10-18T04:13:17.747+00:00

251414-image.png

I'm following this post here: https://social.technet.microsoft.com/forums/lync/en-US/acf022bf-5d9c-4d3d-b9ca-2d8a368ca786/insert-a-table-in-excel-using-powershell?forum=ITCG

It Creates a table on my Worksheet and no errors, but how can I set my First Row (Which is now the Row 2 of my Output File) as the Header of my table?
And what does $Null do here in this line?
$ListObject = $OutputSheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $OutputSheet.UsedRange, $Null)

Thank you :)

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,653 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,434 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Chilly 116 Reputation points
    2023-03-16T22:24:59.3033333+00:00

    https://learn.microsoft.com/en-us/office/vba/api/excel.listobjects.add

    It looks like $null is to external data source, but is not relevant.

    Indicates whether an external data source is to be linked to the ListObject object. If SourceType is xlSrcExternal, the default is True. Invalid if SourceType is xlSrcRange, and will return an error if not omitted.

    Also note that the post you reference has a better answer that does not generate a new line

    $ListObject = $ExcelApplication.ActiveSheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $ExcelApplication.ActiveCell.CurrentRegion, $null ,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes)

    0 comments No comments