How to automatically refresh an excel sheet

[shina67]

Hi All,

I have an excel sheet that pulls information from an external source. The external source updates regular.
My query is :- How do I get my excel sheet to update automatically without having to do anything manually.
I need the excel sheet to this as it will be on a monitor as live data all day.

Thanks in advance.

 

[offthelip]

It depends on whetherthe external update triggers a wroksheet change event if it does then you can force a recalculation by putting this code inthe worksheet change evnt:

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sheet1").EnableCalculation = False
Worksheets("Sheet1").EnableCalculation = True
End Sub

Put your sheet name is as required.
if the update doesn't trigger a worksheet change event, what you can then do is put an equation into a spare cell that uses one of the cells which is updated by the external source and does a calcuation on it, e.g
assuming A1 is updated automatically and that it is text you can put this equation in Z100
=Len(A1)
This will trigger a worksheet calculate event so you can put the code above into that worksheet event, if you still need it. the function should have done it for you anyway.

 

[shina67]

It depends on whetherthe external update triggers a wroksheet change event if it does then you can force a recalculation by putting this code inthe worksheet change evnt:

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sheet1").EnableCalculation = False
Worksheets("Sheet1").EnableCalculation = True
End Sub

Put your sheet name is as required.
if the update doesn't trigger a worksheet change event, what you can then do is put an equation into a spare cell that uses one of the cells which is updated by the external source and does a calcuation on it, e.g
assuming A1 is updated automatically and that it is text you can put this equation in Z100
=Len(A1)
This will trigger a worksheet calculate event so you can put the code above into that worksheet event, if you still need it. the function should have done it for you anyway.

Thanks offthelip

My code is as follows:-

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'


ActiveWorkbook.RefreshAll
End Sub

I just need this to automatically run every 60mins.

Hope you can help

 

[offthelip]

if you wnat ot update it every half hour you can use a timer just add this to your code , it will keep running every half hour.

Sub Macro1()'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'


ActiveWorkbook.RefreshAll
tim = Now() + (1 / 48)
Application.OnTime tim, "Macro1"


End Sub

1/48 is 24 hours divided by 48 ie half an hour

 

[JavaGear13]

Here's some modified worksheet code I used for a similar project:

Private Next_Update As DatePrivate Frequency as Date


Private Sub Worksheet_Activate()
     Call Update_Sheet
     Frequency = timeSerial(0,60,0) 'the arguments are hours, minutes, seconds
End Sub


Sub Update_Sheet()
    Dim tm As Date


    If Next_Update > Now() Then Exit Sub 'Cancel if there is already an update scheduled


    
    Worksheets(1).Range("H1").Calculate 'use relevant cell here


    tm = Now + [frequency].Value
    [UpdateTime].Value = tm
End Sub

When running my code, I found that there were certain events that would interrupt my update function. By saving the scheduled time to Next_Update and checking if an update is scheduled, you can call Update as much as you like without having to worry about creating a cascade of updates.
Having an assigned variable makes it easy to adjust frequency for whatever reason.

 

[shina67]

if you wnat ot update it every half hour you can use a timer just add this to your code , it will keep running every half hour.

Sub Macro1()'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'


ActiveWorkbook.RefreshAll
tim = Now() + (1 / 48)
Application.OnTime tim, "Macro1"


End Sub

1/48 is 24 hours divided by 48 ie half an hour

Thanks for that. Will this only work if the workbook is open?
If so how do I make it work when the workbook is closed?

 

[offthelip]

you can get it to work on a closed workbook I suggest you look at this link to see if this can help;
http://www.snb-vba.eu/VBA_Application.OnTime_en.html

Can I point out you didn't specify this in your original requirement

 

[Fazza]

refreshing automatically requires no code - just a setting

for example, see https://support.office.com/en-us/ar...in-excel-1524175f-777a-48fc-8fc7-c8514b984440

On earlier versions it is simpler, just right click from the query & change the setting

This is for both auto refresh on file open, and, for refresh every n minutes (when the file is open)