VBA While Loop Exiting Early

Question

I am looping through a folder of .csv files and running another macro on each of them. However despite there being multiple files in the folder the loop always ends after the first iteration. Does anyone know the solution to this?

Here is the first macro which just loops through the files in the folder

Sub looper()
    Dim root, workbookname As Long
    Dim csvName As Variant
    
    root = ThisWorkbook.Path
    csvName = Dir(root & "\CSVs\")
    
    While csvName <> ""
        csvName = Left(csvName, Len(csvName) - 4)
        Call get_data(root, csvName)
        csvName = Dir 'set the csvname to the next csv
    Wend
    
    '***THE LOOP BREAKS OUT AFTER THE FIRST ITERATION***
    
    MsgBox ("Finished Looping Through CSVs - Trends Saved in 'Trends' Folder of Same Directory")
    ActiveWorkbook.Close

End Sub

This macro scrapes the data from the csv into the open workbook and does various formatting measures.

Sub get_data(path_root, workbookname)

    Dim CSV_path, pathname As String
    Dim TRD_wb As Workbook: Set TRD_wb = ThisWorkbook
    Dim CSV_wb As Workbook
    Dim lrow As Long
    
    Application.ScreenUpdating = False

    'delete any old data in the template
    ActiveSheet.Rows(4 & ":" & ActiveSheet.Rows.Count).Delete
    
    CSV_path = path_root + "\CSVs\" 'get path for csvs folder
    '*** CHANGE THIS FOR THE LOOP FOR EACH CSV ***
    CSV_name = Dir(CSV_path & workbookname & ".csv")
    
    Workbooks.Open (CSV_path & CSV_name)
    Set CSV_wb = ActiveWorkbook

    Call sort_delete_format_csv 'format the csv data
    'Application.Wait (Now + TimeValue("0:00:10"))
    
    
    'get data from csv
    Range("A5:J5").Select 'select first row of data
    Range(Selection, Selection.End(xlDown)).Select 'select all rows to the bottom of sheet
    Selection.Copy
    
    TRD_wb.Activate 'select the template sheet
    ActiveSheet.Paste Destination:=Worksheets("Trends").Range("A3") 'paste the data in
    
    'copy pasted titles over from the csv
    CSV_wb.Activate
    Range("e4:j4").Copy
    TRD_wb.Activate
    ActiveSheet.Paste Destination:=Worksheets("Trends").Range("E1")
    
    CSV_wb.Close savechanges:=False 'close csv without saving so raw data is left uncorrupted in case of error
    
    With ActiveSheet
        lrow = Cells(Rows.Count, 1).End(xlUp).Row 'find the number of the last row
    End With
    
    Range("k3:n3").Select
    Selection.AutoFill Destination:=Range("K3:N" & lrow) 'autofill the in cell functions for graph
    
    
    'save with name of csv in folder- datestamp folder?
    'loop for all csvs
    Application.ScreenUpdating = True
    
    pathname = path_root & "\Trends\"
    ActiveWorkbook.SaveAs pathname & workbookname, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
End Sub

`get_data` starts its own `Dir()` enumeration. That breaks the parent enumeration, like explained in the duplicate. — GSerg, Aug 04 '20 at 13:13

score 2 · Accepted Answer · answered Aug 04 '20 at 13:13

2

You have another Dir call inside your get_data function.

Dir only has one state for your whole script. You cannot nest loops that use Dir, because the inner call will erase the previous state and set a new one!

What you can do is write all the filenames into an array at once, and then loop over that array.

answered Aug 04 '20 at 13:13

CherryDT

25,571
5
49
74

He could also concatenate the parts together in the subroutine instead of using Dir again. – SeanC Aug 04 '20 at 13:19

VBA While Loop Exiting Early

1 Answers1