Opening Excel-files in several directories
 

Bob Philips gave me the solution (see hereunder) to open Excel-files in a
certain directory. Is it also possible to change the directory after
finishing this and restart the job from the beginning? In other words: is it
possible to change the line
"sFile = Dir("C:\TestFolders\*.xls", vbNormal)" into
"sFile = Dir("C:\TestFolders2\*.xls", vbNormal)" by a For..Next loop?

Dim sFile As String
sFile = Dir("C:\TestFolders\*.xls", vbNormal)
If sFile < "" Then
Do
'Do your stuff
Debug.Print sFile
sFile = Dir

Loop Until sFile = ""
End If

Opening Excel-files in several directories
 

Sub OpenFiles()
Dim v as Variant, sFile as String, i as Long
v = Array("C:\TestFolders\*.xls", _
"C:\TestFolders2\*.xls", _
"C:\TestFolders3\*.xls")
Dim sFile As String
for i = lbound(v) to ubound(v)
sFile = Dir(v(i), vbNormal)
Do while sFile < ""
'Do your stuff
Debug.Print Left(v(i),len(v(i))-5) & sFile
sFile = Dir
Loop Until sFile = ""
Next i
End sub

--
Regards,
Tom Ogilvy


"Dutch76" wrote:

Bob Philips gave me the solution (see hereunder) to open Excel-files in a
certain directory. Is it also possible to change the directory after
finishing this and restart the job from the beginning? In other words: is it
possible to change the line
"sFile = Dir("C:\TestFolders\*.xls", vbNormal)" into
"sFile = Dir("C:\TestFolders2\*.xls", vbNormal)" by a For..Next loop?

Dim sFile As String
sFile = Dir("C:\TestFolders\*.xls", vbNormal)
If sFile < "" Then
Do
'Do your stuff
Debug.Print sFile
sFile = Dir

Loop Until sFile = ""
End If

Opening Excel-files in several directories
 

Thanks Tom!

"Tom Ogilvy" wrote:

Sub OpenFiles()
Dim v as Variant, sFile as String, i as Long
v = Array("C:\TestFolders\*.xls", _
"C:\TestFolders2\*.xls", _
"C:\TestFolders3\*.xls")
Dim sFile As String
for i = lbound(v) to ubound(v)
sFile = Dir(v(i), vbNormal)
Do while sFile < ""
'Do your stuff
Debug.Print Left(v(i),len(v(i))-5) & sFile
sFile = Dir
Loop Until sFile = ""
Next i
End sub

--
Regards,
Tom Ogilvy


"Dutch76" wrote:

Bob Philips gave me the solution (see hereunder) to open Excel-files in a
certain directory. Is it also possible to change the directory after
finishing this and restart the job from the beginning? In other words: is it
possible to change the line
"sFile = Dir("C:\TestFolders\*.xls", vbNormal)" into
"sFile = Dir("C:\TestFolders2\*.xls", vbNormal)" by a For..Next loop?

Dim sFile As String
sFile = Dir("C:\TestFolders\*.xls", vbNormal)
If sFile < "" Then
Do
'Do your stuff
Debug.Print sFile
sFile = Dir

Loop Until sFile = ""
End If