Refreshing numerous excel ssheets en masse

[postmanphat]

Hi, I am running an Access database that updates info 28 spreadsheets from a query in the db. These 28 spreadsheets then feed into 1 'master' spreadsheet as a kind of summary.

The problem is that I have to go into each one of the 28 spreadsheets and refresh them all individually first in order for any changes to be shown on the master sheet.

Is there a way that I can refresh all 28 sheets at once, perhaps from the master spreadsheet??? Ideally, a macro would run on my master sheet that refreshed the data on all the 28 spreadsheets, then brought the data through onto the master sheet.

Thanks in advance

Dave

 

[SkipVought]

Did you try to code I posted in Forum68.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

Did you try to code I posted in thread68-1464430.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[postmanphat]

Hey Skip,

Wasn't that code so that the individual spreadsheets refreshed 'on open'? Like I said on the previous thread, that bit works now due to the change you suggested to the Data Range Properties. So that bit is sorted thanks.

A bit of background:
The access database is a system that monitors financial expenditure throughout the county, which is split into 28 localities (hence the 28 ssheets). Each of these individual spreadsheets gets its data from the mdb and using simple formulae calculates net spend/balance etc. No problem. The 'Master' spreadsheet is a summary of all the 28 locality spreadsheets that simply links back to 2 cells on each spreadsheet: 'Amount spent' and 'Balance'

My current situation is this: If I want to look at the Master spreadsheet, I first have to open up all of the 28 spreadsheets individually so that the data refreshes, and populate the ssheet to get it fully up to date. Only then can I get accurate figures when I open up the Master sheet.

If I open up the master sheet without refreshing the 28 others, they will show whatever was on them the last time they were opened and refreshed, regardless of how much I've updated my database since then.

 

[Bong]

Wouldn't it be better to have your master collect data directly from the database(s)?

_________________
Bob Rashkin

 

[SkipVought]

"updates info 28 spreadsheets"

You meant to say, "updates info 28 WORKBOOKS."

You can query your Access DB directly from the Master to generate the appropriate summary information.

BTW, it is not a particularly good idea to summarize from queries (reports) in various locations. Query the SOURCE!

It is also not a particularly good idea to generate 28 different workbooks. You could have done ALL this from ONE workbook with ONE query, parameterizing the field criteria that discrimiates the 28.

Just MHO.



Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[postmanphat]

It is also not a particularly good idea to generate 28 different workbooks. You could have done ALL this from ONE workbook with ONE query, parameterizing the field criteria that discrimiates the 28" I tried this but after the query reached a certain size, it just died, so I had to split it over the 28 workbooks.

I think I will look into getting my master to query the DB directly. Thanks for your help.

 

[SkipVought]

"...but after the query reached a cerain size..."

Please post 2 or 3 of your queries. Maybe we can help you streamline things.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}