I am using the Dir function to iterate thru a directory for files with a
certain name as follows, there will be more than one file that meets the
mask

Dim myFiles
myFiles = Dir("C:\"Export_List*.xls")
Do While myFiles < ""
myFiles = Dir
Loop

My question

How do I store all the file names found into an array?



--
Cheers
Nigel

On this page you can see how to do this Nigel
http://www.rondebruin.nl/copy3.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Nigel" wrote in message ...
I am using the Dir function to iterate thru a directory for files with a
certain name as follows, there will be more than one file that meets the
mask

Dim myFiles
myFiles = Dir("C:\"Export_List*.xls")
Do While myFiles < ""
myFiles = Dir
Loop

My question

How do I store all the file names found into an array?



--
Cheers
Nigel

Using your code you could do it something like this...

Sub test()
Dim myFile As String
Dim myFiles() As String
Dim lng As Long

lng = 0
myFile = Dir("C:\Export_List*.xls")
Do While myFile < ""
ReDim Preserve myFiles(lng)
myFiles(lng) = myFile
myFile = Dir
lng = lng + 1
Loop

End Sub
--
HTH...

Jim Thomlinson


"Nigel" wrote:

I am using the Dir function to iterate thru a directory for files with a
certain name as follows, there will be more than one file that meets the
mask

Dim myFiles
myFiles = Dir("C:\"Export_List*.xls")
Do While myFiles < ""
myFiles = Dir
Loop

My question

How do I store all the file names found into an array?



--
Cheers
Nigel

Hi Nigel,

use a collection instead of an array,
so you don't have to worry about re-dimension.

Sub Test4()
Dim oCll As Collection
Set oCll = New Collection
Dim myfiles As String

myfiles = Dir("C:\test\excel\*.xls")
Do While myfiles < ""
oCll.Add myfiles
myfiles = Dir
Loop
MsgBox oCll(1)
MsgBox oCll(oCll.Count)
End Sub

And have a look at the quotation marks in your sample.
There is one too many.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Thanks to all for your valued suggestions. I now have a way forward

--
Cheers
Nigel



"Helmut Weber" wrote in message
...
Hi Nigel,

use a collection instead of an array,
so you don't have to worry about re-dimension.

Sub Test4()
Dim oCll As Collection
Set oCll = New Collection
Dim myfiles As String

myfiles = Dir("C:\test\excel\*.xls")
Do While myfiles < ""
oCll.Add myfiles
myfiles = Dir
Loop
MsgBox oCll(1)
MsgBox oCll(oCll.Count)
End Sub

And have a look at the quotation marks in your sample.
There is one too many.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"