VBA File Name Loop with Extension and Folder FileDialogFolderPicker
https://vbatutorialcode.com/vba-file-...
The above link has the file available for download, full line-by-line explanation, and full code for copying and pasting if you don't want to download a file from my website.
In this video, I show you how to pull all the file names from a particular folder into an excel workbook. It pulls the file names and the file extensions. Then it assigns a variable to a particular file containing a certain word anywhere in the file name using the SEARCH formula, which I review thoroughly in my European to US dates video on Youtube.
Secondly, I have the application.filedialog(msofiledialogfolderpicker) open up. This filedialogfolderpicker lets you select which folder you want to open that file from. It then assigns that folder path to a second variable.
Lastly, I concatenate the folder path and the file name & file extension into a single variable so that the VBA code can open the file. You can essentially run the path variable first and then grab the file from whichever folder you select and have that file open. So if your file moves around monthly and the name is basically the same but changes every month, then this macro is flexible enough to continue opening the new files month-by-month and run code with those files.
Basically, if you set the fldrpicker before the Module 2 reference in module 1, then you can pull the file names from thefldrpicker that you pick and open that specific file and pull the data from it quicker even if the name is constantly changing without requiring user input and letting the user specifically and visually select the folder where the data is coming from to ensure 100% that the data is being pulled from the right place by the computer. That's how I format the file on my website so it's even more streamlined.