Training

Do you ever want to change the default data refresh option for your Excel workbooks that are linked with ShowCase Query using Query References? Would you like to eliminate the prompt to refresh or keep the data that was last saved with your workbook? Or would you prefer to automatically refresh the query results without prompting? Here’s an easy way to eliminate the Refresh/Keep prompt (see Figure 1 below) that appears when you open an Excel workbook with ShowCase Query data.

Figure 1: This prompt appears after you open an Excel workbook with previously saved data.
(Click to enlarge)

ShowCase Query application program interfaces (APIs) exist to control several functions. In a workbook to which queries have been added, use ScSetOpenRefreshOption to change how query data should be refreshed when the workbook is opened. The syntax you’ll use is ScSetOpenRefreshOption(option).

Figure 2: Help for ScSetOpenRefreshOption; this changes how query data will be refreshed.
(Click to enlarge)

Use the API function to create an Excel macro in the workbook with a ShowCase Query added (see Figure 3 below):

Private Sub Workbook_Open()
Dim result As Boolean
'option 2 eliminates the Keep/Refresh prompt; ‘option 1 will automatically run
result = Application.Run("ScSetOpenRefreshOption", 2)
End Sub

Figure 3: The macro you’ve created.
(Click to enlarge)

A user event can be any Visual Basic (VB) macro or Microsoft Excel macro. (For information on creating these macros, see the Microsoft Excel documentation.) As illustrated in Figure 4, WORKBOOKNAME.xls!MODULENAME.MACRONAME is the syntax for defining a user event.

Figure 4: This is a sample user event in ShowCase.
(Click to enlarge)

The Excel macro will use the API to control the ShowCase Query data refresh behavior. Now, when you open the workbook, the data will automatically refresh, and you will no longer see the Keep/Refresh prompt.