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

--
Cheers
Nigel

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


"Nigel" wrote in message
...
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

--
Cheers
Nigel

Nigle

It works okay for me in XL97.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Nigel wrote:
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

Hi Nigel

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

HTH. Best wishes Harald

"Nigel" skrev i melding
...
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

--
Cheers
Nigel

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" wrote in message
...
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
Cheers
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.

--
Cheers
Nigel



"Jim Cone" wrote in message
...
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" wrote in message
...
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
Cheers
Nigel