Using Microsoft Office for Mac as a Relational Database

By Jim Gordon, co-author of Office 2011 for Mac All-in-One For Dummies.

Part 8 - Queries - Refreshing data

In part 8 we will refresh the data in the report we made in Part 7.

The quick, easy way to refresh your data is click the Refresh button on the Data tab of the Ribbon.

AUTOMATIC REFRESH
You can tell Excel to automatically refresh your data when the workbook is opened by using one of three methods.

1. When you first return data to Excel from Microsoft Query, in the Returning External Data to Microsoft Excel dialog shown in
Part 7, click the Properties button to display the External Data Range Properties dialog. Click the check box for Refresh Data on File Open, then click the OK button.

2. Display the External Data Range Properties dialog by this round-about method:
    a. Right click in the data range
    b. From the pop-up menu choose Convert to Range
    c. Right click in the converted range and choose Data Range Properties from the pop-up method
Then
click the check box for Refresh Data on File Open, and then click the OK button.

3. Use a Visual Basic for Applications (VBA) macro. Here is an example. Make .RefreshOnFileOpen = True or False as desired

Sub AdjustQueryTableProperties()
    'Select a cell within the result set
    With Selection.QueryTable
        .RefreshOnFileOpen = True
    End With
End Sub

If you want to programmatically refresh the query table with the vba Refresh command, you must first convert the default Table (a list object) to a Range obect before refreshing the table. To convert, right click in the table and choose Convert to Range. Another way is to use VBA. Change the table properties .UseListObject = False

VBA .RefreshPeriod property is not supported.

REFRESH ON DEMAND
Select a cell anywhere in the data range, then click the Refresh button on the Data tab of the Ribbon. You can refresh just the selected query or all queries within the workbook.

You can use VBA to refresh on demand:
Sub RefreshNow()
    Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

Part 1

Part 7 (Previous)

Part 9 (Next)