Specify FolderPath from user-supplied value in cell

I need to pull information from hundreds of spreadsheets into a summary file. I've found code to do it, and it works, but i'd like to tweak it so whoever is using the macro can specify the folder location of the source files rather than have this coded (since the file location may change depending on the dataset).

The source code is below (from microsoft site: https://msdn.microsoft.com/en-us/lib...ice.14%29.aspx)

Sub MergeAllWorkbooks()
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim NRow As Long
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    
    ' Create a new workbook and set a variable to the first sheet. 
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    
    ' Modify this folder path to point to the files you want to use.
    FolderPath = "C:\Users\Peter\invoices\"
    
    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1
    
    ' Call Dir the first time, pointing it to all Excel files in the folder path.
    FileName = Dir(FolderPath & "*.xl*")
    
    ' Loop until Dir returns an empty string.
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set WorkBk = Workbooks.Open(FolderPath & FileName)
        
        ' Set the cell in column A to be the file name.
        SummarySheet.Range("A" & NRow).Value = FileName
        
        ' Set the source range to be A9 through C9.
        ' Modify this range for your workbooks. 
        ' It can span multiple rows.
        Set SourceRange = WorkBk.Worksheets(1).Range("A9:C9")
        
        ' Set the destination range to start at column B and 
        ' be the same size as the source range.
        Set DestRange = SummarySheet.Range("B" & NRow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)
           
        ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value
        
        ' Increase NRow so that we know where to copy data next.
        NRow = NRow + DestRange.Rows.Count
        
        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False
        
        ' Use Dir to get the next file name.
        FileName = Dir()
    Loop
    
    ' Call AutoFit on the destination sheet so that all 
    ' data is readable.
    SummarySheet.Columns.AutoFit
End Sub

As a novice to VBA novice, how can i modify this so instead of FolderPath = "C:\Users\Peter\invoices\", it would use the folder location specified in cell "B5" of "sheet 1" for example? (ie., where B5 = C:\Users\Peter\invoices\ or any other link).

Thanks for any suggestions.
Jonny

Hi Stephen, thanks. Your first option would have been preferable, but it doesn't seem to work (i correct what i assumed was a typo, FolderParth to FolderPath).

It runs with no error message, it would create the new worksheet, but no data was entered from the source data files. On occasion it opened the wrong folder, not the one I specified in B5 (in this instance, it would open the first excel file from that folder, but the macro quit because it couldn't find the correct worksheet to extract data from.) I have no idea why it defaulted to that directory, it was not specified anywhere.

It didn't seem to matter if i specified the folder location in B5 as z:\Folder\subfolder\ or the network location (e.g., \\server\folder\subfolder\), or if the path was a hyperlink or not (excel automatically turned the network location into a hyperlink). I could not get it to run with the macro and folder of data files saved to the C drive either.

Any ideas?

Although I tried your second option, it's obviously not as straightforward as pasting my code into the spot where you indicate "your code here", which failed with errors. Trying myself to debug got me nowhere.

Hi Stephen,

I'm at home so I created new files to test with. I made 5 test data files with data in A9:C9, put them in c:\Temp\, then created the workbook specifying the folder c:\Temp\ in B5 and a macro using the original code and substituting the following:

 FolderPath = Sheets("Sheet1").Range("B5").Value

Upon running the macro, it pulled data, but it did not go to c:\Temp\. It pulled info from all the excel files in the "My Documents" folder instead (c:\users\home\documents\). It pulled the correct values from A9:C9 from those documents, but they're obviously not the right documents. That's similar to what happened this afternoon when it opened a file in the wrong folder, but quit because the tabs in that file were renamed (no Sheet1).

When I ran the “merged code” option, the macro pulled the correct information from the c:\Temp\ location I browsed to. However, it only worked because there are only excel files in there. When I ran it after browsing to my desktop, it stopped with errors when it tried to open all the other filetypes on the desktop. I have tweaked the following line and it seems to work now:

Original: 
StrFile = Dir(Path & "*")

Revised: 
StrFile = Dir(Path & "*.xl*")

One other issue: this macro does not return the filename in column A as the original does. This is odd, because it appears the same line of code is used in both the original and merged version. Perhaps it is not referenced properly in the merged-code version?

SummarySheet.Range("A" & NRow).Value = FileName

It’s about 90% there, although I do need the filename in column A since with pulling from ~200 files every time, I want to know where the data came from.


The only problem with browsing for the folder location (versus specifying in cell b5) is at work, the files are about 10 folders deep so it takes a long time to browse for files each time you want to run the macro. Whereas you can quickly paste a shortcut with the other code (if it actually worked!). I could probably change the file location though.

Use this.

FolderPath = ThisWorkbook.Sheets("Sheet1").Range("B5").Value

Or put FolderPath-line above the Set SummarySheet-line and then use.

FolderPath = Sheets("Sheet1").Range("B5").Value

Hey Bakerman, tested both of those mods, and the code works! Nice! The scary thing is I actually understand the subtle difference and why it now works.

Cheers to both you and Spitfireblue for the help!
Jonny

The scary thing is I actually understand the subtle difference and why it now works

That really feels good, doesn't it.

Thanks to both of you for the rep.