Differences

This shows you the differences between two versions of the page.

--- excel [2018/07/25 14:48] – [IP Data Query UI] Updated to match new UI features su
+++ excel [2018/07/31 11:39] – [IP Data Query UI] minor changes su
@@ Line 13: / Line 13: @@
   - Download the Excel Query Add-In from the [[https://appstore.intelligentplant.com/|App Store]] (scroll down to the "Spreadsheets" section in the home area)
   - Run the installer
-  - The Add-In will launch automatically (skip step 1 in "Running the Add-In")
+  - The Excel will launch automatically (skip step 1 in "Running the Add-In")
 === Running the Add-In ===
-  - Double click the "Excel Query Add-In" icon on your desktop to launch the Add-In
+  - Open Excel or double click the "Excel Query Add-In" icon on your desktop to launch the Add-In
   - You may be asked if you trust the Add-In, select either "Always Trust this Publisher" or "Allow for this Session"
   - An App Store login window will open in Excel, sign in with your App Store account
@@ Line 26: / Line 26: @@
 //You can download a spreadsheet containing examples of all the formulas [[https://appstore.intelligentplant.com/nuget/downloads/IP Excel Addin example.xlsx|here]].//
-//Automatic Calculation must be on in order for the add-in to work properly//
+=== Updating ===
-==== IP Data Query UI ====
+To update the Add-In double click the "Excel Query Add-In" icon on your desktop. If a new version is available you will be prompted to download it.
-[[https://www.youtube.com/watch?v=_Q_361HDaOE&list=PLsVwx5rtV9c0duV5jln2IX9BIMDCCEWrY&index=2|This video]] shows how to use the Data Query UI.
+=== Uninstalling ===
+  - Open "Add or Remove Programs" in settings or "Uninstall a program" in "Programs and Features" in control panel.
+  - Select "Excel Data Query Add-In"
+  - Click "Uninstall"
+  - Unsure the "Remove the application from this computer" option is selected
+  - Click "Ok"
+You may start getting an error when Excel opens that says the add-in can't be found, to fix this either reinstall the add-in or remove the add-in from Excel.
+To remove the add-in from Excel:
+  - Open Excel
+  - Dismiss the warning and open a work book
+  - Go to File -> Options -> Add-Ins
+  - Select "Excel Add-Ins" in the selection box at the bottom of the dialogue
+  - Click "Go"
+  - Deselect "Gestaltdataexcel-Addin-Packed" in the list
+  - Click "Yes" when prompted if you want to remove the add-in from the list
+==== The IP Data Query Tab ====
+[[https://www.youtube.com/watch?v=_Q_361HDaOE&list=PLsVwx5rtV9c0duV5jln2IX9BIMDCCEWrY&index=2|This video]] shows how to use the "IP Data Query" tab.
 === Get Current Value ===
@@ Line 44: / Line 67: @@
 If the data source you want to query isn't listed it may be because it's not authorized, it doesn't support tag read operations or you aren't authorized to read from the data source.
-== Advanced ==
-All text boxes can will be evaluated as a formula if they begin with "="
-You can use the "Custom Tag" box to add tags that aren't listed, this is primarily useful if you want to query Alarm Analysis Meta tags.
-Deselect "Enter As Formula" if you would like the result of the query to be added directly to the sheet. Leaving it selected generates a formula as described in the "Using Data Query Formulas" section
 === Get Historical Values ===
@@ Line 67: / Line 82: @@
 If the data source you want to query isn't listed it may be because it's not authorized, it doesn't support tag read operations or you aren't authorized to read from the data source.
-== Advanced ==
-All text boxes can will be evaluated as a formula if they begin with "="
-You can use the "Custom Tag" box to add tags that aren't listed, this is primarily useful if you want to query Alarm Analysis Meta tags.
-Deselect "Enter As Formula" if you would like the result of the query to be added directly to the sheet. Leaving it selected generates a formula as described in the "Using Data Query Formulas" section
-=== Refresh Data ===
-This button will reload all data query formulas on the current worksheet.
 === Write Data ===
@@ Line 92: / Line 95: @@
 If you don't see the data source you want to write to listed it may be because it's not authorized, it doesn't support write operations or you don't have permission to write to that data source.
-== Advanced ==
+=== Advanced ===
 All text boxes can will be evaluated as a formula if they begin with "="
 You can use the "Custom Tag" box to add tags that aren't listed, this is primarily useful if you want to query Alarm Analysis Meta tags.
+Deselect "Enter As Formula" if you would like the result of the query to be added directly to the sheet. Leaving it selected generates a formula as described in the "Using Data Query Formulas" section
+=== Refresh Data ===
+This button will reload all data query formulas in the currently opened sheet.
+=== Refresh Selection ===
+This button will reload all data query formulas in the currently selected range.
+=== Wiki ===
+This button will open this wiki page.
 ==== Using Data Query Formulas ====
@@ Line 108: / Line 125: @@
 Once you have written (or edited) a formula press Ctrl + Shift + Enter to update the effected range, if you forget to do this you will get a "You can't change part of an array" error from Excel.
-If a cell contains "#GETTING_DATA" that means that the query has been sent but hasn't been returned yet. "#N/A" means that there is no result for that cell (i.e. the range you selected is larger than the number of returned data points).
+If a cell contains "#VALUE" that means that the query has been sent but hasn't been returned yet. "#N/A" means that there is no result for that cell (i.e. the range you selected is larger than the number of returned data points).
 === Get Available Data Sources ===
@@ Line 130: / Line 147: @@
 You can get the data sources available to the Add-In by using the "GetTags(...)" formula.
-  * Select a column (e.g. A)
+  * Select 3 a columns (e.g. A:C)
   * Enter "=GetTags(<data source name>, <tag query>)" in the formula bar
@@ Line 140: / Line 157: @@
-This will return a list of tags on the specified data source that match the query.
+This will return a list of tag names, descriptions and units on the specified data source that match the query.
 **Examples**
@@ Line 213: / Line 230: @@
 Would get the historical value of "Example Tag" for the past 10 days, without timestamps or a header row.
+==== Tips and Tricks ====
+=== Defined Names ===
+You can define a name to make it easier to use the formulas.
+  - Copy the fully qualified name of the data source (see the "Get Available Data Sources" section for details) e.g. "My Data Source"
+  - Open the "Formulas" tab
+  - Click "Define Name" in the "Defined Names" section
+  - Enter a meaning full name in the name feel e.g. "DataSource"
+  - Enter the fully qualified name of the data source into the "Referes to:" field like this ="<data source name>" e.g. ="My Data Source"
+You can now use the name you set in place of the data source name in formulas, e.g. =GetTags("My Data Source", "*") becomes =GetTags(DataSource, "*"). This makes it easier to change the data source and saves you typing out or copying long data source names, like those used by App Store connects.
+You can also use this for any other parameters in the UI or in fomrulas.
+=== Formula Wizard ===
+You can more easily enter formulas by using Excel's built in formula wizard.
+  - Open the "Formulas" tab
+  - Click "Insert Function"
+  - Select "GestaltDataExcel Add-In" from the category drop down, or search for the relevant formula
+  - Select a formula from the list
+  - Fill in the prompted function arguments