[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

macro to refresh data in all 35 worksheets same work book

Hi Experts
using Access and Excel 2003

I currently have an excel workbook with 35 sheets that are linked to different access qrys which I manually refresh each day.

I need a macro, so when the workbook is opened it automatically refreshes excel with the most current data from each of the qtys from ms access.

in each sheet row one is headers and then data from row 2 onwards.
0
route217
Asked:
route217
  • 2
  • 2
2 Solutions
 
mikeyd234Commented:
This should work:
Sub calcSheets()

  Dim WS_Count As Integer
  Dim I As Integer

  ' Set WS_Count equal to the number of worksheets in the active
  ' workbook.
  WS_Count = ActiveWorkbook.Worksheets.Count

  ' Begin the loop.
  For I = 1 To WS_Count

    ActiveWorkbook.Worksheets(I).EnableCalculation = False
    ActiveWorkbook.Worksheets(I).EnableCalculation = True

  Next I

End Sub

Open in new window

0
 
Rgonzo1971Commented:
Hi,

pls try

Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.ScreenUpdating = True

Open in new window

0
 
mikeyd234Commented:
Ignore my first post, use this one liner :)

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

Open in new window


You can add that into the workbook open event or into a sub :P

More info:
http://msdn.microsoft.com/en-us/library/office/ff195741.aspx
0
 
route217Author Commented:
thinks for the feedback let me test.
0
 
route217Author Commented:
Hi Experts

silly question if I have 7 worksheet s what line of code do I change to accommodate. .
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now