Updating numerous ssheets at once 1

[postmanphat]

Hi, I am running an Access database that updates 28 spreadsheets. These 28 spreahseets then feed into 1 'master' spreadsheet.

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???

Thanks in advance

Dave

 

[SkipVought]

Hi,

"These 28 spreahseets then feed into 1 'master' spreadsheet. "

How? Linked cells? QueryTables? Copy 'n' paste?

I believe your answer will require VBA.

Skip,

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

 

[postmanphat]

Yep, the spreadsheets are linked to a query in Access. When you open the spreadsheets you have to click on Refresh Form Data which retrieves the data from the query.

It would also be really handy if anyone knows a way that the spreadsheet can refresh all upon opening it, instead of manually having to go up and click 'Refresh',

Thanks

Dave

 

[SkipVought]

You could set the Data Range Properties to refresh on open.

OR you could run a macro like this in the Workbook_Open event...

dim ws as worksheet, qt as querytable
for each ws in worksheets
  for each qt in ws.querytables
    qt.refresh false
  next
next

Any further code discussion ought to be done in Forum707

Skip,

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

 

[postmanphat]

Thanks for your help there Skip - Data Range properties solves the refresh on open issue. Excuse my idiocy, but what/where is forum 707???

 

[SkipVought]

Forum707.

Skip,

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

 

[postmanphat]

Changing the data range properties worked a treat for the second bit of my problem. Cheers! Have a star...