Showcase Query Excel Add-in API's

 

With the Showcase Query Excel Add-in, you can link queries into Excel and choose actions from several different commands from the Showcase menu item. The API text makes these commands available outside of the menu selections. They allow you to create buttons within your spreadsheet in order to complete the menu tasks. One of the most used API commands is the ScRefreshQuery. This API allows you to refresh a specific query by simply clicking on a button created through Visual Basic.

Creating a Command Button with Visual Basic

1. From the menu bar in Excel, select View and choose Toolbars.
    
2. Select the Control Toolbox
    
3. The following Toolbar will appear: (ensure that design mode is enabled by clicking on the drafting triangle icon in the top left corner of the Control Toolbox).
    
4. Click the Command Button icon on the toolbar. This action changes the cursor into a crosshair.
    
5. Position the crosshair to a spot on the spreadsheet where you would like your command button.
    
6. Click and drag the mouse, which will draw a button on the spreadsheet. Release to view the new button with resizing handles
    
7. With the new button as the selected object, click the Properties icon (which looks like a hand holding a list in the top right corner) on the Control Toolbox.
    
8. Type RefreshQuery in the Properties edit box next to the (Name) parameter.
    
9. Type Refresh Query in the Properties edit box next to the Caption parameter. At this point, you may want to resize the button by clicking and dragging on the resizing handles.
   
   Attaching the API code, to the button.
    
10. With your command button (Refresh Query) still highlighted,  click on the View Code Button from the Control Toolbox.
     
11. Type the following syntax (Blue text) between the provided code (red text):

    Private Sub RefreshQuery_Click()
    result = Application.Run("ScRefreshQuery", 1)
    End Sub

The #1 represents the number of the added query. If you had 2 queries that were linked into excel, you could specify which one you wanted refreshed by simply declaring 1 or 2 within the ("ScRefreshQuery", * ) API.

To return to the excel spreadsheet, click on the drafting triangle icon in the top left corner of the Control Toolbox.  You should now be able to Click on the Refresh Query button.  If you have any prompts within the query you are refreshing, they will appear within Excel.

How to change which query gets refreshed without changing the code every time

Typically a user will have multiple queries added within Excel and will want to choose which query they want refreshed. Rather than having a command button for each query, we will create a prompt that allows the user to choose which query they would like refreshed. This can be done with a little alteration of the code. Rather than simply using this code:

result=Application.Run(ScRefreshQuery",1) we will use the following:
Private Sub RefreshQuery_Click()
  Dim Var%
  Var = InputBox("Enter Numeric value 1-15")
  result = Application.Run("ScRefreshQuery", Var)
End Sub

Alter the syntax like so:

1. The first line "Dim Var%" declares a variable 'Var' as a numeric value '%'
    
2. The second line calls an Input Box that allows the user to put in a numeric value that represents the query they would like refreshed. (When a user adds a query to excel, the first one added would be query #1, the second query added would be #2,and so on.)
    
3. The third line of code calls the API "ScRefreshQuery" and also uses the variable "Var."

To return to the excel spreadsheet:

1. Click on the drafting triangle icon in the top left corner of the Control Toolbox. 
    
2. You should now be able to Click on the Refresh Query button. 
    
3. Once you click on the button, an input box should appear requesting a number to be entered.
    
4. Enter the number of the query you want refreshed and click OK.
    
5. You should see the screen refreshing. See API example, ScRefreshQuery, for different syntax example.

API syntax examples

While syntax is made available to users, most of the functions are made available through our ShowCase Addin for Excel.

The syntax for these API's, was tested against a Windows 95 PC using Excel 97.

ScRefreshQuery

Text Description: Allows user to refresh a specific query within Excel. You will need to create a command button with visual basic.

result = Application.Run("ScRefreshQuery", 1)

 Values:
1-15  This number indicates the order in which the queries were referenced in Excel.

ScOpenRefreshOption

Text Description: This function returns the option specified for how query data should be refreshed when a workbook to which queries have been added, is opened.

result = Application.Run("ScOpenRefreshOption",1)

Value:
0  Ask whether to refresh queries
1  Always refresh queries without prompting
2  Never refresh queries and don't prompt

ScSetOpenRefreshOption

Text Description: When user opens an Excel spreadsheet with a query attached, they will get prompted to Refresh or Keep data. This API helps the user designate a default for this prompt.

result = Application.Run("ScSetOpenRefreshOption",1)

Values:
0  Ask whether to refresh queries.
1  Always refresh queries without asking.
2  Never refresh queries and do not ask Returns True if option can be set. False if option cannot be set.

ScClearGlobalVariables

Text Description: This API allows user to clear the values held within the Global Variable for Query.

result = Application.Run("ScClearGlobalVariables()")

ScSetQueryVariable

Text Description: This API allows user to set a value held within the Global Variable for Query Application.Run("ScSetQueryVariable", "", "Customer", "CO,CA,WA") Values:   Customer: Name of the global variable defined in Query

CO, CA, WA: Values to be set in global variable

RefreshData

Text Description:  Equivalent to choosing the Refresh All Queries command from the Microsoft Excel ShowCase menu. This function runs all the queries added to the active workbook.

result = Application.Run("RefreshData")

ScAddQuery

Text Description: Equivalent to using the Queries dialog box to add a query to the active workbook.

 The following example will allow you to Add Queries through a "Hot Button" rather than using the given functionality through the menu. This requires knowledge of how to create a command button with Visual basic.

Private Sub CommandButton1_Click()
   Path = InputBox("Enter the path for query to add.")
   result = Application.Run("ScAddQuery", Path)
End Sub

Key Notes:

• When the user is prompted for the path of the query, they must put everything in Capital Letters. (i.e. C:\WINDOWS\DESKTOP\QUERY1.DBQ )
• The first line of code "Path = InputBox("Enter the path for query to add.")" Creates a variable called "Path". This variable gets set to whatever the user enters in the input box.
• The second line of code "result = Application.Run("ScAddQuery", Path)" is the API code. (notice the word "Path". This is the predefined variable in line 1)

ScQueryAt

Text Description: This function returns the name, extension and path of the query at the specified index. This requires knowledge of how to create a command button and a label, for the path to be displayed, in Visual basic.

Private Sub CommandButton2_Click()    
   Sequence = InputBox("Enter the query sequence number.")
   Label1.Caption = Application.Run("ScQueryAt", Sequence)
End Sub

Key notes:

• When the user is prompted for the "Sequence number" it is requiring a number 1-15
• The first line of code "Sequence = InputBox("Enter the query sequence number.")" creates a variable called "Sequence." This variable gets set to whatever the user enters in the input box.
• The second line of code "result = Application.Run("ScAddQuery", Path)" is the API code. Notice the word "Sequence." This is the predefined variable in line 1.

ScQueryCount

 Text Description: This function indicates the number of queries added to the active workbook.

 This requires knowledge of how to create a command button and a label, for the value to be displayed, in Visual Basic.

Private Sub CommandButton3_Click()    Label2.Caption =Application.Run("ScQueryCount")
End Sub

ScAddUserEvent

 Text Description: Equivalent to using the User Event dialog box to add a user event to the active workbook.

Private Sub CommandButton1_Click()
  result = Application.Run("ScAddUserEvent", "AddEvent.xls!Sheet1.CommandButton2", _ 1, "C:\WINDOWS\DESKTOP\QUERY1.DBQ") 
End Sub