Refreshing data from multiple large workbooks

Hi all, I currently have a summary sheet with links to about 40 workbooks (this will increase over time) which are each approx 1.5mb in size. The trouble is re-freshing the data is currently taking some time each time the summary is opened. I was planning to dump the relevant data as a value from the 40 or so workbooks each time they are saved so that a refresh would not be needed. However I wondered whether there is a better method that anyone has found for linking multiple large spreadsheets to a summary? :dunno

Thanks for your time,
rrenis

If different people are working with the 40+ books, it may be best to export the summary data to a database, which is more suited to shared access. Your summary sheet can query the database for the required information.
Data can be extracted from closed workbooks.
Can you expand on your detailed requirements?

Hi, thanks for the reply. Unfortunately I have no experience with Access although I'm sure your suggestion is the way to go. It's just it will take a while for me to learn access in my own time so I'll have to make this work some how in excel for the time being. :think:

Just to provide a bit more info, I'm currently using VBA to dump the relevant info from specific cells to a new line on the summary whenever a new workbook is created (hence the approx 40 spreadsheets will grow steadily). When the summary is opened at the moment it is reading the data from the 40 or so closed workbooks (approx 1.3mb in size) located on a server folder. Whilst refreshing data was OK at first it's now becoming an issue and as the number of workbooks linking into the summary will only increase it's forced me into re-thinking my current approach.

My only thought is to use VBA to dump the relevant cell values to the summary sheet of each currently linked workbook by attaching it to the workbook close event therefore clearing the links in the summary sheet. Although what I know about VBA I've learned from this site and various google searches I think this idea is do-able (how is another story! - the lookup and replace - on the summary - each time I close a workbook to update the data I'm not too sure about). Luckily each spreadhsheet has a unique reference in one of the columns on the summary so I could maybe look that up and replace the contents of that row in VBA (?)

My concern is what happens when someone tries to update the summary whilst new data from another workbook is being updated - maybe a check exists in VBA to check whether a workbook is open (I'll google that one) and present a message box for the user to try again - would be great if it could include the username of the person who has the summary open, but this may be taking things too far?

I've rambled on a bit here but hopefully it may be enough to either confirm it's worth pursuing or maybe suggest another way to acheive this using excel and not access, although I'm sure long term I'll have to migrate over.

Many Thanks,
rrenis

Hi rrennis,
Understanding Access is not really an issue. Data can be exported to an mdb file and read from there by Excel (see this KB item). It avoids the sharing issue in using an Excel file.
Can you post a sample to show the complexity of what you need to save/download. Maybe that wil suggest other possibilities.

Hi mdmackillop :hi:

Thanks for your response - I never knew it was possible to read the contents of excel sheets and dump them to an access file. Presumably all of the workbooks could dump to the same mdb file simultaniously and the summary would import the contents of the mdb?

With regard to the data some of the cells will be formula results from calculations on other sheets within the workbook. It's only about 10 cells or so per workbook which is a collection of dates, figures and text. I'm not at work at the moment so I've cobbled together a rough (very rough!) idea of what I'll need to take from each workbook and how it's currently presented on the summary.

Thanks for your continued help with this, it's much appreciated :bow:

Cheers,
rrenis

Hi mdmackillop - I had a quick look at your kb link last night and opened up the access file. Do I need to create this prior to exporting from excel? Also is VBA required to export from excel to access or is there an export facility which allows you to select the data from a sheet? :dunno

cheers,
rrenis

Hi rrenis,
Making some progress. The Export is giving inconsistent results and I'm not quite there with the import using the same methodology
Regards
MD

Hi mdmackillop,

Thank you so much for investing your own time into providing a solution to my problem!! :bow:

I'll have a look at the VBA you've done so far and try and get a my head around how and what it's doing.

Once again, many thanks for your help which is very much appreciated! :cloud9:

Have a good weekend.

cheers,
rrenis
:beerchug:

If the fields are static across all the workbooks, you can dump data into Access using the following (super easy to use) :
[vba]
Sub DAOFromExcelToAccess()
Sheet5.Activate ' make sure proper sheet is selected to
load data
' exports data from the active worksheet to a table in an
'Access database
Dim db As Database, rs As Recordset, r As Long 'declare
'variables (DB & table)
Set db = OpenDatabase("I:\filepath\databasename.mdb") '2003 DB
' open the database
Set rs = db.OpenRecordset("TYPE_TABLE_NAME_HERE", dbOpenTable)
' get all records in a table
r = 1 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
.Fields("DATE") = Range("B" & r).Value
.Fields("EMPLOYEEID") = Range("C" & r).Value
.Fields("TIME") = Range("D" & r).Value
.Fields("SALE") = Range("E" & r).Value



' add more fields if necessary...

.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

'Call CountData 'count records loaded
ScreenUpdating = True

MsgBox ("successful!")

End Sub
[/vba]
Make sure you add the reference in VBA to Microsoft DAO 3.6 (excel 2003, or the other one if your in older version. You can even take it one step futher and run an update query in Access to dump it into Oracle/SQL Server if you don't like Access.

You can also use similar code to run queries from within Access too (i.e deletes).

Also - if your looking for the reverse (access to excel) it's super easy and can be done with zero code! If you need details let me know. I'm guilty of skimming through this thread ... :)

The return of data is also required.
The purpose is to allow multiple users to save to a shared location, and the return of data to one workbook.
Thanks for helping out
Regards
MD

Hi debauch, Thanks for your code - I'll have a go with it and see how I get on as mdmackillop has opened up my interest in finally getting to grips with Access - it's a program I'd never even opened up until last week!!
:beerchug:
Cheers,
rrenis.