CODEWITHSUNDEEP
excelpowershell
nExoR LU
nExoR LU

Reputation: 37

PowerShell: how to add table to excel spreadsheet

i need simple code to add table to a excel file. whatever i do, adding table fails and i can't find solution. my code:

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $false
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)
$worksheet.name

$Table = $worksheet.Tables.Add(
    [Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange,
    $worksheet.Range("A1:C2"), 
    "importedCSV",
    [Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes
    )
$worksheet.SaveAs("test.xlsx", 51,$null,$null,$null,$null,$null,$null,$null,'True') 
$Excel.Quit()
while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) ){}

i tried as well:

$Table = $worksheet.Tables.Add($worksheet.cells("A1:C2"), "importedCSV")

...and many others. i always get 'You cannot call a method on a null-valued expression.' or 'Value does not fall within the expected range.' HELP!

Upvotes: 1

Views: 5022

Answers (1)

PMental
PMental

Reputation: 1179

Try changing this:

$Table = $worksheet.Tables.Add(

to this:

$Table = $worksheet.ListObjects.Add(

Upvotes: 1

Related Questions