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