Listing files in a listbox

[Helper monkey]

Hi Mudface,

Thanks for your modified function. I've placed it in my user form but couldn't find the check box in references that you refered to -?). I'm running excel 97 if that helps explain the problem.

When running the code the debugger says that the 'user-defined type is not defined' on the First line of the code (Dim myFileSystem...)


Private Sub UserForm_Initialize()
Dim myFileSystem As New Scripting.FileSystemObject
Dim myFolder As Scripting.Folder
Dim myFiles As Scripting.Files
Dim myFile As Scripting.File
Const TargetFolder As String = "C:\My Documents\"
Const myExtension As String = "*.xls"

On Error Resume Next
Set myFolder = myFileSystem.GetFolder(TargetFolder)
If Err <> 0 Then MsgBox "Unable to find folder.": Exit Sub

Set myFiles = myFolder.Files
For Each myFile In myFiles
If Right(LCase(myFile.Name), 4) Like myExtension Then
Me.ListBox1.AddItem myFileSystem.GetBaseName(myFile.Name)
End If
Next myFile

Set myFolder = Nothing
Set myFileSystem = Nothing

End Sub


Thanks in advance for your help.

Just noted that your from Hull, are you lucky enough to get 'Look North'? Or do you manage to escape it's reaches - Stunning interview techniques etc... :wink:

 

[Mudface]

The reference is called 'Microsoft Scripting Runtime', it's a windows system file which you (should) have. If it isn't there, try amending the code to: -

Private Sub UserForm_Initialize()
Dim myFileSystem As Object
Dim myFolder As Object
Dim myFiles As Object
Dim myFile As Object
Const TargetFolder As String = "C:My Documents"
Const myExtension As String = "*.xls"

Set myFileSystem = CreateObject("Scripting.FileSystemObject")

On Error Resume Next
Set myFolder = myFileSystem.GetFolder(TargetFolder)
If Err <> 0 Then MsgBox "Unable to find folder.": Exit Sub

Set myFiles = myFolder.Files
For Each myFile In myFiles
    If Right(LCase(myFile.Name), 4) Like myExtension Then
        Me.ListBox1.AddItem myFileSystem.GetBaseName(myFile.Name)
    End If
Next myFile

Set myFolder = Nothing
Set myFileSystem = Nothing

End Sub

Oh, yeah, Look North's class, Calendar's strangely pish, too :wink:. Best regional news in the Welsh one, though. After Manchester failed to get the 1996(?) Olympics, their main headline was 'Wales loses out on Olympic bid'. It turned out one of the yachting races would have been run in Cardigan Bay .

 

[Helper monkey]

Hi,

Thanks for the help!

I'm at uni in Bangor (North wales) and the local radio stations have a compulsary welsh hour. It can catch you unawares if your not careful :wink: Nothing compared to the class acts in Yorkshire though!

 

[Mudface]

Ooof, not my favourite place in the world, Bangor- watch out for S4C, too.

Anyway, I forgot to add this to the last post- to open your file from the listbox, add a command button to the userform and put this code in the userform module (note that the TargetFolder constant declaration should be at the top of the module: -

Option Explicit
Const TargetFolder As String = "C:My Documents"

Private Sub CommandButton1_Click()

Workbooks.Open TargetFolder & ListBox1.Text

End Sub

Private Sub UserForm_Initialize()
Dim myFileSystem As Object
Dim myFolder As Object
Dim myFiles As Object
Dim myFile As Object
Const myExtension As String = "*.xls"

Set myFileSystem = CreateObject("Scripting.FileSystemObject")

On Error Resume Next
Set myFolder = myFileSystem.GetFolder(TargetFolder)
If Err <> 0 Then MsgBox "Unable to find folder.": Exit Sub

Set myFiles = myFolder.Files
For Each myFile In myFiles
    If Right(LCase(myFile.Name), 4) Like myExtension Then
        Me.ListBox1.AddItem myFileSystem.GetBaseName(myFile.Name)
    End If
Next myFile

Set myFolder = Nothing
Set myFileSystem = Nothing

End Sub

 

[Helper monkey]

SC4 is applaling - and i'm not sure understanding it would amke much difference!

I'm still having trouble finding this reference 'Microsoft Scripting Runtime'. It's not listed to be checked. The only similar option is Microsoft Scriplet Component and this has no affect. I've tried using the alternative that you suggested but the code stuggles with the

Set myFileSystem = CreateObject("Scripting.FileSystemObject")

portion. Thanks again for your time - only 2 hours togo before look north... :wink:

 

[Mudface]

Oh well, scripting might have been disabled on your PC. The following will do the same: -

Option Explicit
Const TargetFolder As String = "C:My Documents"

Private Sub CommandButton1_Click()
Workbooks.Open TargetFolder & Me.ListBox1.Text
End Sub


Private Sub UserForm_Initialize()
Dim i As Integer

With Application.FileSearch
    .NewSearch
    .LookIn = TargetFolder
    .Filename = "*.xls"
    .Execute
    For i = 1 To .FoundFiles.Count
        Me.ListBox1.AddItem Mid(.FoundFiles(i), Len(TargetFolder) + 1)
    Next i
End With

End Sub

 

[Helper monkey]

That works a treat. Thanks very much!

Have a great weekend

 

[Helper monkey]

That works a treat. Thanks very much!

Have a great weekend

 

[Helper monkey]

That works a treat. Thanks very much!

Have a great weekend