locked
Powershell - Excel: Find named cells and their values RRS feed

  • Question

  • Hi,

    Currently, I'm working on a PowerShell script which will read data from excel files. reading the cell values is not a problem as long as I use numbered reference (eg. instead o1,1 instead of A1). However, it is easy to add or remove rows/columns and therefore a fixed cell is not desirable.

    We decided to used named cells, to fix the problem of adding rows/column. A new problem is the PowerShell script. I can't find an example of reading named cell.

    Does anyone kwon whet

    her it is possible to use named cell references?

    Part of my current code:

    $book=$excel.Workbooks.Open($global:filesNames[1].FullName)
    $sheet=$book.WorkSheets.item(1)
    $Cell=$sheet.Cells.Item(2,1).text #(row,col)
    $Cell     

    What I would like:

    $book=$excel.Workbooks.Open($global:filesNames[1].FullName)
    $sheet=$book.WorkSheets.item(1)
    $Cell=$sheet.Cells.Item("First_Name").text #(named cell)
    $Cell    

    Many thanks in advance

    Wednesday, February 8, 2017 3:23 PM

All replies

  • Hi, I spent hours to find how. My idea is to read a kind of an Excel configuration file, with named columns because I don't want to use index numbers which is pretty hard to maintain in a script.

    What I did :
    In an Excel worksheet, I created a table (named by default "Table1"), then I named all my columns - more or less automatically, by selecting column (but without header row) and clicking Define Name button in Formulas ribbon tab. For example, if my column is "Column name', the proposed range name is Worksheet!Column_name. Fine.

    Then in my PowerShell code, after opening Excel file,
    $Sheet = $Workbook.Sheets.Item("Worksheet")
    # By cycling into rows of table,
    foreach ($row in in $Sheet.Range("Table1").Rows) {
    # I can access a given cell content with :
    $celltext = $row.Columns($Sheet.Range("Sheet!Column_name").Column).Text
    }

    I am not sure at all that it is the best way, but it is working on the fact that $Sheet.Range("Sheet!Column_name").Column is the index of the named column Column_name... Just to tell, beware of the s for the first Columns et without s in second one Column...

    Antoine


    Tuesday, May 23, 2017 7:34 PM