Better way to fill a ComboBox ?
 

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.Cle ar
Workbooks(HomeBook).Sheets("Report").ComboBox1.Tex t = "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

Better way to fill a ComboBox ?
 

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("Report ExportPath").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
'-----------------



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.Cle ar
Workbooks(HomeBook).Sheets("Report").ComboBox1.Tex t = "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

Better way to fill a ComboBox ?
 

Just noticed that...
For Loopy = 1 to 7
--should be--
For Loopy = 0 to 6
--
Jim Cone


"Jim Cone"
wrote in message
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("Report ExportPath").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
'-----------------

Better way to fill a ComboBox ?
 

Jim Cone wrote:
Just noticed that...
For Loopy = 1 to 7
--should be--
For Loopy = 0 to 6
--
Jim Cone


"Jim Cone"
wrote in message
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("Report ExportPath").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
'-----------------

Jim, thanks for taking the time to reply...... I have taken your code
and adapted it as follows...

'
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

myPath =
ThisWorkbook.Sheets("Configuration").Range("Report ExportPath").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 = 0 To 6
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
Left(Dir(.FoundFiles(i)), (Len(Dir(.FoundFiles(i))) - 4))
Next i
End If
End With
Next
Application.ScreenUpdating = True
End Sub

The only chage was to remove the ".xls" The file naming disipline of
"day-date-shift" looks just that bit better without the file extension
showing. And yes, Dir does work. Oh and I changed the array base ... 0
to 6

Again, many thanks for improving the code...... Mike

Better way to fill a ComboBox ?
 

Mike,
You are welcome.
One more and it won't make much difference, but...

It appears your code is in the module for the "Report" sheet.
So you can replace...
ThisWorkbook.Sheets("Report")
with...
Me

It would look like...
Me.ComboBox1.'existing code
in three places.

Regards,
Jim Cone



wrote in message
-snip-
Again, many thanks for improving the code...... Mike