Forum Discussion

FlorisMK's avatar
FlorisMK
Icon for Helper I rankHelper I
2 years ago
Solved

Refer to worksheet-scope Named Range

I can refer to an Excel named range in Power Query thus:   Excel.CurrentWorkbook(){[Name="MyNamedRange"]}[Content]{0}[Column1]   Unfortunately, as far as I can tell, this only works if MyNamedRan...
Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi FlorisMK 

    Based on your question, I take it you are using Power Query within Excel - is that right?

     

    I did some testing at my end, and Power Query appears to be able to access both worksheet-scoped and workbook-scoped named ranges.

     

    For example, I have these named ranges in an Excel workbook:

    and these appear as follows when I connect to the workbook in Power Query:

    You could query the content of a worksheet-scoped range Range_Sheet3 on sheet Sheet3 using:

    = Excel.CurrentWorkbook(){[Name="Sheet3!Range_Sheet3"]}[Content]

     or for the top-left cell

    = Excel.CurrentWorkbook(){[Name="Sheet3!Range_Sheet3"]}[Content]{0}[Column1]

    and this would allow parameterising as necessary depending how queries are organised.

     

    Does this help?

     

    Regards

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
2 years ago

You're welcome FlorisMK  🙂

A query created in Power Query isn't aware of where it is ultimately being loaded to in the worbook, at least in any way that could be represented in M code (to my knowledge).

 

For your requirements, I'm thinking that you may need extend Power Query with VBA or some other method to handle the query creation.

 

Out of curiosity, I played around with some VBA code that:

  1. Grabs the M code from the Query Text sheet.
  2. Loops through all sheets in the workbook and:
    1. Creates a query in Power Query sourced from the range "MyRange" on that particular sheet, with "<SHEET_NAME>" "<RANGE_NAME>" replaced within the query text.
    2. Loads the result of that query to $F$1 on the same sheet.

This likely is not exactly what you want, but at least it illustrates how VBA could help. Note that there is no error-handling in the code (e.g. if queries already exist).

 

The code is contained in Sub CreateQueries in Module1, and I linked it to a button on the first page for testing.

 

Link to the Excel workbook.

 

Hoping this helps you get closer to your solution!

 

 

 

Regards

FlorisMK's avatar
FlorisMK
Icon for Helper I rankHelper I
2 years ago

Thanks! Given that this is a biannual analysis, I won't actually be using VBA, but it's good to have an example of how to interact with queries. I'm accepting your previous answer as solution; this is the above and beyond bit 🙂