VBA loop through computer folder using DIR works but fails to open files (or loop won't work without dir)

[LeFather]

Code:
Sub DirectoryLoop
MyPath = "c:\*sales*.xls*" ' Set the path.
MyName = Dir(MyPath) ' Retrieve the first entry.
Do While MyName <> "" ' Start the loop.
MsgBox (MyName)
Call ProcessFile(MyName) 'This processes each file
MyName = Dir() ' Get next entry.
Loop
End Sub

Sub ProcessFile(MyName as String)
CallIngFile = thisworkbook.name
Workbooks.open(MyName)

For Each MyCell in activeworkbook.range("Input")
Thiscell = Mycell.Address
if TypeName(Mycell)>0 then
'do stuff
else
'do other stuff
End If
next MyCell
Workbooks.close(MyName)
End Sub





Hi,

I have one problem currently using the above code. If I use the Dir() function for MyPath to get the variable MyName, it gives error when trying to open the chosen file at point where is the code "Workbooks.open (MyName)". It is the 1004 File not found error (the file exists and can be opened manually from the folder as usual). If I skip the Dir(MyPath) = MyName part of the code and use only the MyPath through the code it works in the part where code opens the first workbook "Workbooks.open (MyPath)". That path includes at that point the directory and the "*sales*" variable. So it succesfully opens the first "*sales*" file in the folder and everything goes well. But I believe without the Dir() function the code cannot loop through the folder correctly. Why does the code give me this error when using MyName (which is Dir(Mypath))? What should I do the get the looping + file opening work?

Conclusion: Using MyPath without Dir, the code successfully opens the file but loop does not work. Using MyName it gives errors trying to open the files (but loop would probably work).

BR Juha

 

[gallen]

As far as I can see you aren't passing the fully qualified path. You are just passing in the file name.

Ensure, when you execute the open command, the 'MyName' variable has the full path.

[I]Workbooks.open([B][COLOR=#ff0000]MyName[/COLOR][/B])[/I]

 

[LeFather]

As far as I can see you aren't passing the fully qualified path. You are just passing in the file name.

Ensure, when you execute the open command, the 'MyName' variable has the full path.

[I]Workbooks.open([B][COLOR=#ff0000]MyName[/COLOR][/B])[/I]

Okay, thanks for the info. Could you give a suggestion what to add to the code to take this change into consideration? Many thanks in advance.

 

[gallen]

Okay, thanks for the info. Could you give a suggestion what to add to the code to take this change into consideration? Many thanks in advance.

Yes of course,

You just need to fully qualify so if I wanted to open a file named MyTest.xlsx in "C:\Test" then I would need:

Workbooks.Open("C:\Test\MyTest.xlsx"

In your case I'd suggest storing the path in a variable. The below code worked in my tests:

Sub DirectoryLoop()
    Dim MyName As String, MyFileLocation As String, MyPath As String
    
[COLOR=#ff0000][B]    MyFileLocation = "C:\" 'Directory containing files[/B][/COLOR]
    MyPath = "c:\*sales*.xls*" ' Set the path.
    MyName = Dir(MyPath) ' Retrieve the first entry.
    
    Do While MyName <> "" ' Start the loop.
        MsgBox (MyName)
        Call ProcessFile([COLOR=#ff0000][B]MyFileLocation &[/B][/COLOR] MyName)  'This processes each file
        MyName = Dir() ' Get next entry.
    Loop
    
End Sub


Sub ProcessFile(MyName As String)


    CallIngFile = ThisWorkbook.Name
    Workbooks.Open (MyName)
    
    For Each MyCell In ActiveWorkbook.Range("Input")
    ThisCell = MyCell.Address
    If TypeName(MyCell) > 0 Then
    'do stuff
    Else
    'do other stuff
    End If
    Next MyCell
[COLOR=#ff0000][B]    Workbooks(MyName).Close [/B][/COLOR]
End Sub

I've highlighted the changes in red. Note I change how you close the workbook as your syntax was wrong.