Specify .FileType from Combobox Selection?

[mobiius]

Hey all,

I am trying to write a little code that will search a drive letter for a specific type of file. Both choices are selected by the user.
They select the drive letter then they select the type of file they want to look for.

I have the drive letter part working fine, but the file type returns a "Type Mismatch" error. Can someone take a look at the code and tell me what I'm doing wrong?

ThisWorkbook code:

Private Sub Workbook_Open()
    Dim fs, d, dc
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set dc = fs.Drives
    For Each d In dc
    UserForm1.cboDriveList.AddItem d.DriveLetter
    Next d
    With UserForm1.cboFileType
        .AddItem (".xls")
        .AddItem (".doc")
        .AddItem (".jpg")
        .AddItem (".csv")
        .AddItem (".txt")
        .AddItem (".exe")
        .AddItem (".pst")
        .AddItem (".epf")
        .AddItem (".zip")
        .AddItem (".mdb")
        .AddItem (".pdf")
        .AddItem (".htm")
        .AddItem (".html")
    End With
    UserForm1.Show
End Sub

UserForm1 code:

Private Sub cmdFind_Click()
Dim vaFileName As Variant, lCnt As Long
Dim MyDir As String
Dim MyFile As String
MyDir = UserForm1.cboDriveList.Value & ":\"
MyFile = "*" & UserForm1.cboFileType.Value
'just for me to verify that the variables hold the correct selection
MsgBox "MyDir = " & MyDir, vbOKOnly
MsgBox "MyFile = " & MyFile, vbOKOnly
With Application.FileSearch
.NewSearch
.LookIn = MyDir
.SearchSubFolders = True
.FileType = MyFile
If .Execute > 0 Then
Application.ScreenUpdating = False
For Each vaFileName In .FoundFiles
lCnt = lCnt + 1
Sheet1.Cells(lCnt, 1).Value = vaFileName
Next
Application.ScreenUpdating = True
Else
MsgBox "There were no Excel files found."
End If
End With
UserForm1.Hide
End Sub

Thanks!

 

[Trevor G]

Question, which version of Excel are you using as FileSearch has been removed in Version 2007 and 2010.

 

[mobiius]

Hi Trevor,
I'm using Excel 2003 Pro SP3

Thanks

 

[Trevor G]

Here is sample code to look through a folder and list all the files into the activesheet. I think you can adapt it.

Sub FolderDir()

Dim sFilename As String, iRow As Long

sFilename = Dir("M:\Access Files\")
Do Until sFilename = ""
iRow = iRow + 1
Cells(iRow, 1) = sFilename
sFilename = Dir()
Loop

End Sub

 

[mobiius]

Ok, thanks...

I'm not looking to list every file it finds in the drive, I want it to show only the file type that is specified by the user. Listing the files in the sheet is not the issue.

When I manually specify the file type in the ".FileType = " part of the code, it all works. The problem is when I try to pass the value of the combobox selection to the ".FileType" part of the code.

Thanks for replying.

Anyone else?

 

[Trevor G]

The code I suggested you look at can be adapted to refer to what you have selected in the combo boxes, it was a sample for you to adapt.

If you have created what you have so far then you would see you can adjust what I sent. If you can't do that then let me know and I will send you the code corrected for you.