filtering .xls files

S

suee

From the following code below, i would like to add a filter so that th
combobox only displays .xls files. EG if a .txt file is stored in th
same folder it will not appear in the combobox for selection.
Can anyone tell me the code to insert and where to insert it
Regards




Sub CreateFormsListBox()
Dim FSO As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim sPath As String
Dim oList As ListBox

Application.CommandBars("Forms").Visible = True
ActiveSheet.ListBoxes.Add(18, 12.75, 150, 200).Select
Selection.OnAction = "myPrintMacro"
Set oList = Selection
Set FSO = CreateObject("Scripting.FileSystemObject")
sPath = "C:\Documents and Settings\Klara Rac\My Documents\My eBooks\"
Set Folder = FSO.GetFolder(sPath)
Set Files = Folder.Files
For Each file In Files
oList.AddItem file.Name
Next file

Range("A1").Select

End Sub
Sub myPrintMacro()
Dim sPath As String
Dim myLB As ListBox

Set myLB = ActiveSheet.ListBoxes(Application.Caller)
sPath = "C:\Documents and Settings\Klara Rac\My Documents\My eBooks\"
If myLB.ListIndex < 1 Then
'do nothing
Else
Workbooks.Open Filename:=sPath & myLB.List(myLB.ListIndex)
End If

End Su
 
J

Jim Rech

Perhaps a modification like this (untested):

For Each file In Files
if lcase(Right(file.name,4)) = ".xls" Then _
oList.AddItem file.Name
Next file


--
Jim Rech
Excel MVP
| From the following code below, i would like to add a filter so that the
| combobox only displays .xls files. EG if a .txt file is stored in the
| same folder it will not appear in the combobox for selection.
| Can anyone tell me the code to insert and where to insert it
| Regards
|
|
|
|
| Sub CreateFormsListBox()
| Dim FSO As Object
| Dim Folder As Object
| Dim file As Object
| Dim Files As Object
| Dim sPath As String
| Dim oList As ListBox
|
| Application.CommandBars("Forms").Visible = True
| ActiveSheet.ListBoxes.Add(18, 12.75, 150, 200).Select
| Selection.OnAction = "myPrintMacro"
| Set oList = Selection
| Set FSO = CreateObject("Scripting.FileSystemObject")
| sPath = "C:\Documents and Settings\Klara Rac\My Documents\My eBooks\"
| Set Folder = FSO.GetFolder(sPath)
| Set Files = Folder.Files
| For Each file In Files
| oList.AddItem file.Name
| Next file
|
| Range("A1").Select
|
| End Sub
| Sub myPrintMacro()
| Dim sPath As String
| Dim myLB As ListBox
|
| Set myLB = ActiveSheet.ListBoxes(Application.Caller)
| sPath = "C:\Documents and Settings\Klara Rac\My Documents\My eBooks\"
| If myLB.ListIndex < 1 Then
| 'do nothing
| Else
| Workbooks.Open Filename:=sPath & myLB.List(myLB.ListIndex)
| End If
|
| End Sub
|
|
| ---
|
|
 
Top