Results 1 to 2 of 2

Thread: How to use multiple Directories using VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2015
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default How to use multiple Directories using VBA


    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

  2. #2
    MrExcel MVP
    Join Date
    Oct 2007
    Post Thanks / Like
    8 Post(s)
    2 Thread(s)

    Default Re: How to use multiple Directories using VBA

    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 by John_w; Feb 18th, 2016 at 07:05 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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