CODEWITHSUNDEEP
vbaloopsdirectory
GCoxxx
GCoxxx

Reputation: 75

VBA unable to move on to next file in Directory, instead picking file named ".."

I am having a little issue with the Loop function to open files within a Directory. Find the code below:

    'Build the complete folder path:
     strTargetFolder_Batch = "I:\PerfTeam"

     strTargetFolder_Batch = strTargetFolder_Batch & strMonthNo & " " & strMonthName & " " & strYear & "\" & "Attribution - Draft"

        If Right(strTargetFolder_Batch, 1) <> "\" Then
            strTargetFolder_Batch = strTargetFolder_Batch & "\"
        End If

        If Not CreateFolder(strTargetFolder_Batch) Then
            MsgBox "Unable to create the folder:" & vbCrLf & strTargetFolder_Batch, vbExclamation
        Else
        End If

        FolderPath = strTargetFolder_Batch

        'Sets Parameters to Open the file
        MyFolder = FolderPath 'location of files
        MyExtension = "*.xlsx*"
        MyFile = Dir(MyFolder & MyExtension)

        Do While MyFile <> "" 'will start LOOP until all files in MyFolder have been looped through

              Set oWbk = Workbooks.Open(MyFolder & "\" & MyFile)

               *Batch Run is a Boolean function*

              '*** 1. Calls Import Data Macro, which Imports the Data ***'
              Call Import_new_data(Batch_Run, oWbk)

              '*** 2. Calls Data Collector Macro, which Analyses the Data ***'
              Call Data_Collector(Batch_Run)

              '*** 3. Calls Report Production Macro, which Produces Report ***'
              Call Report_Production_Sub(Batch_Run)

        ContinueLoop:
        MyFile = Dir 

        '**^^ Here is where the Macro breaks after completing a full first iteration** !

        Loop

What essentially the macro does, it picks up data from the opened file, closes the file and then analyses it, before creating a report out of it. It should then move on the second file in the folder and perform the same operation.

While the first file gets opened fine, and analysed as it should, the problem arises moving on to the second file. The variable MyFile in fact picks up a 'Ghost' file named ".." which then throws an error of course as it does not exist. Doing some research I have found out this may relate to the Directory path.

Any help would be super appreciated!

Upvotes: 0

Views: 732

Answers (1)

FunThomas
FunThomas

Reputation: 29652

Calling the Dir function with parameter starts a search for matching files. If nothing is specified as second parameter, is will search only regular files (no directories, no hidden files etc).

Any following calls to Dir (without parameter) will continue the last search initiated by a Dir(with parameter).

The .. you get as result of the Dir within your loop is not a file, it's a folder (up directory). You will get this only when you started a Dir with option vbDirectory as second parameter. As this parameter is missing in your code, I would strongly assume that anywhere in your code (that is not displayed) a new Dir-search is started (which destroys the search results of a previous Dir-search).

Update: If you need to check if a folder exists but don't want to destroy your Dir-Loop, you could use the FileSystemObject. The FileSystemObject is usefull for several things concerning files and folders.

if CreateObject("Scripting.FileSystemObject").FolderExists("<enter your path>") then

Upvotes: 1

Related Questions