How to use multiple Directories using VBA


New Member
Sep 29, 2015

I am trying to write a code that will go in and compare 2 statements together and see if they are formatted correctly. To do this it requires me to look in two different folders. I was able to make one directory and retrieve files from it. But when I added a second directory, my macro is off because now my original directory has been replaced by the second directory so now my macro is looking in the wrong folder. I want to be able to look in one directory to find a comparable statement and then go back to the original folder. I saw online that making a collection would be able to help solve this problem but I am having trouble getting started. Any suggestions or tips would be great!

Running on Windows 7 , Excel 2013

Sub Pull_In_Data(DataName As String)

Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
.Title = "Select the folder with investor statements"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With

FormatSecondaryMsgBox = MsgBox("Would you like to compare the partner cash flow summary with a past quarter?", vbYesNo)

If FormatSecondaryMsgBox = vbYes Then

Set FldrPicker2 = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker2
.Title = "Please select the prior quarter investor statement folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
mypath2 = .SelectedItems(1) & "\"
End With

FortmatSecondary = True

End If

'In case of cancel
myPath = myPath
If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
myExtension = "*.xls"

'Target Path with Ending Extention
Myfile = Dir(myPath & myExtension, vbDirectory)

'Loop through each Excel file in folder
Do While Myfile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(FileName:=myPath & Myfile)

'Run Footing application
Application.Run "RunFootingMain"

'Incase the investorstatement formats have changed this will activate
If FormatWrong = True Then
ContinueMsgBox = MsgBox(Myfile & " is not formmated correctly, process was stopped. Would you like to continue with the other investor statements?", vbYesNo)
If ContinueMsgBox = vbNo Then
Exit Sub
End If
End If

Application.Run "FindCapStatement", Myfile, myExtension, mypath2

' Myfile = Dir(myPath & myExtension)

'End If
'Save and Close Workbook
wb.Close SaveChanges:=True

'Get next file name
Myfile = Dir(, vbDirectory) '< this directory needs to be same path as mypath

'Message Box when tasks are completed
MsgBox "Task Complete!"

'Reset Macro Optimization Settings
Application.EnableEvents = True
'Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Sub FindCapStatement(Myfile As String, myExtension As String, mypath2 As String)

myfile2 = Dir(mypath2 & myExtension, vbDirectory)

Do Until Right(myfile2, 17) = Right(Myfile, 17)
'Keep searching until Macro finds same investor with indentical MCP
'Grab next file name
myfile2 = Dir '<-- I need this directory to only look in mypath2

If myfile2 = "" Then
FormatSecondary = False
Exit Do
End If


End Sub

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce


MrExcel MVP
Oct 15, 2007
The Dir function loses context when you call it with different directories in a nested loop.

A simple solution is to use a separate Dir loop to first store the file names in a Collection, and change the first (outer) Dir loop to loop through the Collection instead.

    Dim MyFile As Variant
    Dim fileNamesCollection As Collection
    Set fileNamesCollection = New Collection
    MyFile = Dir(myPath & myExtension, vbDirectory)
    Do While MyFile <> ""
        fileNamesCollection.Add MyFile
        MyFile = Dir

    For Each MyFile In fileNamesCollection
        'Set variable equal to opened workbook
        'Set wb = Workbooks.Open(Filename:=myPath & MyFile)
        'Rest of your code inside loop
and delete these lines:
'Get next file name
      Myfile = Dir(, vbDirectory) '< this directory needs to be same path as mypath
Please use CODE tags - the # icon in the message editor.
Last edited:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...