Accessing Excel Named Ranges

This topic contains 0 replies, has 1 voice, and was last updated by  Chris Charette 2 months, 3 weeks ago.

  • Author
    Posts
  • #94732

    Chris Charette
    Participant

    I use PowerShell to access a series of Excel workbooks and pull data from them. All the examples I have found to extract data from Excel Named Ranges requires that I know the worksheet that the Named Range is on such as:

    $WorkSheets = $WorkBook.Worksheets |where {$_.name -eq "Property Summary"}
        if ($worksheets){
    ...
    ...
    $Details = [pscustomobject]@{
    Address = $WorkSheet.Range('Address').Text
    City = $WorkSheet.Range('CityStateZip').Text
    ...
    ...
    }
    $Details | Export-Csv -path .....
    }
    else {}
    ...
    ...
    

    This sorta defeats the purpose of a Named Range in that the Named Ranges should be allowed to be anywhere in the workbook.

    Is it that there is no way around this or did all the examples happen to use the Worksheet/cell address and Named Range interchangeably and I'm just not finding the right solution?

    I have some versions of these worksheets where the Named Range value is on a specific worksheet in one "version" of the workbook and it's on a different worksheet in other versions so I'm having to code around this with If/Then loops to try to identify the particular version of worksheet. The more things change, like someone renaming a worksheet, the harder this becomes.

You must be logged in to reply to this topic.