I'm looking to get a list of all folders on a particular drive, whether or not they have contents. So far I've been using PowerShell, but it's clunky and takes a bit of time.



I tried PowerQuery on a folder, and I get a resulting list of files. I can remove duplicates on the folder path column, but as far as I can tell it won't yield any folders that don't already have files in them.

Am I missing a way?

Thanks,

Ann

Does this help?:
Get the List of FOLDERS only in Power BI using Power Query - RADACAD
Get the List of FOLDERS only in Power BI using Power Query - RADACAD

Thanks, p45cal, I can see that would work, but since I have no other current use for Power BI, I would be installing it for a single use.

What I'd been using thus far is a Powershell command , which works for my purpose, except for having to import the resulting text into Excel and then do a separate parsing to get only the Project number in a separate column next to the full name. Thought maybe PQ could do that more readily, and also be refreshed. All things being equal, I will probably just do the PQ and know I'll have a few missing \Invoices\ subfolders when the first invoices come out (due to their being empty).

No need for PowerBI, Power Query is built in to Excel since Excel 2016 and Excel 2013 can be persuaded to use it.
It's under the Data tab, Get & Transform Data.
Get Data, From File, From Folder.

The attached has such a query. Currently it's looking at C:\Users\Public, you'll need to adjust that to your base folder. Thereafter you'll need do nothing except refresh the query to update the results. You refresh the table in the same way as you refresh a pivot table: eg. right-click the table and choose Refresh.

To change the base folder, alter the first line of the query:

2023-02-17_180603.png

or we could put the base folder in a cell and have the query look at that cell, and that cell could be updated with a tiny macro to let you browse for that base folder.
At the moment I've opted to show only unhidden directories (folders), but that can be much more finely tuned.

Attached Files

msofficeforums50425.xlsx (16.0 KB, 4 views)

Ah, OK, I didn't realize that if it would work in Power BI it would also work in Excel.

I did open your workbook and change the directory. It does give me a nifty listing of just the main project folders on the drive. My wrinkle is that I actually need the subfolders for invoices in each of the project folders. I am able to do that in Power Query, except for the few Invoices files that don't yet have invoices in them. I'm also able to add a column that trims the long folder just down to the project number, which gives me exactly what I need except for those few empty ones. I have tinkered around with the setup you shared, and I can only get it to list the main folders on the drive.

What I do to get the subfolders is get a list of everything, then filter for where the folder path ends in "Invoices", then filter to uniques. Then I duplicate the folder path column and trim to just the project number, giving me the XLOOKUP info that I need to do the "auto-filing" VBA that Paul helped me figure out here (https://www.msofficeforums.com/excel...using-vba.html)

If I'm missing something and there's a way to get into subfolders, I'll keep trying.

Thanks!

Ann

If you've done (or started) this with a powershell command, what was the command, because command you've shown only returns the top level of folders?
Are we talking only one level down of subfolders?


Otherwise I'll have an explore in the coming days.

Yes, for the most part they are usually just one level down. It'd be like \\Projects\\J1234G State Road 567 at Pine Street Intersection\Invoices\

You're right, I think I just grabbed the wrong Powershell command line that I keep in Outlook Notes. That's the one I used to get the names of all the PDFs of the invoices, which I have changed to doing by PQ. The Powershell command that yields literally everything in the main folder (recursive) is: And that yields a lot of rows of data, which I then filter in Excel for anything that ends in "Invoice" and end up concatenating the rest of the file path in.

I suppose the other thing I could do is search out the few ...\Invoices\ subfolders that are empty and place a small dummy file in them?

I think I'm like 90% there, so I'm not that worried about it.

In the attached cell A1 is a named range FPath. Adjust it to your path, refresh the table below. Empty folders included.
Instead of using a named range, the path could be hard-coded into the query.


Stolen from Power Query How to list folder names only NOT files | Page 2 | MrExcel Message Board

Attached Files

msofficeforums50425b.xlsx (17.3 KB, 9 views)

Thank you so much for your determination and your help! I've changed that path in A1, and it's been "refreshing" for about 40 minutes. Granted, I had to open a couple of other workbooks and do other things in the meanwhile so I may be thinning the resources.

If I have to give up on it now, I may set it to refresh while I'm gone overnight and see if I get results.

Update: trying again, and instead of just hitting 'Refresh" on the table, I went into the Query after changing the path in A1. It's still taking a very long time to load. I want to see if I can further filter after your results come through to show only the folder paths that end in *Invoices. I don't think there's a way to use a variable in the A1 path like \projects\*\*Invoices, is there? The first * would be the project folder file name, and the second * would be anything that came before Invoices, in some cases a folder number like "2. Invoices".

How extensive are the folders below FPath?
Are we talking urls, sharepoint, or just the local hard drive?

Maybe, just perhaps… see here: Credentials problem | Excelguru Forums


The path in the named range FPath is used as a top, starting folder, but it doesn't have to be; the query could use it differently, but I don't think it would necessarily speed things up. The query I showed doesn't try to process further the results - I wpould expect the query to be added to for that. It was only meant as a starting point.

It's a rather large shared drive (1.99 TB) with lots of sub-folders and sub-sub folders. The funny thing is that the regular Power Query search from which I filtered for the folder paths that have *Invoices\ in them doesn't take nearly that long, so I guess your method is more complex. Appreciated, but I think it's just not going to be practical. I don't think it's a credentials issue, though.

Yup, last night I pulled it back up before I left and I attempted to add a filter to the results. It had all night to run, but I must have done something wrong as it came up empty. I'm tending to think I'll go with what I have, and if I need to look up a few file paths manually that were empty folders, I'll just keep those results in a file so I won't need to do too much in any month.

A macro instead?

Yes, your method plus a macro or some simple manual filtering would get me mostly there. It's just odd that it processes so much more slowly. Granted, it is a lot of files.