Copy directory listing to a file/spreadsheet 1

[ThomaJo]

Hello. I would like to know if anyone can give me code to open a directory list (give a drop-down or something to allow a user to choose the directory of his choice from his computer) and then copy the "filenames" (names only, not the actual files) and paste the list of names into a text file or Excel spreadsheet. I need to get a list of my files and compare them to another list (and its just easier to do this from a sheet of paper). Any help would be appreciated.

 

[Swi]

A few questions:

#1 - Are you concerned about sub folders
#2 - Where is the list you are comparing coming from

Drop a directory list box and a command button on a form:

Option Explicit
Dim fso As FileSystemObject
Dim dict As Dictionary
Dim OutStream As TextStream
Dim fld As Folder
Dim fle As File

Private Sub Command1_Click()
    Set fso = New FileSystemObject
    Set dict = New Dictionary
    Set OutStream = fso.OpenTextFile("C:\Common Files.txt", ForWriting, True)
    dict.Add "Test.txt", 1
    dict.Add "Test2.txt", 2
    dict.Add "log.txt", 3
    Set fld = fso.GetFolder(Dir1.Path)
    For Each fle In fld.Files
        If dict.Exists(fle.Name) Then OutStream.WriteLine fle.Name
    Next
    OutStream.Close
    dict.RemoveAll
    Set OutStream = Nothing
    Set dict = Nothing
    Set fso = Nothing
    MsgBox "Done!", vbInformation
End Sub

The dict.Add part is basically making list of files to check against. This should get you started. Also, you do not have to use the fso. You could use Dir but since I was utilizing the dictionary object I thought I would just use the fso.

Swi

 

[ThomaJo]

Thanks Swi!

To answer your questions, #1, No, don't need subfolders. #2 the list I am comparing it to is basically data sheets (paper forms).

I thought it would be easy, but I can't figure out how to "Drop a directory list box" on my form. I can't find the command button in the tool list. What's it actually called? I tried help and if I don't know the correct term, its useless. I'm trying to work from VBA (MS Access) but I could do this in VB 6 if necessary.

 

[Swi]

If you indeed want to do this in Access then forum707 will be of assitance. The Directory List Box and the Command button objects are on the left hand side of the screen when you open a new project in VB6.

Swi

 

[ThomaJo]

OK, I tried this in VB 6 and I get a Compile error "User defined type not defined" at line:

Dim fso As FileSystemObject


I don't know what a FileSystemObject is. Any suggestions?

 

[Swi]

You need to add a reference to Microsoft Scripting Runtims under Project/References menu. Here is a link that explains the FSO.

http://msdn.microsoft.com/en-us/library/6kxy1a51(VS.85).aspx

Swi

 

[ThomaJo]

OK, I finally tried this again and I got the "done" message box. A file was created in C:\ ("Common Files.txt") but the file is empty. I don't see a file list. What am I missing here?

 

[Golom]

If you just want the list of files then

Private Sub Command1_Click()
    Dim OutStream As Scripting.TextStream
    Dim fle       As Scripting.File

    With New FileSystemObject
       Set OutStream = .OpenTextFile("C:\Common Files.txt", ForWriting, True)
       For Each fle In .GetFolder("C:\myFolder").Files
           OutStream.WriteLine fle.Name
       Next
       OutStream.Close
       Set OutStream = Nothing
    End With
    MsgBox "Done!", vbInformation
End Sub

Swi's code was designed to compare the files found in the directory with file names stored in a dictionary and output them only if there was a match. This will output all files in the folder.

 

[ThomaJo]

Great, I do want just the list of files. But I try this and I get the error (76) "Path not found" at line

"For Each fle In .GetFolder("C:\myFolder").Files"

The directory location to get the files from should be coming from the "directory list box" (called Dir1). I'm not sure how to get a variable from that and how to send it to your code.

 

[Golom]

Then change "C:\myFolder" to Dir1.Path.

 

[ThomaJo]

Perfect! That's exactly what I want. Thanks so much Golom!

 

[ThomaJo]

One more question. Can I get the Directory List box (Dir1) to "display" the actual file names? It just show the directory names. Being able to View the files in that directory would be useful. Thnx!

 

[Golom]

Drop a FileListBox on your form and link it to the directory list box.

 

[ThomaJo]

OK now, how do I link them ("FileList1" to "Dir1")?
(I only dabble in this, I'm not a programmer.)

 

[Golom]

Private Sub Dir1_Change()
    File1.Path = Dir1.Path
End Sub

 

[ThomaJo]

Great! One more question. How do I get it to open the file now (C:\Common Files.txt). I coud either add the code to a button or I can add the code to the Command1_Click. Either way would work. Thnks!

 

[Golom]

Open it for what purpose?

- Read it?
- Write to it?
- Browse It?
- Edit It?

 

[ThomaJo]

Well, its saved as a text file, so I just want the text file to open automatically for review. Once its open with a text program (Wordpad, Notepad, etc.) then I can copy/paste the contents into Excel or whatever.

 

[johnwm]

Just shell to the default file handler. The following will open ANY document that has a default file handler, so .TXT will open with Notepad, .XLS will open with Excel etc.

Public Function OpenDocument(DocumentWithPath As String) As Long
    OpenDocument = Shell("RUNDLL32.EXE URL.DLL,FileProtocolHandler " & DocumentWithPath, vbNormalFocus)
End Function

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints