Better way to fill a ComboBox ?

M

mike.r.harris

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
 
J

Jim Cone

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
'-----------------


<[email protected]>
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
 
J

Jim Cone

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


"Jim Cone" <[email protected]>
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("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
'-----------------
 
M

mike.r.harris

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


"Jim Cone" <[email protected]>
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("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
'-----------------
Click to expand...

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("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 = 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
 
J

Jim Cone

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


<[email protected]>
wrote in message
-snip-
Again, many thanks for improving the code...... Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with second count routine from userform 4
removing duplicate rows 1
UDF Not Returning Array to Range 6
Open most recent file 10
Citrix Excel VBA Search "There were no files found" 3
Copy item from Outlook to folder via Excel 12
application.filesearch error in office 2010 3
Need help with Array with type, invalid qualifier & runtime error 6

Top