Good morning all

I have a small dilema, which I will try to explain for you;
I recieve approx 80 seperate spreadsheets each week from a team o
filed reps. I need to extract data from certain cells on these sheet
and collate them into a master report, preferably without opening u
each sheet seperatley.
After a bit of searching, I found this fantastic little VBA macro tha
will do exactly this, but for only one cell value. My question is, ca
this be adapted to import more than one cell from each sheet?

Sub ReadDataFromAllWorkbooksInFolder()
Dim FolderName As String, wbName As String, r As Long, cValue A
Variant
Dim wbList() As String, wbCount As Integer, i As Integer
FolderName = "C:\Link Reports"
' 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
For i = 1 To wbCount
r = r + 1
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "LIAR"
"C131")
Cells(r, 1).Formula = wbList(i)
Cells(r, 2).Formula = cValue
Next i
End Sub
Private Function GetInfoFromClosedFile(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


What I get from this code is, in column A are all of the file name
from the "Link Reports" folder, and in column B are the values fro
cell C131. Is it possible to have another cell value in column C etc
etc.

Many thanks in advance for any suggestions you have.



--
Message posted from http://www.ExcelForum.com