Last week, I illustrated how to transfer data from various fields within multiple spreadsheets into a single master repository. Today, I will demonstrate how to capture the file directory for the data that has been ascertained into the master file.
I will use the macro from last week as a reference point and I will add some code to that. The folder path could be ascertained using the msofiledialogfolderpicker as shown below:
The additional codes have been checkmarks. I have indicated cell A1 of the second tab (i.e. Sheet2) as the location in which I want the folder path to be conveyed. The entire macro code is enclosed below:
Once the macro is executed, first it will prompt for the selection of files as explained in my previous blog post. But this time, a second window will pop up that will ask for the folder from which the files are being captured.
After selecting that file, the folder path will be captured in the desired tab/cell of the spreadsheet. Here is the desired outcome in the “sheet 2” tab.