Store filenames in array 4

[Fahy]

I have a procedure which checks for receipt files.
The following procedure works in that it amalgamates all
the files into one file; but the file names are retrieved in no particular order, which means receipts are not ordered.

Open MyFileOut For Output As #2
MyName = Dir(MyCriteria) ' Retrieve the first entry.
Do While MyName <> &quot;&quot; ' Start the loop.
MyFile = MyPath & MyName
If IsNumeric(Right(MyName, 1)) Then
Open MyFile For Input As #1
Do While Not EOF(1)
Line Input #1, A1
Print #2, A1
Loop
Close #1
End If
MyName = Dir ' Get next entry.
Loop
Close #2

Microsoft Help says you may want to store returned file names in an array and then sort the array. How do I do this?

 

[FancyPrairie]

You might want to try using a recordset. Not sure what you're trying to do with you names, but here's an example of how to loop thru the directory, store the names of the files in a recordset, and then sort the recordset.

Dim MyName As String
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset


rst.Fields.Append &quot;FileName&quot;, adVarChar, 255
rst.Open

MyName = Dir(MyCriteria) ' Retrieve the first entry.
Do While MyName <> &quot;&quot; ' Start the loop.
rst.AddNew Array(&quot;FileName&quot, MyName
MyName = Dir ' Get next entry.
Loop

rst.Sort = &quot;Filename&quot;

rst.MoveFirst

While Not rst.EOF
MsgBox rst!FileName
rst.MoveNext
Wend

rst.close

 

[Fahy]

It is an Access 97 database.
I had to set a reference to the ADO library (Microsoft ActiveX Data Objects x.x Library - msado15.dll).
before it recognised your code.
Thanks after that it worked.:-D

Does this mean I have to set a reference from within the Modules window Tools,References to this file on each pc that accesses the database. Or is there yet another way of doing this using DAO.

 

[GComyn]

Here is the DAO version.... With the DAO version, you can't use the NEW statement for the DAO.Recordset (or at least, I can't get it to work....) So you have to have a table already created with the 'FileName' field. I've named the table 'tblFileNames' and the field 'strFileName' (following my naming convention)

    Dim MyName As String
    Dim MyCriteria As String
    Dim rst As DAO.Recordset
    
    Set rst = CurrentDb.OpenRecordset("tblfilenames")
    MyCriteria = "G:\BCC\Agency\Stan\National\*.*"
    MyName = Dir(MyCriteria)    ' Retrieve the first entry.
    Do While MyName <> &quot;&quot;    ' Start the loop.
        With rst
            .AddNew
            .Fields(&quot;strFileName&quot;) = MyName
            .Update
        End With
        MyName = Dir    ' Get next entry.
    Loop
    rst.Close
    
    Set rst = CurrentDb.OpenRecordset(&quot;select strfilename from tblfilenames order by strfilename&quot;)
    rst.MoveFirst
    
    While Not rst.EOF
        MsgBox rst!strFileName
        rst.MoveNext
    Wend

    rst.Close

I got that to work... if someone can think of another way to do this in DAO, I'd be interested in seeing it....

GComyn

 

[Fahy]

Thanks that way worked as well