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.
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.
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:
To return to the excel spreadsheet:
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:
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:
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
Still have questions? We can help. Submit a case to Technical Support.