Sleeper: Load Folder Content in Single Column List Box

[sheeeng]

Hi all ,

How can I load a folder contents into a list box?

The list box display the full name of the file.

After that, when I double click the item in list box, it will open the file.

eg. C:\MyFile has files such as name.txt, add.txt, money.txt
(most of my file is *.txt)

ListBox <------ load the folder content into list box
---------
name.txt <----- double click here will open name.txt with notepad.
add.txt
money.txt

Thanks.

[Bob Phillips]

How can I load a folder contents into a list box?

The list box display the full name of the file.

After that, when I double click the item in list box, it will open the file.

Sheeng,

Why not just create a new worksheet with a hyperlinked list of files. I showed an example yesterday at http://tinyurl.com/7l4lv

[sheeeng]

Sheeng,

Why not just create a new worksheet with a hyperlomked list of files. I showed an example yesterday at http://tinyurl.com/7l4lv

Thanks for the information. But it looks messy when 100+ files involved.
Can we still proceed on list box?

Btw, what is tinyurl for? Why it leads to google groups?

[Norie]

Where is the listbox located?

Is it on a userform?

Please see the attached

[Bob Phillips]

Thanks for the information. But it looks messy when 100+ files involved.
Can we still proceed on list box?

Btw, what is tinyurl for? Why it leads to google groups?

Easybit first. Tinyurl is a web service that will take a long URL and apply an encoding algorithm that comes up with a link to TinyURL. When you click on the TinyURL URL, it is decoded and links you to the real URL. We use it a lot in the newsgroups where wrap-around cuts URLs up.

You can add TinyURL as a link in your browser, see http://tinyurl.com/#toolbar

There are many similar services, such as Make A Shorter link, Snurl, etc. I like TinyURL the best.

In what way do you find it messy, is it the grouping, the extar file information? If so, these could be removed.

[sheeeng]

Easybit first. Tinyurl is a web service that will take a long URL and apply an encoding algorithm that comes up with a link to TinyURL. When you click on the TinyURL URL, it is decoded and links you to the real URL. We use it a lot in the newsgroups where wrap-around cuts URLs up.

You can add TinyURL as a link in your browser, see http://tinyurl.com/#toolbar

There are many similar services, such as Make A Shorter link, Snurl, etc. I like TinyURL the best.

In what way do you find it messy, is it the grouping, the extar file information? If so, these could be removed.

Well, the tabs on most left is very distracting for me. Sorry to mention it.
Plus, It list the name of folder repeatedly. (I don't know why.) Maybe too much file and folder contain in parent folder....

[sheeeng]

Where is the listbox located?

Is it on a userform?

Please see the attached

The attachment excel macro seem to stop for long time with no results...
How?

[gsouza]

so how do you double click on the list to open a specific file listed? I want to know also : )

[gsouza]

I think i did it

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Link = ListBox1.Text 
On Error GoTo NoCanDo
ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
Unload Me
Exit Sub
NoCanDo:
MsgBox "Cannot open " & Link
End Sub

[Bob Phillips]

Well, the tabs on most left is very distracting for me. Sorry to mention it.
Plus, It list the name of folder repeatedly. (I don't know why.) Maybe too much file and folder contain in parent folder....

Try this

Option Explicit
Private FSO As Object
Private cnt As Long
Private arfiles
    
Private Const kFolder As String = "c:\myTest"
Sub Folders()
Dim i As Long
Dim sFolder As String
Dim iStart As Long
Dim iEnd As Long
Dim fOutline As Boolean
Set FSO = CreateObject("Scripting.FileSystemObject")
    arfiles = Array()
    cnt = -1
    ReDim arfiles(2, 0)
    If sFolder <> "" Then
        SelectFiles kFolder
        Application.DisplayAlerts = False
        On Error Resume Next
            Worksheets("Files").Delete
        On Error GoTo 0
        Application.DisplayAlerts = True
        Worksheets.Add.Name = "Files"
        With ActiveSheet
            For i = LBound(arfiles, 2) To UBound(arfiles, 2)
                If arfiles(0, i) = "" Then
                    With .Cells(i + 1, 1)
                        .Value = arfiles(2, i)
                        .Font.Bold = True
                    End With
                    iStart = i + 1
                    iEnd = iStart
                    fOutline = False
                Else
                    .Hyperlinks.Add Anchor:=.Cells(i + 1, 2), _
                                    Address:=arfiles(0, i), _
                                    TextToDisplay:=arfiles(2, i)
                    iEnd = iEnd + 1
                End If
            Next
            .Columns("A:Z").Columns.AutoFit
        End With
    End If
End Sub


Sub SelectFiles(Optional sPath As String)
Dim oSubFolder As Object
Dim oFolder As Object
Dim oFile As Object
Dim oFiles As Object
Dim arPath
arPath = Split(sPath, "\")
    cnt = cnt + 1
    ReDim Preserve arfiles(2, cnt)
    arfiles(0, cnt) = ""
    arfiles(2, cnt) = sPath
    Set oFolder = FSO.GetFolder(sPath)
    Set oFiles = oFolder.Files
    For Each oFile In oFiles
        cnt = cnt + 1
        ReDim Preserve arfiles(2, cnt)
        arfiles(0, cnt) = oFolder.Path & "\" & oFile.Name
        arfiles(1, cnt) = Right(oFile.Name, Len(oFile.Name) - _
            InStrRev(oFile.Name, "."))
        arfiles(2, cnt) = oFile.Name
    Next oFile
    For Each oSubFolder In oFolder.Subfolders
        SelectFiles oSubFolder.Path
    Next
End Sub

[Norie]

Change this line

  ListBox1.AddItem .FoundFiles(I)

to this

  ListBox1.AddItem Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "\") + 1)

To get rid of the path.

[sheeeng]

Change this line

ListBox1.AddItem .FoundFiles(I)

to this

ListBox1.AddItem Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "\") + 1)

To get rid of the path.

Sorry. The program cannot work, Execution time seem halted. How?

[Bob Phillips]

The program cannot work, Execution time seem halted. How?

How many files have you got? I could only take one tenth of a second even on the windows directory files.

ANyway, this code is 20-30% quicker than the other guy's in my tests. You need to set a reference to the Microsoft Scripting Runtime library

Dim oFSO  As Scripting.FileSystemObject
Dim oFolder As Folder, oFile As File
Set oFSO = CreateObject("Scripting.FilesystemObject")
    Set oFolder = oFSO.getfolder("c:\windows")
For Each oFile In oFolder.Files
        ListBox1.AddItem oFile.Name
    Next oFile

[sheeeng]

How many files have you got? I could only take one tenth of a second even on the windows directory files.

ANyway, this code is 20-30% quicker than the other guy's in my tests. You need to set a reference to the Microsoft Scripting Runtime library

Dim oFSO As Scripting.FileSystemObject
Dim oFolder As Folder, oFile As File
Set oFSO = CreateObject("Scripting.FilesystemObject")
Set oFolder = oFSO.getfolder("c:\windows")
For Each oFile In oFolder.Files
ListBox1.AddItem oFile.Name
Next oFile

Can yo attach your file? How can I set a reference to the Microsoft Scripting Runtime library? Through Menu in VBE or by Code?

[Ivan F Moala]

If speed is an issue then use the Dir command instead of FSO then you don't need to set a reference to it. Look up DIR in you VBA help.

[sheeeng]

If speed is an issue then use the Dir command instead of FSO then you don't need to set a reference to it. Look up DIR in you VBA help.

Can you show me a sample? Thanks.

[Ivan F Moala]

XLD showed you a link that has an example

see http://tinyurl.com/#toolbar

[Justinlabenne]

See if this gets you started:

[sheeeng]

See if this gets you started:

That's great!! Almost like what I need...
Can you modify the code so as to remove the full path?
Just show the file name in the list box, but show the path at title of form...

Why cannot link to the file through the list box?

Thanks!!

[Justinlabenne]

Since this idea is something I could actually find useful myself, give me some time and I will finish it up.