Get a list of worksheets that works in web and desktop without VBA

I need to build a list of worksheet tabs in a workbook that keeps updated. I need it to work with the web, but the environment is locked down, so I can't use VBA.

I use this list in the Dropdowns worksheet, cell C21, to filter the ones I want in E21, get some data in F21 and then fill in the !Data worksheet.

On my desktop, I have used either of these Names formulas:

Formula:

=MID(GET.WORKBOOK(1)&T(NOW()),FIND("]",GET.WORKBOOK(1)&T(NOW()))+1,255)

Formula:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")&T(NOW())

But on the web, I get "#NAME?" and no Name Manager or Defined Names option (which the Help says to use) in the Formulas tab.

So, the solution to fix it from the MS KB doesn't work:
Excel \ Formulas and functions \ Errors \ How to correct a #NAME? error


Is there another option, or do I need to forget Excel and use something better?

Regards,
Mike

I think I've found a way around the problem using a Power Query that exposes the workbook metadata. Will do some more testing during the week unless anyone replies with a more elegant solution...


Regards,
Mike

Originally Posted by MikeInAu

Power Query that exposes the workbook metadata

That sounds promising. I have not used PQ and am not familiar with its functionality available in the web version.

Originally Posted by 6StringJazzer

That sounds promising. I have not used PQ and am not familiar with its functionality available in the web version.

Hi Jeff,

Aside from a warning on opening the file via the web that it might be unsafe, it seems to be working on both platforms. One odd thing - you have to disable OneDrive to set it up but must have OneDrive enabled for it to update.
Annoying that Microsoft doesn't have a simple function to pull a list or array of sheet names that can then be filtered... I've seen so many questions and solutions that don't quite work in my searches.

I'll try to post back with my solution once I've fully tested it.

Mike

There is no way to get a list of worksheets (tabs) automatically without using some kind of macro. The web version of Excel does not support macros. Your formula uses GET.WORKBOOK. That is not a VBA macro, but it is an old Excel 4.0 macro, which also is not supported on the web version, and that is why you are getting a NAME error. Excel does not recognize the name GET.WORKBOOK.

The web version has its own macro language Office Script, which can probably do this. I think it is available only on certain versions--when I bring up it up, it does not have the Automate tab on the Ribbon. I think you might need an enterprise license.

The drawback of using Office Script for the web version is that your desktop version may not support it.

In PQ it is just Get Data --> From file and then transform

One click (select "Name" columns) and you are done

PHP Code: 

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\Book1.xlsm"), null, true),
    fltr_sheets = Table.SelectRows(Source, each ([Kind] = "Sheet")),
    sheets_names = Table.SelectColumns(fltr_sheets,{"Name"})
in
    sheets_names 


Originally Posted by JEC.

In PQ it is just Get Data --> From file and then transform

One click (select "Name" columns) and you are done

JEC,

Is there a way to make the PQ refer to the file when the file name changes?
Also, to not refer to the local path so the file can be shared?

Thanks,
Mike

Originally Posted by JEC.

The best way is to use Get Data --> From File --> From Sharepoint Folder
Furthermore, choosing the variable filename is possible while creating the query.

If you don't have this "From Sharepoint Folder" option, you could go for Get Data From Web. Use the sharepoint URL.
Not bound to a local path but not so easy to select the dynamic filename.

Hi JEC,

I get it mostly working via OneDrive with this query:

let
    Source = Excel.Workbook(Web.Contents("https://domain-my.sharepoint.com/personal/user_domain/Documents/TfNSW/ValueCreationFramework-Calculator_v9.xlsm"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each [Kind] = "Sheet"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each Text.StartsWith([Name], "Item ")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Name", "ItemsListPQOD"}})
in
    #"Renamed Columns"

But, it fails when the filename is changed.

I do have a Sharepoint subscription... but not sure how to get the variable into the query:

let
    Source = SharePoint.Files("https://domain-my.sharepoint.com/personal/user_domain/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "TfNSW") and Text.StartsWith([Name], "ValueCreationFramework-Calculator"))
in
    #"Filtered Rows"

The last query only shows the various files, rather than the sheets in my file.

I did a test with Power Query and after creating a new sheet with the sheet names I saved the workbook and reloaded the query, then 3 new lines emerged.
The red one isn't a sheet name, would it be a function?

Excel 365 (Windows) 64 bit

	A
1	Name
2	Introduction
3	Value Drivers
4	Item (1)
5	Item (2)
6	Item (3)
7	Item (4)
8	Item (5)
9	Scorecard
10	pRankBy2
11	Dropdowns
12	!Data
13	Sample Qualitative Metrics
14	pRankBy2_1
15	_xlnm._FilterDatabase
16	DadosExternos_1
17	pRankBy21

Sheet: pRankBy2

So I found that you need to filter first to load only sheet names.

Attachment 854813

By the way, the exact code that JEC pointed in message #6 (sorry for not noticing...)

I still have a bit more testing to do, but it seems that JFC's idea might solve the problem.

Hey JFC, how do I link to a list of the sheets?

Originally Posted by JEC.

What are you trying to do with "linking" sheets. Are you trying to get data from these sheets? Can you give more info?

Does the attached help?

Originally Posted by JEC.

What are you trying to do with "linking" sheets. Are you trying to get data from these sheets? Can you give more info?

Still trying to work out the links, so they work ...

VCF v9 - Sharepoint or OneDrive

Originally Posted by DJunqueira

The best solution would be to import any file inside the folder and if they all follow the same structure use PQ to filter the data.

The issue is that it's a single workbook with multiple sheets.

I want to be able to read all the sheets beginning with "Item..."

Originally Posted by DJunqueira

For this you can go for the first "action" of PQ and filter, very simple.
Could you prepare a good sample, so I can make a video for you?

Simply, I want it to get the list of sheets from the current workbook in a way that doesn't break if I save_as to a different path or filename.

I can use OneDrive or Sharepoint, if that helps?

Currently using the OD query:

Source = Excel.Workbook(Web.Contents("https://domain-my.sharepoint.com/personal/user_domain/Documents/TfNSW/ValueCreationFramework-Calculator_v9.xlsm"), null, true),

JEC indicated that dynamic variables for the filename are possible (but easier with sharepoint), here:
https://www.excelforum.com/excel-for...ml#post5907041

Thanks,
Mike

Solved it!!!

I created a hidden and named cell with this formula:
= SUBSTITUTE(TEXTBEFORE(CELL("filename"),"]"),"[","")

Then used this query:

let
    FilePath = Excel.CurrentWorkbook(){[Name="VCFfile"]}[Content]{0}[Column1],
    Source = Excel.Workbook(Web.Contents(#"FilePath"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each [Kind] = "Sheet"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each Text.StartsWith([Name], "Item ")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Name", "ItemsListPQOD"}})
in
    #"Renamed Columns"

Damn, Microsoft made this difficult! Considering they still have the Excel 4.0 macro - GET.WORKBOOK(1) - built-in, you'd think they could have made this a function so it could be used in templates that can be shared with others.

Bugger!

The CELL("filename") function doesn't work in web.

And, even if I manually put in the field info, it can't refresh.

Looks like it's not quite possible.

Originally Posted by DJunqueira

Are you aware that you have to follow an order of queries and functions inside of Power Query to all go as planned, aren't you?

I wasn't... but, as I said, the issue (only with web) is before PQ is used. It's with the cell that gets the path, using the function =CELL("filename") -- This is fine on the desktop, but the "filename" part of the CELL() function doesn't work with Web.

Originally Posted by DJunqueira

You can get images from internet with IMAGE() function, but can't get a file?
Something wrong, stop insisting with CELL(), this is an old function.

Can you recommend a better function?

As a result, I need a list of the workbook tabs that I can filter and sort. It should automatically be updated and work from a desktop or the web. It can't use VBA or macros due to corporate lockdowns.

Originally Posted by JEC.

You can get the filename with Office scripts in Excel webversions but you have to run this script before running the Query. Not something that I would advice.
Still I would retrieve your worbook with the "From Sharepoint Folder" connector. Give your file a flag which will allow you to select the most recent file in Power Query

Can the Sharepoint folder be a variable that works if the file is moved?