+ Reply to Thread
Results 1 to 7 of 7

Paste multiple folder/file paths into excel using VBA

  1. #1
    Registered User
    Join Date
    07-13-2012
    Location
    Carteret, NJ
    MS-Off Ver
    Excel 2010
    Posts
    14

    Unhappy 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?
    Last edited by sherlucky; 08-24-2016 at 03:58 PM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,596

    Re: Paste multiple folder/file paths into excel using VBA

    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
    Last edited by AlphaFrog; 08-24-2016 at 04:00 PM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    08-18-2016
    Location
    Earth
    MS-Off Ver
    2010 and 2013
    Posts
    64

    Re: Paste multiple folder/file paths into excel using VBA

    I got this code from: http://software-solutions-online.com...ubdirectories/

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-13-2012
    Location
    Carteret, NJ
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Paste multiple folder/file paths into excel using VBA

    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?

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,596

    Re: Paste multiple folder/file paths into excel using VBA

    Quote Originally Posted by sherlucky View Post
    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.

  6. #6
    Registered User
    Join Date
    07-13-2012
    Location
    Carteret, NJ
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Paste multiple folder/file paths into excel using VBA

    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.

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,596

    Re: Paste multiple folder/file paths into excel using VBA

    Quote Originally Posted by sherlucky View Post
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 10-20-2014, 07:25 AM
  2. Not sure this is possible with folder paths and macros
    By jh51745 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-13-2014, 08:27 PM
  3. Copy Paste data from Multiple excel to single excel in the same folder
    By Sreeram1686 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-24-2013, 03:16 PM
  4. search for a file in a folder based on data, copy and paste it in another folder
    By kboy1289 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-05-2013, 11:47 AM
  5. Replies: 1
    Last Post: 09-22-2010, 11:00 AM
  6. [SOLVED] excel file paths
    By Jane in forum Excel General
    Replies: 1
    Last Post: 08-17-2006, 12:05 PM
  7. Replies: 1
    Last Post: 10-17-2005, 04:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1