ExcelBanter - How do I link data workbooks to a create a graph?

hi Andrea,

Many things are possible in Excel & I'd say that you could probably
find a way of linking all the workbooks. However, I think any "easier"
approach would involve opening each file & copying the data from each
sheet in each file to a single sheet in one file (with an extra column
identifying the original source) so it can become a single source of
data for charting (ie a "database" approach).

This approach will make it easier for grouping your data in various
ways such as by day, week, month or quarter etc. It can be done by
creating a macro that will do all the repetitive consolidation
automatically. To do this we need some more details though...

Are all files in the same folder?
If so, are there any other files in the folder?
Do the monthly files contain sheets for every day of the month or just
the weekdays?
Do all the sheets have the same layout (ie number of header rows, same
columns, how many rows - same or variable between sheets*)?
What is the naming convention of the sheets?
Are there any other sheets in each file?
* If it is it likely that the total amount of rows would be more than
65,536 rows (Excel 97-2003) or 1 million (Excel 2007) then some
precautions can/need to be added into the macro code.

The below link shows an example of consolidating single sheeted files:
'Excel programming Consolidate Workbooks with Single header Row'
(http://tinyurl.com/mv547t)

Rob

--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105363