DIR and Arrays

[Nigel]

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?

 

[Ron de Bruin]

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

 

[Guest]

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

 

[Helmut Weber]

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"

 

[Nigel]

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