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" <nigel-(E-Mail Removed)> wrote in message news:%(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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"