CODEWITHSUNDEEP
powerquery
ImFabien75
ImFabien75

Reputation: 189

Power Query: Extract dynamic sheet data for my current file

I have a sheet named "CENTList" which is loaded while activating a VBA macro. This sheet can then have a dynamic number of rows, with columns extending from A to X, and with headers on first row.

I want to extract this data in Editor Power Query:

I found a way to do it writting specifically the name of the file. The following code is working and you can see on the pic, the "CentList" exists

let
    Source = Excel.Workbook(File.Contents("myfilepath\myfilename.xlsm"), null, true),
    DataSheet = Source{[Item="CENTList",Kind="Sheet"]}[Data]
in
    #"DataSheet"

enter image description here

But what I want it is to use the current file, whatever its name. Then I tried that:

    let
    Source = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="CENTList"]}[Content]{0}[Name]), null, true),
    DataSheet = Source{[Item="CENTList", Kind="Sheet"]}[Data]
in
    #"DataSheet"

Doing that, PowerQuery replies me it can't find "CENTList"

Do you know what I should do?

Thanks for replies

Upvotes: 0

Views: 222

Answers (1)

horseyride
horseyride

Reputation: 21413

Your VBA needs to create a named range

ActiveWorkbook.Names.Add Name:="abcd", RefersToR1C1:="=CENTList!R7C5:R16C10"

or

Sub macro4()
Worksheets("CENTList").Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Names.Add Name:="abcd", RefersTo:=Selection
End Sub

then PowerQuery can read it

Source = Excel.CurrentWorkbook(){[Name="abcd"]}[Content]

Upvotes: 0

Related Questions