Help with getting a Table without Headers

[NoralK]

I have look for hours trying to figure out how to get a Table Range back that does not include the table headers so I can add a named reference that has a scope of Workbook. I would use -RangeName when creating the Table but two issues with that, 1) the scope of the RangeName is the worksheet only and 2) the Range includes the headers. This is what I have:

$TableRange = $xl.Workbook.Worksheets["theSheet"].Tables["theTable"].Address.Address
$RangeCells=$ws.cells[$TableRange]
$xl.Workbook.Names.Add("theName", $RangeCells)

Please let me know and if there is a better way.
Cheers

[dfinke]

Sorry @NoralK, I have never tried to that nor have I seen it done. I believe you are the first 🎉

[NoralK]

@dfinke Does this mean you will not look into it?

[dfinke]

@NoralK not for a while. I have other pressing matters.

[pauljnav]

Hi @NoralK (CC: @dfinke)
In VBA, the Range.Offset property represents a range that's offset from the specified range.
I see the same exists in ImportExcel for the $RangeCells object.

Try the following for size:

# Define offset range, omitting row 1.
$offsetRange = $RangeCells.Offset(1, 0)
# Reveal the cell addresses to confirm
$offsetRange | Select-Object Address
$RangeCells | Select-Object Address

[NoralK]

@pauljnav Thank you! for that nudge...I was able to come up with this complete solution:

$xlSourcefile = "./theFile.xlsx"

$data = Import-Csv "./SingleColumnOfDataWithHeaderRow.csv"

$data | Export-Excel $xlSourcefile -WorksheetName "theSheet" `
    -TableStyle None `
    -AutoSize -StartRow 1 -StartColumn 6 `
    -TableName "theTable"

$xl = Open-ExcelPackage $xlSourcefile
$ws = $xl.Workbook.Worksheets["theSheet"]
$TableRange = $ws.Tables["theTable"].Address.Address
# Original Range F1:F50
$RangeCells=$ws.cells[$TableRange]
# Define offset range, omitting row 1
#     however this gives an extra row that we do not want.
# will product F2:F51
$offsetRange = $RangeCells.Offset(1, 0)
# Need F2:F50 - mash it together
$NewTableRange = "$($offsetRange.Start.Address):$($rangecells.End.Address)"
# Get the cells
$RangeWithoutHeaders = $ws.cells[$NewTableRange]
# Add the named range
# F2:F50 
$xl.Workbook.Names.Add("theName", $RangeWithoutHeaders) | Out-Null
# Now I can reference theName[ColumnName] in the entire
#     Workbook where theTable[ColumnName] has issues
Close-ExcelPackage $xl

[dfinke]

Glad Open-ExcelPackage and the -PassThru are part of the ImportExcel. Escape hatch to the underlying object model.

Thanks all!