Not sure whether "Dir" will work as shown, as it was too much
bother to do the setup to test it.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
Private Sub ComboBox1_DropButtonClick()
Dim myPath As String
Dim i As Long
Dim DayO As String
Dim Loopy As Integer
Dim varDays As Variant
Application.ScreenUpdating = False
'HomeBook = ThisWorkbook.Name
myPath = _
ThisWorkbook.Sheets("Configuration").Range("ReportExportPath").Value
ThisWorkbook.Sheets("Report").ComboBox1.Clear
ThisWorkbook.Sheets("Report").ComboBox1.Text = _
"Select a previous shift to View"
varDays = Array("Monday*.xls", "Tuesday*.xls", "Wednesday*.xls", _
"Thursday*.xls", "Friday*.xls", "Saturday*.xls", "Sunday*.xls")
For Loopy = 1 To 7
DayO = varDays(Loopy)
With Application.FileSearch
.NewSearch
.LookIn = myPath
.SearchSubFolders = False
.Filename = DayO
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
ThisWorkbook.Sheets("Report").ComboBox1.AddItem Dir(.FoundFiles(i))
Next i
End If
End With
Next
Application.ScreenUpdating = True
End Sub
'-----------------
<(E-Mail Removed)>
wrote in message
Borrowed and "bent" this code to display what I needed but.....This
works for just a few files but has a slight delay..... I suspect as
the files build-up it will slow even more. Is there a better way to add
files from a preset folder (with the filename starting with the days of
the week) into a ComboBox?
Private Sub ComboBox1_DropButtonClick()
Dim myPath As String
Dim lLen As Long, i As Long
Dim DayO As String
Dim Loopy As Integer
Application.ScreenUpdating = False
HomeBook = ThisWorkbook.Name
myPath =
Workbooks(HomeBook).Sheets("Configuration").Range("ReportExportPath").Value
lLen = Len(myPath) + 1
Workbooks(HomeBook).Sheets("Report").ComboBox1.Clear
Workbooks(HomeBook).Sheets("Report").ComboBox1.Text = "Select a
previous shift to View"
For Loopy = 1 To 7
If Loopy = 1 Then DayO = "Monday"
If Loopy = 2 Then DayO = "Tuesday"
If Loopy = 3 Then DayO = "Wednesday"
If Loopy = 4 Then DayO = "Thursday"
If Loopy = 5 Then DayO = "Friday"
If Loopy = 6 Then DayO = "Saturday"
If Loopy = 7 Then DayO = "Sunday"
With Application.FileSearch
.NewSearch
.LookIn = myPath
.SearchSubFolders = False
.Filename = DayO & "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
ComboBox1.AddItem Left(Right(.FoundFiles(i), Len(.FoundFiles(i)) -
lLen), (Len(Right(.FoundFiles(i), Len(.FoundFiles(i)) - lLen)) - 4))
Next i
End If
End With
Next
End Sub
Any help appreciated
Similar Threads
