File list to text

[dpw]

I'm attempting to obtain names of files and place them as text into a database using the code below (posted in this forum previously). A problem arises at the last line of code when the following error appears highlighting 'RowSource' :

Compile error: Method or data member not found

Is it something to do with the references that have been selected?

Private Sub Command7_Click()

Dim i As Integer
Dim strRow As String
Dim strSearch As String
'set the search string
strSearch = "c:\My Documents"
With Application.FileSearch
.LookIn = strSearch
.SearchSubFolders = False
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
.Execute
MsgBox .FoundFiles.Count & " files found"
For i = 1 To .FoundFiles.Count
strRow = strRow & Mid(.FoundFiles(i), Len(strSearch) + 2, 500) & ";"
Next i
End With

Me.lbxfilelist.RowSource = Left(strRow, Len(strRow) - 1)

End Sub

 

[AlanS]

Change the period before RowSource to an exclamation mark. In general, a period indicates a property or method, while an exclamation point indicates a user-defined field.

 

[dpw]

No success

Changed the period to an exclaimation mark and it resulted in a compile error.

Any other ideas?

 

[ghudson]

This function searches the path C:\Temp\ for all files that end in *.mdb and updates the TempFileName field in the tTempFiles table with the file names found.

Public Function ListFiles()
On Error GoTo Err_ListFiles
    
    Dim db As Database
    Dim rs As Recordset
    Dim vDir As Variant
    
    Const sPath = "C:\Temp\" 'Path to search
    
    Set db = CurrentDb
    
    db.Execute "Delete tTempFiles.* from tTempFiles;"
    
    Set rs = db.OpenRecordset("tTempFiles")
    
    vDir = Dir(sPath & "*.mdb") 'Searching for Access files *.mdb
    Do Until vDir = ""
        rs.AddNew
        rs!TempFileName = vDir
        rs.Update
        vDir = Dir
    Loop
    
    rs.Close
    db.Close
    Set db = Nothing
    Set rs = Nothing
    
Exit_ListFiles:
    Exit Function
    
Err_ListFiles:
    MsgBox Err.Number, Err.Description
    Resume Exit_ListFiles
    
End Function

HTH

 

[AlanS]

Sorry, I didn't read your original code carefully enough - I thought that RowSource referred to a text box on your form, rather than the listbox property by that name.

Your For...Next loop results in all the file names in the folder being concatenated into the String variable StrRow, delimited by semicolons. If you want to display that string on a form, simply assign StrRow to a text box control, rather than a list box. For example, you could change

Me.lbxfilelist.RowSource = Left(strRow, Len(strRow) - 1)

to

MyTextBox = Left(strRow, Len(strRow) - 1)

If you want to load the file names into a table, as your description sugggests, you'd need to include code to do so inside the For...Next loop, so that each file name would be added to a new record in the table. The easiest way to do that would be with DAO objects.

 

[dpw]

Sub Folder list to text

Thanks a lot for all your help. I got the code to work (eventually) but I've got just one further query.

Can you easily alter the code to obtain the names of sub-folders within a particular folder??? I used ghudson's function so how can it be adapted accordingly?

 

[AlanS]

If you change

Dir(sPath & "*.mdb")

to

Dir(sPath & "*.mdb", vbDirectory)

it will return sub-directory names rather than file names from the specified directory.

 

[dpw]

No joy

Alan
I replaced the code as you suggested but it simply turned red. Do I have to adjust my references?

Thanks D

 

[AlanS]

It may be a References problem - I'm not sure. vbDirectory is a system-defined constant, with a value of 16, so the quick-and-dirty way of resolving this would be to just change vbDirectory to 16.

 

[dpw]

Changing it to 16 didn't work either

 

[AlanS]

I'm not sure what the problem is. I'm using Access 97, and the syntax may be different for you if you're using another version. Check the help screen for the Dir function.

 

[dpw]

Dir[(pathname[, attributes])] is the syntax but being a novice at VBA this doesn't help me.

 

[ghudson]

dpw,

Do you only want to list the directory names within a specified path or do you want to list the complete addess of the files?

Example:

Temp

OR

C:\Temp\TestFile.txt

 

[dpw]

I want to list the directory names

 

[dpw]

Apologies to AlanS as when I pasted your alteration into the function I mistakenly deleted 'vDir='.


Thanks very much for your help.

D