CODEWITHSUNDEEP
vbaexceldir
squar_o
squar_o

Reputation: 567

Dir loop missing one file in specified folder

I've written a macro to process data within all files in a specified folder. However, it skips the first file in the folder. The problem is that the first file is referenced on this line:

FileName = Dir(path)

but the next file is referenced with this line:

FileName = Dir()

Full code:

Sub data_gatherer() 'skips ESAM_50

    'Removes unrealistic data and sums the no. starts/hours run for each pump stream
    Application.ScreenUpdating = False

        Dim sheet As Worksheet
        Dim calcSheet As Worksheet
        Dim path As String
        Dim ColCount As Integer
        Dim StreamCode As String
        Dim StreamSum As Double
        Dim NextRow As Double
        Dim FilePath As String
        Dim FileName As String
        Dim i As Integer
        Dim SumRange As range
        Dim SheetName As String
        Dim sSrcFolder As String

        sSrcFolder = "C:\IRIS MACRO TEST ZONE\SPS IRIS Bulk Data\" ' unprocessed data

        path = sSrcFolder & "*.csv" 'files withing sSrcFolder
        FileName = Dir(path)


        Do While FileName <> ""

            FileName = Dir() '''''skips first file here'''''''''''''''''''''''''''''''''''''''''''''''
            FilePath = sSrcFolder & FileName

                If FilePath = "C:\IRIS MACRO TEST ZONE\SPS IRIS Bulk Data\" Then ''' avoids error message for " .csv"
                    Exit Do
                End If

                Workbooks.Open (FilePath) 'error here - looks for "" filename

            SheetName = Left(FileName, 10)

                With Workbooks(FileName).Sheets(SheetName)
                    ColCount = .Cells(3, .Columns.count).End(xlToLeft).Column 'COUNT COLUMNS WITH DATA need to start with col 2
                    For i = 2 To ColCount 'i=2 to avoid date column

                            Call data_cleaner_all(FileName, SheetName, i)
                            Call StreamCalcs(NextRow, FileName, SheetName, SumRange, i)

                    Next i

                End With

             Workbooks(FileName).Saved = True
             Workbooks(FileName).Close
        Loop

    Application.ScreenUpdating = True

    End Sub

Upvotes: 0

Views: 1418

Answers (1)

Andre
Andre

Reputation: 27634

Put FileName = Dir() at the end of the loop, directly before the

Loop

line.

Edit re:

What is the difference in meaning between FileName = Dir() and FileName = Dir(path) ?

Dir(path) initializes the Dir function, and returns the first file/folder name. Dir() is always a follow-up call to a Dir(path) that came before, and returns the next file/folder.

If you call Dir() without having called Dir(path) before, you get a runtime error.

Upvotes: 4

Related Questions