How to use multiple Directories using VBA

[Seba5404]

Hello,

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
NextCode:
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
Loop


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


ResetSettings:
'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


Loop




End Sub

 

[John_w]

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
    Loop

    For Each MyFile In fileNamesCollection
        'Set variable equal to opened workbook
        'Set wb = Workbooks.Open(Filename:=myPath & MyFile)
        
        'Rest of your code inside loop
    
    Next

and delete these lines:

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

Please use CODE tags - the # icon in the message editor.