Listing files in a listbox

Helper monkey

Board Regular
Joined
Jun 23, 2002
Messages
63
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:
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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: -

Code:
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 :biggrin:.
 

Helper monkey

Board Regular
Joined
Jun 23, 2002
Messages
63
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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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: -

Code:
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

Board Regular
Joined
Jun 23, 2002
Messages
63

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Oh well, scripting might have been disabled on your PC. The following will do the same: -

Code:
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

Board Regular
Joined
Jun 23, 2002
Messages
63

ADVERTISEMENT

That works a treat. Thanks very much!

Have a great weekend :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top