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

LeFather

New Member
Joined
Mar 13, 2015
Messages
22
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.

Code:
[I]Workbooks.open([B][COLOR=#ff0000]MyName[/COLOR][/B])[/I]
 
Upvote 0
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.

Code:
[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.
 
Upvote 0
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:

Code:
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:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top