Hi,

I have been working with this Macro, but I have some issues getting it
to work the way I would like.

I am attempting to do the following:

Creat a master spreadsheet that will collect data from many identical
spreadsheets all contained in one directory. Each spreadsheet in the
directory contains a summary sheet with one row that I need to bring
into the master spreadsheet in one single worksheet.

With this macro I am able to retrieve one cell just fine, but when I
try to specify to get data from A2 through J2 it brings back a total
into one cell in the master. Does anyone know how I can modify this
macro to bring in rows or many cells instead of just one?

Also if thier is a better way to do this I would be greatful for
suggestions.

Thanks in advance.....


Sub Import()
Dim FolderName As String, wbName As String, r As Long, cValue As
Variant
Dim wbList() As String, wbCount As Integer, i As Integer
Dim MyInput As String
MyInput = InputBox("Enter Directory Path To Your TE's I.E. D:\TE")
FolderName = MyInput
' create list of workbooks in foldername
wbCount = 0
wbName = Dir(FolderName & "\" & "*.xls")
While wbName < ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
' get values from each workbook
r = 0
Workbooks.Add
For i = 1 To wbCount
r = r + 1
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "A2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "B2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "C2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "D2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "E2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "F2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "G2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "H2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "I2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "J2")
Cells(r, 1).Formula = wbList(i)
Cells(r, 2).Formula = cValue
Next i
End Sub

Private Function FinishImport(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) < "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function