Hi

I have been using this code to open all XL files in a certain
directory. Works well sometimes but if I am in Excel and go to File -
Open and change the directory to a completly different drive the Dir
function remembers this drive rather than the one I am telling it.
What can I do to force the Dir to always look at my specific
directory?

Thanks


Sub aaa()
Dim sPath As String
Dim sfil As String
Dim strName As String

sPath = "C:\Users\Test\Excel\"
sfil = Dir("*.xls")
ChDir sPath

Do While sfil < ""
strName = sPath & sfil
Workbooks.Open (strName)
sfil = Dir
Loop

End Sub

For ChDir to work, you may need to use ChDrive:
ChDrive "C"
ChDir sPath

"Chad" wrote in message
...
Hi

I have been using this code to open all XL files in a certain
directory. Works well sometimes but if I am in Excel and go to File -
Open and change the directory to a completly different drive the Dir
function remembers this drive rather than the one I am telling it.
What can I do to force the Dir to always look at my specific
directory?

Thanks


Sub aaa()
Dim sPath As String
Dim sfil As String
Dim strName As String

sPath = "C:\Users\Test\Excel\"
sfil = Dir("*.xls")
ChDir sPath

Do While sfil < ""
strName = sPath & sfil
Workbooks.Open (strName)
sfil = Dir
Loop

End Sub

Thanks for the reply Bob appreciate your help.

sFil = Dir(sPath & "*.xls")

Your assistance and the above line helped solve the problem. Thanks
again.