MS EXCEL / REFRESH DATA

[scottcsherman]

I have a excel spreadsheet with about 20 pages. For 10 of the pages the data is brought in from a query from Access. Does anyone know of a way to get it to refresh all the worksheets at once, without clicking each worksheet and then clicking refresh data?

Thanks
Scott
scott_c_sherman@yahoo.com

 

[SkipVought]

Hi,

Try this...
RefreshAll Method
See Also Applies To Example Specifics
Refreshes all external data ranges and PivotTable reports in the specified workbook.

expression.RefreshAll

expression Required. An expression that returns a Workbook object.

Remarks
Objects that have the BackgroundQuery property set to True are refreshed in the background.

Example
This example refreshes all external data ranges and PivotTable reports in the third workbook.

Workbooks(3).RefreshAll

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[scottcsherman]

Skip

Do you think this would work even though I'm not using Pivot Tables? I am populating cells from an access query that then populate an excel line / bar graph.

Thanks

Scott

 

[lestatdelioncourt]

there is a property on the external queries that refreshes the data when you open the spreed sheet. set this to yes.

You can do this by right clicking the data and editing the properties

 

[SkipVought]

I dunno or sure, but I'd try

Other than that I'd code looping thru each worksheet in the worksheets collection and do a refresh. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com