• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sheetname

Marc L

Excel Ninja
As here it's the Excel section I think it would be very difficult with some Excel formula …​
But I let the formulas advanced, experts, gurus, masters - whatever - first answer to this question​
and if it's really no - even if I have an idea but as you forgot to precise what is your Excel version - a moderator​
will move this thread to the VBA section where an easy very beginner code will do the job​
but only once you will have well explained your need 'cause after all, we are still not mind readers …​
 

nivanyameha

New Member
How To Generate A List Of Sheet Names From A Workbook Without VBA, pls check

 

GraH - Guido

Well-Known Member
Alternative to vba or the macro 4 functions, is using a PowerQuery approach. (Not for mac, from 2010 version onwards only)

I've used a technical sheet, which can be hidden from the solution, to store a formula in a named range to return me the filepath in a dynamic way.
That is used in a first Query, so I can use the vFilePath in the final query
Code:
let
    Source = Excel.CurrentWorkbook(),
    vFileName = Source{[Name="vFileName"]}[Content],
    Column1 = vFileName{0}[Column1]
in
    Column1
The final query then outputs a table with all sheetnames using the vFilePath.
Code:
let
    Source = Excel.Workbook(File.Contents(GetFileName), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Sheet")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name"})
in
    #"Removed Other Columns"
Both queries are made using simple mouse clicks. All I needed to do is replacing the hardcoded file path by the variable in the second query. I made that one starting from Data - New Query - From File - From Workbook.

New sheets? In the table, right click, then refresh -> done :)

The trick to make this work, is setting the privacy setting in the Query Options to "Always Ignore". Without this set, the query throws errors at you. That got me puzzled for a couple of days.:rolleyes: What would we do without internet?
 

Attachments

Peter Bartholomew

Well-Known Member
When you analyse the Macro 4 solutions being proposed, it appears that each cell involves two calls to GET.WOOKBOOK(1) and for each the search is conducted over every sheet name to return the length of the workbook name. So 1000 sheets would require 2000 function calls and a million text searches.

Using an array formula, that reduces the calculation to a single dynamic array, involving two function calls,
= SEARCH( "]", @ WorkSheets )
= TRANSPOSE( MID( WorkSheets, n+1, 255 ) )

and only one text search against the first sheet name.

So, unless someone can spot an error in my logic, the time savings would be
1) Rendering the result within a worksheet range - no change
2) Function calls - x1000
3) Character search - x1000000

Any thoughts?
 

Marc L

Excel Ninja
Comparison demonstrations …​
Code:
Sub Demo1()
      Dim S$(), L&
    ReDim S(1 To Sheets.Count, 0)
    For L = 1 To UBound(S):  S(L, 0) = Sheets(L).Name:  Next
    [N1].Resize(UBound(S)) = S
End Sub
Or like a beginner :​
Code:
Sub Demo0()
    Dim R&
    For R = 1 To Sheets.Count:  Cells(R, 14).Value2 = Sheets(R).Name:  Next
End Sub
 

Marc L

Excel Ninja
I totally agree with you, that's the reason why in post #2 I did not ever answer with any VBA solution.​
Post #10 is an answer to Peter asking for a PQ vs VBA comparison I suppose …​
 

vletm

Excel Ninja
Marc L
... but of course,
if few days later, there is still none replies - then it would be suitable to offer even other kind of solution.
There are many times many ways to solve thread.
 

Marc L

Excel Ninja
When a forum has different sections like here, the Logic wants to respect the forum structure …​
Of course if an Excel question here really can't be solved with some Excel features - including formulas - then​
obviously a VBA way could be first asked to the OP and only if it's answer is positive then​
any helper can post a code and a moderator moves the thread to the VBA section.​
The common 'issue' is often helpers don't think an Excel way exists or think it's easier via VBA​
so post some code without waiting for any helper with an Excel way !​
As waiting at least for 24h - even more the week-end - lets the time to other helpers to answer.​
And better first post the question if the OP is mind free for a PQ or a VBA solution​
thus in particular when the OP after first answers didn't give any news, avoiding to waste your time, like here …​
 
Top