Extracting or Referencing named cells in multiple spreadsheets

[Bonehead]

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.

 

[Gary''s Student]

What you want to accomplish can be done with links.

You can set the value of a cell equal to the value in another worksheet or
workbook.

=[Book1]Sheet1!$G$9 or a similar formula. If the link is to another
workbook, Excel will ask if you want the data refreshed when your workbook is
openned.

 

[Barb Reinhardt]

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]

Thank you. I know I didn't do a very good job of defining the real problem.
The problem is that users are going to create bids every week and save them.
I want to be able to get to that data without reentering it into another
spreadsheet or database or adding another cell reference to the spreadsheet
for each new workbook. In other words, I want to be able to enter something
that returns the "final bid" range for all the workbooks in a given directory
without having to name those workbooks -- kind of a x:\*.*\final bid ref.

I'm going to try to swim through Barb's response and see what that means.
Thanks for your help. I appreciate it immensely.

What you want to accomplish can be done with links.

You can set the value of a cell equal to the value in another worksheet or
workbook.

=[Book1]Sheet1!$G$9 or a similar formula. If the link is to another
workbook, Excel will ask if you want the data refreshed when your workbook is
openned.
--
Gary's Student

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.