Try something like this

Sub ExtractfromFiles()
Dim aWB As Workbook
Dim aWS As Worksheet
Dim oWB As Workbook
Dim opath As String
Dim oname As String
Dim myRange As Range

Set aWB = ActiveWorkbook
Set aWS = ActiveSheet

Debug.Print "awb.name = ", aWB.Name

opath = InputBox("enter filepath")
If Right(opath, 1) < "\" Then
opath = opath & "\"
End If
oname = Dir(opath & "*.xls")
Debug.Print oname

icount = 0
Do While oname < ""
Debug.Print oname
Set oWB = Workbooks.Open(opath & oname, ReadOnly = True, UpdateLinks =
False)
icount = icount + 1
Workbooks(aWB.Name).Sheets(aWS.Name).Cells(icount, 1) = opath & oname

'This assumes that the range names are the same in all workbooks and that
the range name
' are workbook named ranges as opposed to worksheet range names.

Set myRange = Nothing
Set myRange = Range("Range1")
If Not myRange Is Nothing Then _
Workbooks(aWB.Name).Sheets(aWS.Name).Cells(icount, 2).Value =
myRange.Value

Set myRange = Nothing
Set myRange = Range("Range2")
If Not myRange Is Nothing Then _
Workbooks(aWB.Name).Sheets(aWS.Name).Cells(icount, 3).Value =
myRange.Value

Set myRange = Nothing
Set myRange = Range("Range3")
If Not myRange Is Nothing Then _
Workbooks(aWB.Name).Sheets(aWS.Name).Cells(icount, 4).Value =
myRange.Value


oWB.Close
oname = Dir

Loop

End Sub



"Bonehead" wrote:

I'm probably asking for the sun and the moon, and I'm not sure if this
doesn't better belong in a general Office or Access forum, but what the hey.

People use a workbook template to create construction bids. I want to
report on that data. Specifically, I want to be able to report on named
cells in each workbook -- Final Price, labor ratio, etc. The workbooks are
all stored in a single directory/folder. I'd like to be able to create a
table at any time of all the data in all the named cells in all the
workbooks. This could be data reference or, probably simpler, data
extraction. I can use Excel or Access to extract or manipulate the data.


In other words, I'd like to be able to come up with a table in which each
spreadsheet in a given directory/folder was a record and each named cell was
a field. New spreadsheets will be added to the directory and I'd like to be
able to account for them. I'm not worried about duplicates, because I can
eliminate them in Access or Excel easily enough.

Is there a way to do this without complicated programming? I've done rather
complex things with Excel and Access, but I'm not a programmer.