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