How To Automatically Update Data In Excel

There are two ways to make Excel automatically refresh data connections and recalculate a worksheet.  These tricks are of import with the Stock and Currency data type in Excel 365 but as well other situations.

Ordinarily Excel will update itself when you lot change a cell value.  These days there are situations where cells alter value only Excel does NOT update the worksheet.  In other words, modern Excel has changed in ways that Microsoft hasn't yet fully adapted to.

Ideally, Excel would take an overall setting to refresh the worksheet every 'n' seconds or minutes.  In 2022, Microsoft finally added some Stock/Currency auto-refresh options but they are incomplete and limited.

This article has 2 different means to force updates in Excel. One is a 'traditional' macro approach, the other is a newer play a trick on using PowerQuery in conjunction with single macro.

Either option gives yous more control over updating than Microsoft's simplistic 'tick the box' addition to Excel 365.

Stock Data Type

With the Stock Data Type  the power to update automatically is more than important.  Users volition want their worksheets to catch the latest prices automatically, something the current preview releases tin't do.  Instead of having a nice automatic 'ticker', nosotros're expected to click 'Refresh' to get the latest prices.

The Stock, Currency and other linked information types are curious beasts. They are data connections to external sources but do NOT announced as Excel Data Connections.  That ways y'all can't setup an automatic data refresh, equally you would with normal data connections.  In fact, there's no exposed controls for the Stock, Currency or other linked data types.

Now() and other volatile functions

The Now() function updates to the latest date and time whenever Excel recalculates the worksheet. But if there'south nothing to make that happen, Now() doesn't modify value.  Some external factor is needed to brand Excel update At present() and the residue of the worksheet.  In other words, you should be able to glance at a worksheet and know it's upwards to the second simply that's not possible with Excel 'out of the box'.

Microsoft calls At present() and similar functions 'volatile' because their values can change even if no other cells have changed.  Other volatile functions are Today(), Randbetween(), Offset() and Indirect().  In some situations Info(), Cell() and SumIf() can also exist volatile.

VBA custom functions can likewise exist tagged as volatile using this line in the part code:

Awarding.Volatile

That line makes the function run anytime Excel updates/recalcs the worksheet.

The play a trick on is to make an Excel macro which forces data connections to update and so marking that function every bit Volatile so it volition exist run whenever the workbook is updating.

Maybe you want your worksheet to update automatically equally a precaution?  Many sometime Excel hands remember situations where Excel hasn't properly updated so they like the 'chugalug and braces' approach (at least occasionally).

Automatic update lawmaking

The standard method of forcing automated update of Excel is a brusque snippet of VBA lawmaking. Hither's what we use, there are many variations on the same theme. The full lawmaking is at the bottom of the article.

In that location are three functions.

RefreshAllDataConn

does the actual refreshing of data connections ( Workbooks(ThisWorkbook.Name).RefreshAll ) and nosotros added 2, optional,  lines to display the last time refreshed on the bottom status bar.

If you wanted to be actress conscientious, add line to explicitly force recalculation.  Either  ActiveSheet.Calculate  or the extreme Application.CalculateFull (use sparingly, this would deadening down a large worksheet).

AutoRefresh

run the RefreshAllDataConn sub every minute or whatsoever value yous assail the line  Awarding.OnTime At present + TimeValue("00:01:00"), "AutoRefresh"

Workbook_Open

an in-built Excel function that runs automatically when the worksheet is opened.  In this instance it starts AutoRefresh.

PowerQuery data Connexion workaround

The disadvantage of the VBA approach is that a .xlsm worksheet is necessary (macro enabled Excel worksheet).  At that place can be issues sharing macro enabled files considering of security concerns.

The arrival of PowerQuery / Get and Transform ways in that location'southward some other way to strength a worksheet recalculation.  It'due south a workaround and not perfect, but it'south possible and doesn't need a macro-enabled worksheet.

In curt, ensure that there's a data query setup with auto-refresh.  If at that place isn't a data connection, add a pocket-size one to the worksheet.

Once you have an machine-refreshing query, the worksheet including whatsoever volatile functions should also refresh.

Ideally the Stock and Geo information types should also refresh.

Whatever Excel data query comes with some refresh options in the Query properties.  Most of them default OFF.

Refresh every nnn minutes –  defaults off with 60 minutes suggested.

Refresh information when opening the file

Enable background refresh

Refresh this connexion on Refresh All

The car-refresh workaround is to create a minor and practically insignificant data connectedness.  Then configure that information connection to update every minute or whatever time you wish.  That should force the worksheet to update including the volatile functions mentioned to a higher place.

Some versions of this workaround add a link to a tiny csv file on the aforementioned estimator.  We've got the aforementioned event using a data connection from a table in the worksheet.

Create a minor table with a single cell.  The cell tin can have anything but we create a jail cell with Now() in it, for reasons we'll explain after.

Select the tabular array then choose Data | Get Information | From other sources | from Table/Range.  The verbal card detail maybe unlike depending on your version of Excel.

When the Query Editor opens, but Close and Load information technology.  In the data connections pane, you lot'll see a query.

Right-click the query, choose properties to see the settings we demand.

Set the refresh rate that suits y'all.

To be tidy, we movement the source table (right) onto the same sheet at the loaded query (left).  Because we used NOW() in the source tabular array cell, it'll exist easy to see when/if the worksheet has refreshed.

VBA Code example

Sub RefreshAllDataConn()     ' Refresh all Information Connections.      ' This should include Stock and Currency data types, even though they aren't listed.      Workbooks(ThisWorkbook.Proper name).RefreshAll      ' Show update time on status bar to confirm.     ' annotate these lines out if not needed.      Awarding.DisplayStatusBar = Truthful      Application.StatusBar = "Refreshed at: " & At present()  Terminate Sub     Sub AutoRefresh()  ' to run a Refresh All on the workbook every n minutes     RefreshAllDataConn     ' Repeat every minute or change to whatever value you prefer.     Application.OnTime Now + TimeValue("00:01:00"), "AutoRefresh"   ' this is a elementary instance. There'south no coded way to exit this function.  Finish Sub  Private Sub Workbook_Open()  ' Starts the automatic refresh when the workbook is opened,  ' commented out as a precaution.      ' AutoRefresh  Cease Sub

Excel's Stock / Currency information (kinda, sorta) gets auto-refresh
Make automatic Excel worksheet list or table of contents

Complete Excel NetworkDays() solution with holidays & vacations

Source: https://office-watch.com/2022/excel-automatic-refresh-recalculation-tricks/

Posted by: smithaftervare.blogspot.com

Share this post