Looking up files

[j_Southern]

I have an application which has as its input, filed workbooks. Each workbook has one used worksheet and I need to copy this into my application.
I would like to automate the process by having some kind of lookup of the file names which could then be opened and copied onto a sheet in the application.
The files are organised as follows U:\JobNames\CustomerName\Job.xls
Is this going to be possible?
I wonder if it would be possible to create a list of all the files that could be loaded onto a worksheet when the application is opened. I could certainly work with that.
John

[mikeTRON]

Your request is unclear.

What are you wanting specifically?

[j_Southern]

Sorry not to be clear.

I have a few hundreds of excel workbooks all with one sheet containing data. These are all in one folder "JobNames". This contains a folder for each customer and each customer folder can have from 1 to 50 of the workbooks I want to load.

So U:|JobNames\CustomerNames\Job.xlsm

I want to be able to have a list of file names so that one can be chosen to be loaded into my application, without reopening Excel manually and loading a file that way and then copying and pasting into the application.

I think I could do this if there was no "CustomerName" Sub folder, but this has defeated me so far.

I hope that my request is clearer and you can help.
John

[j_Southern]

I have made some progress on my problem and come up with the following
[CODE][/Sub NewFile_Button3_Click()
Dim strF As String
Dim FilePath As String
customerName = Sheets("NewFile").CBCusName.Value

A = 0
FilePath = "U:\Everybody!\Windmill_ Printed _Jobs\" & customerName & "\"



Sheets("File_List").Range("A:E").Clear
strF = Dir(FilePath)
With Sheets("File_List")
.Columns("A").ClearContents
Do While Len(strF) > 0
.Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = strF

strF = Dir()
A = A + 1
Loop
End WithCODE]
This works but there is a difficulty which is that the customer name list I used to populate a combo box (line marked *****) is not exactly the same as the customer names that have been used to create the file names.

To overcome this I would like to be able to scan through all the members of Windmill_ Printed _Jobs and pick out the "Customername" part of the FilePath to use as a list for the combobox.
Dir() does not let me do this.
How can I do this?
John

[j_Southern]

I have managed to solve this problem myself by finding that you can display the windows file browsing window in VBA. A much more elegant solution than the one I had envisaged.
John