Dir Function Broken Loop

[rodrigo_m_almeida]

Good afternoon,

My loop is being broken after using the dir function in search file 02
Files are opened but the repetition end...

Does anyone have any idea how I can resolve this ?

Public Sub ABC123()

    ' Optimize
        Application.ScreenUpdating = False: Application.DisplayAlerts = False
    ' Declare Variables
        Dim xPathName, xFileName01, xFileName02 As String, xNewWB, xOldWB As Workbook, xLine As Double
    ' Browse Folder
        Set FileDialog = Application.FileDialog(msoFileDialogFolderPicker)
        FileDialog.Title = "Select Folder :"
        If FileDialog.Show = -1 Then xPathName = FileDialog.SelectedItems(1) Else Exit Sub
        If Right(xPathName, 1) <> "\" Then xPathName = xPathName + "\"
    ' Search File 01
        xFileName01 = Dir(xPathName & "ZEROS*.xls")
        Do While xFileName01 <> ""
    ' Open File 01
            Workbooks.Open Filename:=xPathName & xFileName01
            Set xOldWB = ActiveWorkbook
    ' Search File 02
            xFileName02 = Dir(xPathName & "DIG*" & Mid(ActiveWorkbook.Name, 15, 2) & ".csv")
    ' Open File 02
            Workbooks.Open Filename:=xPathName & xFileName02, Delimiter:=";", Local:=True
            Set xNewWB = ActiveWorkbook
    ' Copy And Paste
            xOldWB.Activate
            Sheets(1).Range("A2:K" & Range("A2").End(xlDown).Row).Copy
            xNewWB.Activate
            Sheets(1).Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial
    ' Save File 01
            xOldWB.SaveAs Filename:=xPathName & xFileName01, FileFormat:=xlWorkbookNormal
    ' Close File 01
            xOldWB.Close
    ' Save file 02
            xNewWB.SaveAs Filename:=xPathName & xFileName02, FileFormat:=xlCSV, Local:=True
    ' Close File 02
            xNewWB.Close
    ' Next File
            xFileName01 = Dir
        Loop
    ' Optimize
        Application.ScreenUpdating = True: Application.DisplayAlerts = True
       
End Sub

Files Names

 

[GWteB]

Within the same loop you call the Dir function for a second time with an argument. This will lose the specification of the first call.***
Apparently there's just one file name that matches the specification of the second time Dir is invoked, resulting in an empty string being returned, so your loop ends at that point.

Perhaps the second call isn't necessary at all, that depends on whether there also could be files named (for example) DIG01.csv or DIG_____01.csv.
See whether this works for you, by replacing this

xFileName02 = Dir(xPathName & "DIG*" & Mid(ActiveWorkbook.Name, 15, 2) & ".csv")

by this

xFileName02 = xPathName & "DIG_" & Mid(ActiveWorkbook.Name, 15, 2) & ".csv"

***
The Dir function returns the first file name that matches the - as argument passed - pathname.
Additional file names that match are returned when Dir is invoked with no arguments.
An empty string is returned when no more file names match.