I have created an app that allows the user to specify one or more spreadsheets as a datasource using getopenfilename.

Now I would like to pull names from the open spreadsheet(s) to populate a drop down in the app so the user can select data to import from the datasource. Any ideas?

I have created an app that allows the user to specify one or more spreadsheets as a datasource using getopenfilename.

Now I would like to pull names from the open spreadsheet(s) to populate a drop down in the app so the user can select data to import from the datasource. Any ideas?

Hi,
I am not sure what u r asking, for my understanding u r trying to get some data from another file, paste them on a sheet of your workbook (eg. a column of data) then u try to make a program to allow user to select only those data, m i right?

Here r a piece of code which use validation using vba

'this code is try to get how many row of records that you have in the column
dim rowNum as String
ActiveSheet.range("AF65536").Select
Selection.End(xlUp).Select
rowNum = ActiveCell.Row 'If column AF has 10 selections, rowNum = 10 in String

ActiveSheet.range("AF2").Select
'because i get data from other sheet, then i need to use this code where OtherStaff is the sheet name
ActiveCell.FormulaR1C1 = "=OtherStaff!RC[-30]"
Selection.AutoFill Destination:=range("AF2:AF" & rowNum), Type:=xlFillDefault

'here is where i change the cell C7 validation to different selection.
'careful, u need to make Cell C7 a validation list first, which u can find it on the excel menu->Data->Validation
ActiveSheet.range("C7").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$AF$2:$AF$" & rowNum
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With


Hope these helps.

thanks for the assistance...I'll give it a try. Where I am running into confusion is the fact that the user is able to specify multiple workbooks so my code has to consider which workbook(s) the user specified in step 1, then search them for a specific field (say customer name) and return data on just those customers. I'd also like to allow flexibility so the user can search based on a different criteria (say zip code).

There are a couple ways of going about this. (1) if the user is specifying workbooks in a multi-select getopenfile dialog the results are returned in an Array. You can retain the array data in a Global Variable or by writing it to a location, text file or hidden workbook. (2) suppose you want to check all the open workbooks. You can use a loop to check each book.
A good idea for something like this is to use a Named Range to contain the data in your workbooks. Then if you are search for data in several books by a named range you can still find it if the position varies from book to book. Although if you call a named range where the range does not exist you will produce an error. you must handle the error unless you are 100% sure the named range is always going to exist.

If this is going to be a frequent process, you may want to consolidate this data into an index file or a database table where you can search all the data in a single location.