Using PowersShell to define a Named area in Excel workbook

This topic contains 2 replies, has 2 voices, and was last updated by  Bartdad 4 years, 1 month ago.

  • Author
    Posts
  • #8088

    Bartdad
    Participant

    I'm trying to use PowerShell to manipulate an Excel  spreadsheet that will then be imported into MS Outlook as Calendar Events. The Import process for Outlook requires that there be a Named range of rows that contain the values to be imported.  Although I have everything I need to open and manipulate the rows and cells within the spreadsheet, I have yet to find any way to define the range of cells for a Named area, and then t actually create the name in the spreadsheet.

    Has anyone done anything like this?

  • #8092

    Richard Siddaway
    Moderator

    I've just run this

    $xl = New-Object -ComObject 'Excel.Application'
    $wkbk = $xl.Workbooks.Add()
    $sheet = $wkbk.WorkSheets.Item(1)
    $range = $xl.Range("A1", "D4")
    $range.Name = "Test"
    

    and it shows up in Excel as a named range – does this do what you want?

  • #8093

    Bartdad
    Participant

    Yes! Thanks.

    I guess I was trying to assign the range to the workbook

You must be logged in to reply to this topic.