Paste multiple folder/file paths into excel using VBA

Hi,

My goal is to be index/matching into multiple files across multiple folders to pull in data into a master file. However, I won't be doing that manually and I was thinking I can use VBA to extract the full folder/file path from a specific folder and paste the path into a column into excel.

I'm not sure if this is possible. Maybe I would have to loop through each file in the folders and grab the name and paste file name into excel. Closed that wkbook and loop onto next workbook and repeat the process. - It seems this process would take to long. Is there a way to do this all at once without opening up the files that I'm trying to get the information of?

Would anyone know how to call multiple folder/file paths and instruct the code to paste those paths into excel?

Here's a free add-in that does that.
RDBMerge, Excel Merge Add-in

Here's the source code if you want to do your own.
Merge data from all workbooks in a folder


List the Files in a Folder and SubFolders

Originally Posted by sherlucky

Thanks for the help guys.

AlphaFrog, the addon works great! Is there a way for me to add brackets around the filepath, when it is embedded with the foldername?

You're welcome.

brackets around the filepath, when it is embedded with the foldername

I don't know what that means.

Originally Posted by sherlucky

Alphafrog,

Typically, when I link to spreadsheet in a network drive, I see the below:

='X:\Reports\eport\Account\2016\08\[08232016_POSITION_08242016_084619.xls]Cash Position'!$AF$12

As you can see, the file name is in brackets as it is being linked to a specific worksheet in that workbook. The addon you linked, does not bracket the file name.

So I was wondering whether there was a way to do just that.

When you say "Addon" are you referring to the code at this link?
List the Files in a Folder and SubFolders