+Aaron Malek asks if there is a way to have a dropdown in Excel that lists all of the files in a folder. Normally, this might require some VBA, but today, a non-VBA solution using Power Query, the new add-in for Excel 2010 and Excel 2013.
Transcript of the video:
The MrExcel podcast is sponsored by “Easy-XL”!
Learn Excel from MrExcel podcast episode 1938 – Excel Dropdowns with File Names from One Folder, Using Power Query!
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Today’s question sent in by Aaron on YouTube: “Wondering if it’s possible to set up a worksheet that has a dropdown list that is linked to a folder, so when you put a new image file in the folder, the dropdown includes that file.” Now initially I thought “Oh, this is going to have to be VBA.” But if you have Excel 2010 or Excel 2013 – 2010 any version with Software Assurance, 2013 Pro Plus or above, and you can download Power Query, this is a great new add-in for Excel, you can solve this problem.
Alright so here’s what I have, I have a folder called C:\Pics and it has a bunch of pictures and a movie, and I want just the picture files available in the dropdown.
So we’re going to come back here, add a new worksheet, and then under Power Query say I want to get the data from a file, and say that I want to get it from a folder, enter the folder name, click OK, alright.
Now, here in the Query Editor we choose which columns we want and don’t want, and really, I just need the name of the file and the extension.
So I can choose this and say Remove Columns, I can choose all of these, click on the first one, Shift-click on the last one, and remove those columns, we got one more, Remove Columns.
And then, I want to keep just the PNGs, JPEGs, and get rid of the AVI, so I’ll open this little filter here, and turn of AVI, that would only get JPEGs and PNGs, click OK.
And then it’s funny, once I’ve done that, I don’t even need this column anymore, so I can remove that column.
And then finally, it doesn’t really matter whether it’s a JPEG or a PNG, so I want to get rid of that data.
So that’s going to be under Transform, Split the Column, By Delimiter, and the delimiter’s going to be a Period which is not in the list so we’ll use –Custom--, period, and At the right-most delimiter.
Click OK, and finally we don’t need that column anymore, so Home, Remove Columns, alright, all good, we’re going to Close & Load.
Alright, now the beautiful thing about Power Query is that it doesn’t just do this once, it remembers all the steps that we needed to get that data.
Alright, so if I would add more files to that folder and then refresh the data, it would automatically update.
But we want to use this result in a Data Validation list, and before I can do that, I need to add a named range.
Even though this is called Query1, I have to add a named range on top of that.
So I’m going to select all of the cells except for the header, and here I’m going to type a name such as MyFiles (no space), press Enter!
See, it actually goes away because it thinks it already has a name.
When we come back here, we will set up Data Validation, say that it comes from a List, and =MyFiles, click OK, and we have a list of all of those files.
Alright, so that’s the first part that is now working, but we need to be able to add more files to that folder and have it automatically update.
OK, so I’m going to drag some more files to the folder, we now have more files, and initially see they are not in the list, it’s still just the original items in the list.
So we have to come back to our query, and then on the Data tab we got to Refresh All, and say Connection Properties.
We have a couple of choices, first one is every time that we open this file, we’ll go back out to that folder and refresh the data.
If it’s a kind of a thing where someone’s going to be adding new files to this folder, you know, every few minutes, we can actually select that we want to refresh the data every 5 minutes, every 1 minute even, although that might be a little bit annoying, maybe 10 minutes, whatever seems to be making the most sense.
Or, we can always just force the thing to refresh by using Refresh All, so Refresh all goes back out to that folder, see what are the new items there.
So one of those choices is either manually click Refresh All, simply set up the connections that we refresh this every time we open the file, or have it update every 5 minutes, 10 minutes, or whatever.
And because I used a named range there, we should see the larger list of files in the dropdown.
What a great idea from Aaron, and normally this would require some VBA, but luckily now if you have Excel 2010, you could use Power Query and solve this without any VBA at all.
Well hey, I want to thank Aaron for sending that question in, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast episode 1938 – Excel Dropdowns with File Names from One Folder, Using Power Query!
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Today’s question sent in by Aaron on YouTube: “Wondering if it’s possible to set up a worksheet that has a dropdown list that is linked to a folder, so when you put a new image file in the folder, the dropdown includes that file.” Now initially I thought “Oh, this is going to have to be VBA.” But if you have Excel 2010 or Excel 2013 – 2010 any version with Software Assurance, 2013 Pro Plus or above, and you can download Power Query, this is a great new add-in for Excel, you can solve this problem.
Alright so here’s what I have, I have a folder called C:\Pics and it has a bunch of pictures and a movie, and I want just the picture files available in the dropdown.
So we’re going to come back here, add a new worksheet, and then under Power Query say I want to get the data from a file, and say that I want to get it from a folder, enter the folder name, click OK, alright.
Now, here in the Query Editor we choose which columns we want and don’t want, and really, I just need the name of the file and the extension.
So I can choose this and say Remove Columns, I can choose all of these, click on the first one, Shift-click on the last one, and remove those columns, we got one more, Remove Columns.
And then, I want to keep just the PNGs, JPEGs, and get rid of the AVI, so I’ll open this little filter here, and turn of AVI, that would only get JPEGs and PNGs, click OK.
And then it’s funny, once I’ve done that, I don’t even need this column anymore, so I can remove that column.
And then finally, it doesn’t really matter whether it’s a JPEG or a PNG, so I want to get rid of that data.
So that’s going to be under Transform, Split the Column, By Delimiter, and the delimiter’s going to be a Period which is not in the list so we’ll use –Custom--, period, and At the right-most delimiter.
Click OK, and finally we don’t need that column anymore, so Home, Remove Columns, alright, all good, we’re going to Close & Load.
Alright, now the beautiful thing about Power Query is that it doesn’t just do this once, it remembers all the steps that we needed to get that data.
Alright, so if I would add more files to that folder and then refresh the data, it would automatically update.
But we want to use this result in a Data Validation list, and before I can do that, I need to add a named range.
Even though this is called Query1, I have to add a named range on top of that.
So I’m going to select all of the cells except for the header, and here I’m going to type a name such as MyFiles (no space), press Enter!
See, it actually goes away because it thinks it already has a name.
When we come back here, we will set up Data Validation, say that it comes from a List, and =MyFiles, click OK, and we have a list of all of those files.
Alright, so that’s the first part that is now working, but we need to be able to add more files to that folder and have it automatically update.
OK, so I’m going to drag some more files to the folder, we now have more files, and initially see they are not in the list, it’s still just the original items in the list.
So we have to come back to our query, and then on the Data tab we got to Refresh All, and say Connection Properties.
We have a couple of choices, first one is every time that we open this file, we’ll go back out to that folder and refresh the data.
If it’s a kind of a thing where someone’s going to be adding new files to this folder, you know, every few minutes, we can actually select that we want to refresh the data every 5 minutes, every 1 minute even, although that might be a little bit annoying, maybe 10 minutes, whatever seems to be making the most sense.
Or, we can always just force the thing to refresh by using Refresh All, so Refresh all goes back out to that folder, see what are the new items there.
So one of those choices is either manually click Refresh All, simply set up the connections that we refresh this every time we open the file, or have it update every 5 minutes, 10 minutes, or whatever.
And because I used a named range there, we should see the larger list of files in the dropdown.
What a great idea from Aaron, and normally this would require some VBA, but luckily now if you have Excel 2010, you could use Power Query and solve this without any VBA at all.
Well hey, I want to thank Aaron for sending that question in, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
