Feb 26 2021 07:17 AM - edited Feb 26 2021 07:21 AM
Hi
IS there a way of copying or exporting a list of the worksheet names so they can be copied and pasted into an overview worksheet at the start of the workbook
Feb 26 2021 07:31 AM
See for example List sheet names with formula
Feb 26 2021 08:53 AM
Feb 26 2021 11:54 PM
@Jude_ward1812 Alternatively, use Power Query to list the workbook contents, applying the following steps in the Advanced editor.
let
Source = Excel.Workbook(File.Contents("FilePath\FileName.xlsx"), null, true),
#"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Sheet")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name"})
in
#"Removed Other Columns"
Replace "FilePath\FileName.xlsx" with the real file path and name of your file.
Step by step:
Save your file;
Connect to the workbook itself (Get Data, From File, From Workbook);
Navigate to the file (i.e. the file you just saved = the one you are still working in);
Select any of the sheets (doesn't matter which one);
Transform;
The PQ editor opens with two applied steps (Source and Navigation). Delete the navigation step;
You are left with an overview of everything in your file (Sheets, Tables, Named Ranges etc.);
Filter all rows where Kind equals "Sheet";
Keep only the "Name" column;
Close & Load to a table in a new sheet to create a list of all sheet names in the workbook.
When you add more sheets, first save the file and refresh the query. The new sheets will be added to your list automatically.
Feb 27 2021 01:19 AM
To combine above two ideas
1) We use =CELL("filename") as named cell within file
2) We generate list of sheets by Power Query
But there are even more limitations:
- that's desktop version of Excel
- this is the file kept on SharePoint site / OneDrive or on synced folder
Script is
let
SourcePath = Excel.CurrentWorkbook(){[Name="filepath"]}[Content],
#"Remove Sheet Name" = Table.SplitColumn(
SourcePath, "Column1",
Splitter.SplitTextByEachDelimiter({"]"}, QuoteStyle.Csv, true),
{"path"}
),
filepath = Table.ReplaceValue(
#"Remove Sheet Name", "[","",
Replacer.ReplaceText,{"path"})
[path]{0},
Source = Excel.Workbook(Web.Contents(filepath), null, true),
#"Keep Sheets" = Table.SelectRows(Source, each ([Kind] = "Sheet")),
#"Only Names" = Table.SelectColumns(#"Keep Sheets",{"Name"})
in
#"Only Names"