CODEWITHSUNDEEP
excelvba
Jackyl
Jackyl

Reputation: 3

Excel VBA Dir loop fails when open and close command added within loop

I am trying to import data from all Excel files within a directory with the same prefix. Data will be aggregated from these files and written to the master file. I have successfully crafted a do while script that can identify the three test files using DIR with a wildcard. However, when I add the command to open the file (and immediately close it) the do while fails after the first pass after opening and closing the file. Commenting out the open and close commands and the do while loop, loops three times identifying the test files. Ultimately I would like to replace the open / close commands with a call to a sub that will open the files, aggregate the data and write it to the master file. I mention this in case it changes the way to code this. I have searched the forum and found a couple of other ways to accomplish some of my objectives but not all. One example being the wildcard in the filename. Any help is appreciated.

Sub LoopThroughFiles()

Dim strName As String
Dim strPath As String
Dim strFile As String


strPath = ThisWorkbook.Path
strName = "Employee Gross Sales"
strFile = Dir(strPath & "\" & strName & "*")

Do While Len(strFile) > 0
    Debug.Print strFile
'   Call OpenFile(strPath, strFile) <-- Eventually will replace open / close commands below

    Workbooks.Open FileName:=strPath & "\" & Dir(strPath & "\" & strFile)
'   Read / Aggregate / Write data code here or in called sub
    Workbooks(strFile).Close SaveChanges:=False

    strFile = Dir
Loop

End Sub

Sub OpenFile(strPath, strFile)
Dim wbTarget, wbSource As Workbook

Set wbSource = Workbooks.Open(FileName:=strPath & "\" & Dir(strPath & "\" & strFile))
wbSource.Close SaveChanges:=False

End Sub

Upvotes: 0

Views: 1476

Answers (1)

YowE3K
YowE3K

Reputation: 23974

Your Dir(strPath & "\" & strFile) in the Workbooks.Open command is "overwriting" your original Dir - you should just use strFile at that point.

If you cut your current code down to just the bits that are affected by the Dir, it would look like this:

strFile = Dir(some_string_including_wildcard)
'The above statement returns the first file name matching the wildcarded expression
Do While Len(strFile) > 0
    ... Dir(specific_filename_being_processed) ...
    'The above statement finds the first file name matching the specific filename
    'which will obviously be the specific filename

    strFile = Dir
    'That statement gets the next file name matching the argument last used as
    ' a parameter to a Dir.  As the last argument was a specific file, and there
    ' are no more files matching that argument (because it contained no wildcards)
    ' this returns an empty string.
Loop

Your code should be written as:

Sub LoopThroughFiles()

    Dim strName As String
    Dim strPath As String
    Dim strFile As String

    strPath = ThisWorkbook.Path
    strName = "Employee Gross Sales"
    strFile = Dir(strPath & "\" & strName & "*")

    Do While Len(strFile) > 0
        Debug.Print strFile
    '   OpenFile strPath, strFile  ' <-- Eventually will replace open / close commands below

        Workbooks.Open FileName:=strPath & "\" & strFile
    '   Read / Aggregate / Write data code here or in called sub
        Workbooks(strFile).Close SaveChanges:=False

        strFile = Dir
    Loop

End Sub

Sub OpenFile(strPath As String, strFile As String)
    Dim wbTarget As Workbook, wbSource As Workbook

    Set wbSource = Workbooks.Open(FileName:=strPath & "\" & strFile)
    wbSource.Close SaveChanges:=False

End Sub

Upvotes: 3

Related Questions