Consulting

Results 1 to 4 of 4

Thread: Vba To Refresh Specific External Connections

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Joined
    Feb 2021
    Posts
    5
    Location

    Vba To Refresh Specific External Connections

    I have 3 External web Connections. I use code below to start and stop automatically refresh, but it refreshed All connections.

    I need 2 Things:
    1- just Refreshing One of Connections: "ConnectionA".
    2- Use a cell value to Assign Seconds to repeat refreshing. for example value of cell "A2" is 45, so auto refresh every 45 second.

    Public RefreshTime As Double
    Public Const Seconds = 30 'Input Refresh Interval in seconds
    
    
    
    
    Sub StartRefreshLoop()
    
    
    'User Message indicating loop is beginning
      MsgBox "Refreshes will begin to occur at " & _
        "the designated interval of " & Seconds & " seconds"
    
    
    'Call the first Refresh
      Call StartRefreshes
    
    
    End Sub
    
    
    
    
    Sub StartRefreshes()
    
    
    'Calculate Next Refresh Time
      RefreshTime = Now + TimeSerial(0, 0, Seconds)
    
    
    'Trigger a Refresh with OnTime function
      Application.OnTime _
        EarliestTime:=RefreshTime, _
        Procedure:="RefreshConnections", _
        Schedule:=True
      
    End Sub
    
    
    
    
    Sub RefreshConnections()
    
    
    'Refresh Data Connections
      ThisWorkbook.RefreshAll
    
    
    'Start Timer Over Again
      Call StartRefreshes
    
    
    End Sub
    
    
    
    
    Sub EndRefreshLoop()
    
    
    'On Error Resume Next
      Application.OnTime _
        EarliestTime:=RefreshTime, _
        Procedure:="RefreshConnections", _
        Schedule:=False
    
    
    'User Message indicating loop has ended
      MsgBox "Refreshes are no longer occurring"
    
    
    End Sub

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,126
    One of the properties of each 'web-connection' is its 'refresh rate'.
    You con't need VBA to do the refreshing.

  3. #3
    VBAX Newbie
    Joined
    Feb 2021
    Posts
    5
    Location
    You are right. best way to activate regularly refresh is on "Properties". but in this way it get refresh until you uncheck it again. I mean when you need 3 Hours a day to refresh 1 per minutes, and after that hours, you should disable update and do it day by day.
    I was looking for an easy and more accessible way by press a button to start 30 second or 1 minuets update and after for example 15PM by pressing button refresh stopped.

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,303
    You're using refresh all in your code, you can refresh on a table by table approach, for example (depending on what kind of query it is):
    Range("H2").ListObject.QueryTable.Refresh    'the query associated with a Table at a cell
    Sheets("Sheet1").ListObjects("Table1_2").QueryTable.Refresh
    Range("Table1_2").ListObject.QueryTable.Refresh    'don't need to refer to the sheet
    'without a Table (listObject):
    Range("A1").QueryTable.Refresh
    Sheets("Sheet1").QueryTables(1).Refresh
    Sheets("Sheet1").QueryTables("mytblName").Refresh
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •