Extract sheet date from only specific excel sheet

Question

I wanted to know if it is possible to extract date from only a specific sheet?

I have a folder of several excel file and I want to extract only the sheets between the sheet named "TTP" and the other sheet "TTL". it is not about the position of these sheet but only it these sheets are between these 2 sheet (TTP and TTL as example).

It is possible?

I tried to create a little power query code, but I just found a code who choose only sheet with a special name. But I am looking for the "between these 2 sheet".

Welcome to Stack Overflow. Google Sheets and Microsoft Excel have different feature sets and will most likely require a different answer each. The [google-sheets] tag description explicitly states "Do NOT use with [excel]" and the [excel] tag description explicitly states "Do NOT use with other spreadsheet software like [google-sheets]." Choose one platform and edit tags appropriately. — doubleunary
– doubleunary, Commented Feb 12, 2023 at 11:14

horseyride · Accepted Answer · 2023-02-13 11:25:36Z

0

Try this in powerquery

let Source = Excel.Workbook(File.Contents("C:\temp\a.xlsx"), null, true),

start=List.PositionOf(Source[Name],"TTP"),
TabsToKeep=List.Range(Source[Name],start+1,List.PositionOf(Source[Name],"TTL")-start-1),
Filtered= Table.SelectRows(Source, each List.Contains(TabsToKeep, [Name])),

List = List.Union(List.Transform(Filtered[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(Filtered, "Data", List,List)
in  #"Expanded Data"

then right click and remove extra columns

EDIT For multiple files, steps spread out for easy follow

let Source = Folder.Files("C:\temp3"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetFileData", each Excel.Workbook([Content],true)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each List.PositionOf([GetFileData][Item],"TTP")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each List.PositionOf([GetFileData][Item],"TTL")),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.2", each List.Range([GetFileData][Item],[Custom]+1,[Custom.1]-[Custom]-1)),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.3", each let find = [Custom.2] in Table.SelectRows([GetFileData], each List.Contains(find, [Item]))),
#"Expanded GetFileData" = Table.ExpandTableColumn(#"Added Custom4", "Custom.3", {"Data", "Hidden", "Item", "Kind", "Name"}, {"Data", "Hidden", "Item", "Kind", "Sheet"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded GetFileData",{"Content", "Hidden", "Item", "Kind", "GetFileData", "Custom", "Custom.1", "Custom.2"}),
List = List.Union(List.Transform(#"Removed Columns"[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", List,List)
in  #"Expanded Data"

edited Feb 13, 2023 at 11:25

answered Feb 12, 2023 at 11:29

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PMDSMW Over a year ago

Hello, thanks but your code is only for 1 Excel file. It is possible to adapt your code for a folder of several excel file ? thank !

horseyride Over a year ago

see edited answer above for multiple files in folder

Collectives™ on Stack Overflow

Extract sheet date from only specific excel sheet

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related