formula to count files in a shared drive folder
Hi everyone,
I have ~50 folders located on a shared drive on the network. Each folder is supposed to have a a spreadsheet loaded into it with varying frequencies (one folder gets a spreadsheet loaded daily, another weekly, another monthly, etc).
Because many other people load these files, I'd like to create a spreadsheet that can - at the very least - count the number of spreadsheets in each folder. Ideally, this tracking spreadsheet could also pull the file name of each spreadsheet in the folder. Ultimately, I'm trying to find a way to track when the most recent spreadsheet was added to each folder. Because I know the expected frequency of adding a new one to the folder, I can flag folders on the tracking spreadsheet when one isn't loaded when its expected to be.
I struggle because most things are typically posted to a SharePoint, which I'm easily able to export, link to, and refresh on the spreadsheet. For irrelevant (read as: dumb) reasons, these files cannot be loaded to a SharePoint.
I've scoured Google, and am under the painful impression that this is potentially feasible, but only with some serious VBA code. My VBA coding skills in Excel are pretty basic, limited to some simple macros and formula creation.
I was just wondering/hoping that maybe I'd missed something, and there was a better way. I hope I described my situation clearly, but if not, please let me know. Thank you for your help a lot. Appreciate it.
Posted Nov 5, 2019, 11:22 PM UTC · edited
Use PowerQuery to get data from a folder. Choose a top level folder and PQ will get all the files from the subfolders. When you have a list of folders you can load the list into Excel. In Excel you can use COUNTIFS to count the number of files. Or you can use the GroupBy feature in PQ to count the number of files. It depends on where you want to do your math.
I just looked a little bit into this and I have never used/heard of the "get and transform" feature in Excel. I'm really excited and going to work with this tomorrow - Thank you SO much for a really good potential solution! Will follow up on this tomorrow!
No problem. You'll love it. You can get the file names, paths, last save date and lots of other file metadata, in addition to data from inside the workbooks.
Solution Verified
You have awarded 1 point to work_account42
^I ^am ^a ^bot, ^please ^contact ^the ^mods ^for ^any ^questions.
Hey! Wanted to provide an update - THANK YOU. This worked exactly how I was hoping it would last night. Played around with it a ton today, and was able to get the metadata I needed. The only thing I wasn't able to figure out was how to pull data within the workbooks themselves. I'll keep messing with it and learning, but if there was an easy way to do that which I'm missing, would love it if you'd share.
Regardless, again, thank you. This has saved me a lot of angst. Really appreciate it.
When you get the data from all the files, there is a column that says 'binary' or something like that. that column will have a small double headed arrow in the header. When you click that it will expand into the contents of each file.
Note that there is a template that is used to import and transform files. If all the files have the same structure, you can use one query. If the files have differing structures, you will need to use several queries.