Selecting and opening another excel file from a combo box 1

[funkmonsteruk]

I want to list the contents of a directory (F:\Process Support\Estate) in a combo box and would like to give the user the facility to select a file from the combo box list and click on a button to open it.

Basically this forms part of a user form which is being operated by several pc illiterate people. The user form has several other functions which allow the user to copy a sheet from the selected workbook(which only has one sheet) and add it to the end of a selection of sheets on another workbook. Along with this comes a defining name command and several other functions which i'm able to programme.....

 

[RobBroekhuis]

Use the filesearch object and methods to find all the relevant files in your folder, and populate the combobox using the AddItem method.
Rob

 

[funkmonsteruk]

Cheers Rob,

Have not used the filesearch object before and have only used the additem method in a single line before eg: combobox1.additem "item1"
combobox1.additem "item2"

could you possibly give me some sample code to use them in conjunction with each other.

Regards,
Funkmonsteruk

 

[RobBroekhuis]

Try something like:

With Application.FileSearch
.NewSearch
.LookIn = "F:\Process Support\Estate"
.SearchSubFolders = false
.FileName = "*.xls"
if .execute>0 then
for i=1 to .foundfiles.count
MyComboBox.additem .foundfiles(i)
next i
end if
End With

You probably have to parse the .foundfiles(i) item to get it into a format you like. This is just to get you started.
Rob

 

[funkmonsteruk]

Cheers Rob, i'm getting a variable not defined error on the i, any suggestions

 

[funkmonsteruk]

It's ok Rob, have defined i as an interger, seems to have done the trick. Any ideal how i can get the combobox to display only the name element of the file and omit the rest of the file string

 

[RobBroekhuis]

If you have "Option explicit" set for your module, then you need to define your variable:

dim i as integer

Rob

 

[funkmonsteruk]

This is fantastic Rob, i'm using the following codePrivate Sub UserForm_Initialize()


Dim i As Integer
Dim strSelect As String
mydate = Date
strSelect = ComboBox1.Value
ComboBox1.AddItem "Estate"
ComboBox1.AddItem "Beneficiary"
ComboBox1.AddItem "Legatee"
Label3.Caption = Format(mydate, "dddd dd mmmm yyyy&quot

With Application.FileSearch
.NewSearch
.LookIn = "F:\Process Support\Taps Balances"
.SearchSubFolders = False
.FileName = strSelect + "\*.xls"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
ComboBox2.AddItem .FoundFiles(i)
Next i
End If
End With



End Sub

The problem i now have is that i want combobox1 to control selection of the subfolder and combobox 2 to list the contentents only of the selected sub folder.

The strSelect variable i've created doesn't seem to be doing the job, do you have any suggestions

 

[RobBroekhuis]

Where you're first using strSelect, the combobox1 does not have any value yet. I would suggest putting the code to fill combobox2 in a separate sub. You could run this sub from your userform_initialize handler, as well as from the combobox1_change handler. That way, when the user selects a different folder, the file list will be updated.
Make sense? Rob

 

[funkmonsteruk]

Fantastic Rob, cheers for your help..........

Funkmonsteruk