pull worksheet name into query without opening source workbook

[MSmith]

Hello,

I've Googled, looked at many blog posts and forums, and searched M is for Data Monkey, but found no answer. I've also spend several hours trying different things to accomplish. So, I'm posting here. Using Excel 365.

I have a query to pull data from a closed workbook and prep it for use in other queries.
It works fine except that the report I'm pulling from is system generated and the single worksheet has a slightly different name each time. So, the first sheet name is EFT_EFTA_ALLTRANS_29859730, Next day it might be EFT_EFTA_ALLTRANS_29895764. There isn't a particular pattern as far as I can tell. The numbers change with each.

Here's my current code. The problem is I have to change the green text every time I link to a new file. I don't really mind, but my coworkers don't know anything about PQ.

let
Source = Excel.Workbook(File.Contents("X:\filepath\AllTrans 04-13-2018.xls"), null, true),
EFT_EFTA_ALLTRANS_1 = Source{[Name="[B][COLOR=#00ff00]EFT_EFTA_[/COLOR][/B][COLOR=#00FF00][B]ALLTRANS_29859730[/B][/COLOR]"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(EFT_EFTA_ALLTRANS_1, [PromoteAllScalars=true])
in
#"Promoted Headers"

I've tried loading the file path and name into a parameter table, and pulling the sheet name. The code below loads the sheet name into a single row table called Sheet_Name, but I can't get the first query to source from that cell in step 2.

let Source = Excel.Workbook(File.Contents(fnGetParameter("File Path")), null, true),
Name = Source[Name]
in
Name

This would result in the first query pulling from the one just above. First query code now looks like this (new code in blue), but it's not working.
let
Source = Excel.Workbook(File.Contents("file path"), null, true),
Source{[Name=fnGetParameter("Sheet Name")]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(EFT_EFTA_ALLTRANS_1, [PromoteAllScalars=true])
in
#"Promoted Headers"


So, I've tried a number of things. A salient point is that I'm trying to do all this without opening the source workbook and changing the sheet name. Since it is original data, I don't want to alter it.



I'm enjoying working through M is for Data Monkey and have added PQ to many of my Excel templates. It's helping me keep ahead of my workload.

Thank you in advance.

Matthew

 

[Rudi]

Do you know if the the position of the sheet in the workbook is fixed? IE: Is it always the first sheet (irrespective of name)?

If it's always in the same position in te workbook, you can use an index to get the data for that sheet (IE: The first sheet).

The code below adds an Index and filters for the first sheet and extracts its data; so there is no need to know the sheet name at any point.
This can be adapted to extract the data if its always in the second sheet, or third....

let
    Source = Excel.Workbook(File.Contents("C:\BlahBlahBlah\Book1.xlsx"), null, true),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Index] = 0)),
    #"Get Data" = #"Filtered Rows"{[Index=0]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Get Data", [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

[Bill Szysz]

I assume, there is only one sheet starts with "EFT_EFTA_ALLTRANS". If I am right, use the code below (or similar to this below)

let
    Source = Excel.Workbook(File.Contents("Your_path_to_file"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Sheet") and Text.StartsWith([Name], "[COLOR=#3E3E3E]EFT_EFTA_ALLTRANS[/COLOR]")),
    ProperSheet = #"Filtered Rows"{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(ProperSheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

[MSmith]

Two excellent answers

Hello,
Thank you for your answers.

Rudi, your answer worked perfectly. I just replaced my blue line from the original post with your 3 rows starting with #"Added Index"

Bill, I tried your method as well. I can see where in some situations it might be better to do a partial match of the worksheet name. I did the same as with Rudi's method. It took a couple of tries - I missed some detail at first - but it works perfectly as well.

Thank you both for your fast, excellent responses!

Now I've got to work some more on my VBA skills to adjust Ken's parameters macro to more of my templates.

Cheers!

Matthew

 

[MSmith]

Bill,

Just a note about your method - since people are using so many versions of Excel. I found that it works from my home computer, but not at work. Work uses the same version of Excel, but is on a slower release cycle. I suppose it will function at work in a few months. I will be glad to have partial match functionality there!

At work, Excel throws an error about the [Kind] portion of the code.

Thanks again,

Matthew