Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting or Referencing named cells in multiple spreadsheets
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting or Referencing named cells in multiple spreadsheets
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 "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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting or Referencing named cells in multiple spreadsheets
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting or Referencing named cells in multiple spreadsheets
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. "Gary''s Student" wrote: 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 "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating only non-empty cells... | Excel Worksheet Functions | |||
link multiple cells to multiple cells | Excel Discussion (Misc queries) | |||
link multiple cells to multiple cells | Excel Discussion (Misc queries) | |||
How can I have excel search and add multiple cells to find a targe | Excel Discussion (Misc queries) | |||
How to Link named ranges from multiple Workbooks into a single Wo. | Excel Discussion (Misc queries) |