Forum Discussion

Jude_ward1812's avatar
Jude_ward1812
Copper Contributor
Feb 26, 2021

Worksheet names

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
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 27, 2021

Jude_ward1812 

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"

Resources