CODEWITHSUNDEEP
xlrdnamed-ranges
user1182090
user1182090

Reputation: 301

How to get named range for a particular cell from excel using python

i need to get "name a range" value entered for a particular cell.

i entered "name a range" value for (A4,B4) cell in excel,to read cell value we use sh.cell(row,col) but how to get name range value for that cell.

i am not getting how to do...

help me.. thanks in advance

Upvotes: 0

Views: 4145

Answers (2)

Devesh Sharma
Devesh Sharma

Reputation: 975

from xlsxwriter.utility import xl_rowcol_to_cell

cell = xl_rowcol_to_cell(1, 2)  # C2

print(cell)

Upvotes: 0

Ryan G
Ryan G

Reputation: 9560

Unfortunately I don't have enough rep to comment, so I will try my best from what I can to understand from your question.

So if I got this right, you have a named range on a worksheet, where the name of the range, Referenced by sh.Range("A4:B4"), is 'name_a_range' (I included underscores because you can't have spaces in the name).

You can get the value from the Sheets.Cells(row, col).Value , but you want to do this through the named range.

Ok, to do this:

    Dim valA as Variant, valB as Variant
    Dim valArray() as Variant
    '' To get the value in Cell "A4"
    valA = sh.Range("name_a_range").Cells(1,1).Value

    '' To get the value in Cell "B4"
    valB = sh.Range("name_a_range").Cells(1,2).Value

    '' Or if you have a Variant() array already Dim'ed (like above)
    valArray = sh.Range("name_a_range").Value

The Worksheet's Range object can either take:

    '' Expression for a range: "A4" to "B4" , using Cells
    sh.Range(sh.Cells(4,1),  sh.Cells(4,2)) 

    '' Expression for a range: "A4" to "B4" , using xlA1 format
    ''  *Which is the most familiar
    sh.Range("A4:B4")

    '' Expression for a range: "A4" to "B4" , using name
    sh.Range("PreviouslyNamedRange")

With any range, you can think of it as it's own array (starting from position 1). And if the size (dimensions) of the range is unknown, you can use the built-in Range functions:

    sh.Range("name_a_range").Rows.Count '' returns 1
    sh.Range("name_a_range").Column.Count '' returns 2

As for the Python aspect from the title, you will use the same exact syntax:

Although, I don't use xlrd, and instead I drive Excel through python using win32com.client

    from win32com.client import Dispatch
    app_xl = Dispatch("Excel.Application")
    wb = app_xl.Workbooks.Open("Path\\To\\Your\\Workbook.xlsx")
    range = wb.Sheets(1).Range("name_a_range").Value

For example, I ran this on one of my Sheets:

    >>> from win32com.client import Dispatch
    >>> app_xl = Dispatch("Excel.Application")
    >>> wb = app_xl.Workbooks.Open("C:\***\***\excel_vba_tests\getinfo.xlsx")
    >>> rangeValues = wb.Sheets("Summary").Range("avgVolume").Value
    >>> rangeValues
    ((u'Avg/Wk',), (Decimal('791517.25'),), (Decimal('796369'),), (Decimal('769922.8846'),), (Decimal('743311.549'),))

Here, I opened my Workbook, "getinfo.xlsx", and from my Sheet named "Summary" I was able to get all the values in my named range "avgVolume"

Is this helpful/what you were looking for? Since I wasn't too sure of what your question was exactly I figured I'd hit as many points as possible.

Upvotes: 1

Related Questions