I have look for hours trying to figure out how to get a Table Range back that does not

Hi <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="

<a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/us

Sorry <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-ur

<a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/us

Glad Open-ExcelPackage and the <code class="notransla

Help with getting a Table without Headers about importexcel HOT 6 CLOSED

NoralK commented on May 27, 2024

Help with getting a Table without Headers

from importexcel.

Comments (6)

pauljnav commented on May 27, 2024 1

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

from importexcel.

NoralK commented on May 27, 2024 1

@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