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
