Hi,

I have folder contains 10 to 15 excel file.


User want the data from any 3 files of that folder,


Any code that will display or pop up the open dialog box to select
the
3 files of that folder


and Three files data to be copied and to be pasted in new excel file.


Thanks in advance.

this code will import data to worksheet "SOME_NAME" from worksheet
"ANY_NAME", in "fname" file, located in "path" directory, adjust
ranges to suit
hope I didn't mess up with any arguments
let me know if you have any problems

Sub pick_file()

ChDir "your_directory"

Set fd = Application.FileDialog(msoFileDialogFilePicker)
catalog = vbNullString


With fd
..AllowMultiSelect = False
..Filters.Clear
'.Filters.Add "Excel files", "*.xls", 1
..InitialView = msoFileDialogViewList
..Title = "PICK A FILE"
..Show
End With



fname = Application.FileDialog
(msoFileDialogFilePicker).SelectedItems.Item(1)

For i = Len(fname) To 1 Step -1
If Mid(fname, i, 1) = "\" Then
path = Left(fname, i)
fname= Right(fname, Len(fname) - i)
Exit For
End If
Next i


sht_name = "ANY_NAME"


'range to be imported is X17:AF17, range to be populated with data is
B23:J23
'ADJUST BOTH TO SUIT
For c = 24 To 32
celll= Cells(17, c).Address
Sheets("SOME_NAME").Cells(23, c - 22) = GetValue(path, fname,
sht_name, celll)
Next c


End Sub

where GetValue is:

Public Function GetValue(path, fname, sht_name, celll)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the path exists
If Right(path, 1) < "\" Then path= path& "\"
If Dir(path& fname) = "" Then
GetValue = "path Not Found"
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & fname & "]" & sht_name & "'!" & Range
(celll).Address(, , xlR1C1)

' Execute an XLM macro
GetValue = Application.ExecuteExcel4Macro(arg)
End Function


On 24 Mar, 15:14, wrote:
Hi,

I have folder contains 10 to 15 excel file.

User want the data from any 3 files of that folder,

Any code that will display or pop up the open dialog box to select
the
3 files of that folder

and Three files data to be copied and to be pasted in new excel file.

Thanks in advance.