Excel DIR

[Nigel]

Hi All
I am using the DIR function in VBA (Excel 2002) to build a list of files in
a specified directory as follows, and it work fine. My problem is that I
need a similar code for Excel 97. It appears the the construct for the DIR
function does not accept the pathname and or attributes parameters in the
smae way. Can anyone help to describe the differences between them.

Directory = "C:\myfiles\"
xFile = Dir(Directory & "*.xls", 7)
Do While xFile <> ""
wpList.Cells(xRow, 1) = xFile
xRow = xRow + 1
xFile = Dir
Loop

 

[Chip Pearson]

Your code works fine for me in Excel97. What specific problem are
you encountering?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Dick Kusleika]

Nigle

It works okay for me in XL97.

 

[Harald Staff]

Hi Nigel

I've had problems with DIR on Windows 9X when used on the root C:\ .
Subfolders work well.

HTH. Best wishes Harald

 

[Jim Cone]

Nigel,

Here is an alternative that has been very reliable...
(note the project reference required)

'---------------------------------------------------
Sub ListFolderAndFiles()
'Jim Cone - San Francisco, USA
'Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll)
'List all files in the specified folder.

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim arrNames() As String
Dim strPath As String
Dim lngCount As Long
Dim lngNum As Long

'Specify the folder...
strPath = "C:\Documents and Settings\user\My Documents"

Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)
lngCount = objFolder.Files.Count + 1
ReDim arrNames(1 To lngCount, 1 To 2)
lngNum = 2

'Load arrNames with the folder path in column one
'and the file names in column two.
arrNames(1, 1) = objFolder.Path
For Each objFile In objFolder.Files
arrNames(lngNum, 2) = objFile.Name
lngNum = lngNum + 1
Next 'objFile

'Transfer array elements to active worksheet in columns B & C.
Range("B1", Cells(lngCount, 3)).Value = arrNames()

Set objFile = Nothing
Set objFolder = Nothing
Set objFSO = Nothing
End Sub
'----------------------------------------

 

[Nigel]

Thanks for the feedback, I will be checking out the options when I return to
the office where xl97 resides. I am beginning to believe it might be a
network problem - I am trying to read a network drive (NT 4) and I have been
getting run-time errors. Maybe I need to use FSO as the access route as
suggested by Jim.