Excel Add-in Changelog 20210809 * Added session memory allocation to display in Query Status section of q-sheet. (CORE-5561) * Added a q-sheet field that can be enabled to clear cache before running the query. (CORE-5522) 20210614 * Reverted cross-tab header feature added in 20201216. Will add back as an optional feature in a later version. (CORE-5605) * Added support for new authentication tag (CORE-5477). 20210423 * Allowed for case insensitive default authentication tag (CORE-5515). 20210330 * Bug fix. Changed default authentication tag to "Default". 20210318 * Added support for new authentication tag (CORE-5362). 20210308 * Fixed precision issue in data downloaded from datetime columns (CORE-5314). 20210225 * Added limited SAML/SSO login support with the "auth" parameter to Login1010 and the "Authentication" TextBox in the Login Window. Contact your administrator for usage information. (CORE-5362) 20210126 * Bug fix for the cross-tabulation column header feature added in previous version (20201216). The bug applied to input queries that only contain once they are expanded on the server-side (e.g. through ). Note that the header feature is available only when the ops are in the unexpanded input query. (CORE-5344) 20201216 * Clarified column headers that result from a q-sheet run with cross-tabulation. Each header now includes the fun(source) that was used to compute the column. (CORE-5060) For example, after running the query: The output headers would be: +------------------------------------------------------------------+ |State|Cnt|Latitude|Anemometer |Anemometer |Anemometer |Anemometer | | | | |Height = 6,|Height = 6,|Height = 6,|Height = 6,| | | | |Time |Time |Time |Time | | | | |Zone = -7, |Zone = -7, |Zone = -7, |Zone = -7, | | | | |cnt(id) |sum(lat) |cnt(id) |sum(lat) | +------------------------------------------------------------------+ 20200728 * "cgi-bin/" is no longer included in the gateway URL built from the user-specified Environment and Version. this resolves an issue reaching cloud environments (CORE-4746). 20200706 * updated digital signature certificate 20200611 * important security update! * updated instructions in the Uninstall menu item to reflect the change to a Windows Installer installation package. 20200424 * installation package 3.00.0001 - Updated SecureCustomProperties so that properties set in the client persist when they are evaluated in the server. - Changed error_excel_version action to execute only once. 20200305 * Updated VBA SDK link path for new MSI installation. 20190530 * Deployed with latest underlying 1010 SDK library. 20181107 * Fixed issue preventing log in with password containing valid non-alphanumeric characters. 20171012 * Updated Login1010 function to have additional optional arguments to allow override of the Environment and Version settings from the Options menu, as well as an optional argument to specify a ProxyPassword. 20170927 * Updated Login Panel to include Environment and Version fields (previously only in Options menu) * The Environment, Version, Username fields of the Login menu retain a history of recent successful connections that can be selected from the dropdown box on these fields. * Added an option to Enable/Disable Shortcut Keys. 20170722 * Updated Options form to recognize new "prime" version names. * Now checks for updated versions on the new website where client side tools are hosted. * Fixed an issue where long queries could be unceremoniously truncated. * Added a digital signature. 20161014 * Added fields to Upload Sheet - Owner: to specify an alternate id to be the uploaded table's owner (can change/delete it). - Final Cell: to specify a cell in the Table Data to set the boundary of the uploaded data. By default, the upload range is determined by the first empty column and row. Final cell can be used to upload NULL rows. 20160929 * Changed how URL redirects are handled internally to reduce network traffic. * Updated certificate authority bundle used by libcurl. 20160919 * Removed option to "Show Advanced Options" in the Add-in menu. Now, all functionality is visible in the menu. * Changed "Forgot Password?" link in Login Prompt to direct you to the reset page specific to the Gateway Server you are attempting to connect to. * Updated upload U-sheet process to load text data in U-sheets in UTF-8 character encoding. 20160620 * Updated the links for the User Guide in the Help menu. 20160609 * Split proxy password from proxy url in options, hiding the password. 20160111 * Updated the Add-in installer to work in Excel 2016. 20151103 * Fixed issue where the "Macro Code" element of the Q-sheet would not be found in certain legacy Excel reports that have improperly modified Q-sheets. 20150818 * Added optional argument to RunQSheets called WhichSheets which can take a Sheets object as a value. When supplied, RunQSheets will run only the enabled Sheets in that object. The default behavior is to run all enabled Sheets in the Active Workbook. * Added Public function LoadUSheets that works similar to RunQSheets, but for uploading data from U-sheets. Also, added advanced menu options under "Upload Data" for uploading all U-sheets in the Active Workbook and in all Open Workbooks. * Added Public function UpdateObject that will cause the current session to update its view of a 1010data path (table|directory) to see any changes since the session began. * Added Public function GetAddinSessionHandle which can get a session identifier that can be used in calls to VBA SDK functions (that will operate in the current active Add-in session). * Fixed issue where "Insert Copied Cells" into the "Macro Code" section of a Q-sheet could move the named range that determines which cells to read the query ops from within. 20150515 * Fixed issue where long running queries might cause an overflow error. 20150507 * Fixed issue identifying Q-Sheets with without _1010q in their sheet names. 20150427 * Updated the installer to work for both 32 and 64-bit Excel, as well as install into both Excel 2010 and 2013 if both are present on the user's machine. * Added handling for Formatted download for column format type currency for units: USD, EUR, GBP, JPY, AUD, CAD, CHF. * Added options to Column Headers in the Q-Sheet Form: "Both - Labels, Names" and "Both - Names, Labels" * Added check on date conversion for dates prior to March 1, 1900 because Excel counts an extra day for non-date Feb. 29, 1900. * Fixed bug where ranges beginning with ' in the "Results Destination" or "Clear Range..." fields would lose the ' after running the Q-sheet. 20150115 * Fixed proxy server connection bug introduced in 20141105. 20141211 * Update to the Add-in installer to run silently from the command line with the /S option. 20141208 * Improved some error messaging. Changed the data download procedure to modulate the fetch window size according to number of columns in the result set to avoid data requests that are too large. 20141113 * Fixed backwards compatability issue where some old workbooks would fail to update Q/U-sheets to the new style introduced in release 20141105. 20141105 * Settings are stored to an external file and will persist when updating the Add-in to a newer version version. Also, added a button to Restore Defaults to the Options menu. * Added more descriptive error messaging when there are error values in the range of cells when uploading a table. * Added conversion of certain formatted Excel data in the upload cells to the corresponding 1010data representations for the data. Specifically, date, month, time, and date+time data are fed through the new publicly available add-in functions FmtAsDate1010, FmtAsMonth1010, FmtAsTime1010, and FmtAsDateTime1010 to perform the translation. User still needs to enter the appropriate info in the Column Types and Column Formats sections of the U-sheet. * New Q/U-sheets do not need to keep the _1010u or _1010q in their sheet names. Older workbooks' Q/U-sheets will be upgraded to the new style when you execute the sheet. * The Debug Logging file selection was changed so that you enter a FILENAME.1010log for basic logging and an additional FILENAME.1010xml file is created when you select "Enable XML Logging". Also, the logging settings are recorded with the other options so now they will persist between uses of the Add-in. * Q-Sheets with no ops in the "1010data Macro Code" section will now run an empty query on the specified table instead of prompting the user with an error. 20140731 * Added uninstall.exe to the Add-in package. The Uninstall Instructions Menu now directs you to run that file. 20140707 * Added checkbox option to allow user to download Column Labels with newlines replaced with whitespace. 20140610 * Changed the order of the optional arguments on RunQSheets so that code built to call into the Add-in 1.0 version of this function will also work using the 2.0 version of RunQSheets without modification. 20140602 * Changed the installers to require only User permission level instead of Admin level. 20140502 * Fixed indexing bug in ListDir function when listing only tabs or only dirs. 20140418 * Update from OpenSSL 1.0.1e to 1.0.1g. 20140415 * Running Queries in All Open Workbooks no longer prompts the user with a Message Box at the completion of execution of each Workbook. Now, there is only one Message at the end of all execution summarizing the results of the queries. 20140404 *****Excel Addin 2.0 Release******** * Refactor to use the 1010data VBASDK to make requests of 1010data. * New Features: * Downloads are as compressed binary data, downloads observed to be 20x faster * Manually repost queries * About... menu let's users know if a new version of the Excel addin is available for download * Enhanced Error Logging - Users may now create both an Addin and XML Log * Enhanced character set support * True Progress Bar - * The Query Progress dialog box shows the percentage of processing complete for queries running in the current workbook * The progress bar reports total workspace * Updated Documentation - * New Documentation available at: https://www2.1010data.com/prod-latest/DocumentationCenter/ExcelAddin/index_frames.html * Excel Addin 2.0 is compatible with Excel 2010 and 2013. * Excel 2007 users must still use the Addin 1.0, available at: http://1010data.com/downloads/1010dataExcelAddinSetup.exe * Templates created with the Addin 1.0 are expected to be fully compatible with the Addin 2.0, save the following known exceptions: * Autofilter Handling - * If the destination worksheet contains an autofilter, the addin will attempt to paste in new data and reapply the filters. * If an autofilter is detected that cannot be reapplied, the addin will report an error and stop processing. * In this case, users may opt to have the addin automatically remove autofilters. * This behaviour is specified in Auto Filter Options in the 1010data Options Menu. * Autofilters known to be unsaveable: "Top N", "Above Average", "Below Average" * Date Formatting - * Formerly, 8-digit integers without any explicit formatting would be autoformatted by 1010data as dates. * Date formatting can be added to column XXX with: * Column Header Word Wrapping * Formerly, the end-of-line Character for column labels,`, would download as a space " ". * Now, the end-of-line is preserved in the excel download. * For example "Hello`World" would download as "Hello World" in Addin 1.0 And in Addin 2.0 "Hello`World" will wrap across 2 lines: Hello World * Excel Pivot table column headers that are sourced from 1010data column labels must be recreated. Excel formulas must be updated to recognize the character change. * Decimal Precision * Formerly, if Data Format is set to "formatted" or "unformatted", the data would be downloaded with the decimal display precision specified in the query (as formatted XML) * Now, decimal values are always downloaded with their intrinsic precision. If "formatted", the displayed decimal precision in Excel will match the displayed decimal precision in 1010data. * For example, would have downloaded as 20.12 in Addin 1.0 and 20.123445 in Addin 2.0. * Proxy Credentials - * Users may specify proxy credentials of form: http://[USER[:PASSWORD]@]HOST[:PORT] * Set in the Options menu * Import a Browser Session - * Users may import a browser session by pasting their full 1010data URL into the form, "Import Browser Session" * This feature is for users that normally log into 1010data without a 1010data UID * The full 1010data URL is only available in special circumnstances. * Most users should share a browser session by choosing "Enter existing session" when logging in to 1010data * VBA SDK - * Users may create their own functions that use the 1010data VBASDK. * VBA SDK functions are deployed in the a1010data.xlam module, VBASDK1010. * VBA SDK web documentation available at: http://www.1010data.com/downloads/vbasdk/doc/index.html * VBA SDK changelog available at: http://www.1010data.com/downloads/vbasdk/changelog.txt * This release (and all following releases unless stated otherwise) is being developed on Windows 7. We fully expect it to work on other versions of windows. 20121001 * Give users option to disable "Save your 1010data session" prompt upon close of Excel * Changed Login Form to have "Enter Existing Session" * Updated to Red 1010data color scheme * Added new option possess=True/False to the Login1010 function. Now can possess a gui session began in any version. 20120801 * Prompt users to "Please check that your username and password are correct." when login is unsuccessful. 20120424 * Fixed bug where importing session from non prod version was giving irrelevant error message 20120419 * Changes to options form so user can now either login with default which will respect the user pref, or specify a specific version * changed "server" caption to "gateway server" * changed "server environment" caption to "version" * default version is now "Default" rather than "Production" and removed "Beta" option 20120307 * No longer logging login and errors to server side logging facility 20111108 * Now properly reporting XML error due to non utf-8 encoded data * Add-in is no longer password protected * Added note to upload sheet that data can't exceed 1mm cells * Removed VerifyVersion and related userForm and global var, was no longer being used * Removed "test" from version dropdown in options panel * Removed dependency on "data" range in u-sheet - prevents error when user deletes top row in upload data 20111107 * File is now .xlam rather than .xla - evolving forward... now should not be subject to worksheet size limitations of Excel 2003 * Now supports new versioning scheme - you can enter custom "environment/version" 20101026 * Raised priority of "Microsoft XML, v6.0" over "Microsoft Forms 2.0 Object Library" in References, this seemed to be causing issues with xml parsing for some users 20100913 * Now uses MSXML2.DOMDocument60 instead of DOMDocument50, by default Windows 7 doesn't have msxml 5, and anyone can download 6 from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=993C0BCF-3BCF-4009-BE21-27E85E1857B1&displaylang=en * Removed calls to VerifyVersion, not using this anyway 20100727 * Made server (in options form) allow manual input (for nyx etc) 20100625 * Reintroduced help on menu - now shows folder instead of opening PDF * Status cell on upload sheet now includes timestamp of upload (or failure) * Added optional argument to listdir to exclude header row 20100527 * Fixed bug in Main.ExecQuery - cross tabs were failing in beta due to change in xml structure of API response (new cbreaks tag), now handles new xml properly 20100422 * Fixed bug in UploadModule.GetTableTemplate - tables with no formats were dropping the headers 20091204 * Fixed bug causing overflow error with queries returning 0 rows 20091202 * Fixed bug causing overflow error (int should have been long) with large downloads 20091201 * Fixed bug with uploading cells containing functions such as format(..., "yyyymmdd") * Changed upload's "date guessing" to only treat as date if col is type i * Fixed bug in v20091130 causing successive chunks to be pasted on top of first chunk, overwriting data and headers 20091130 * Backed out exploratory changes to progress bar that were causing problems * Removed link to pdf from menu (causing crash in Vista/Excel2007) 20091109 * Added "local processing time" to q-sheet status cell update * Added check to ensure there are enough columns available at destination for query results * Turned off logging on getUserSetting/setUserSetting * Made upload submenu * Added looping to getdata in chunks to avoid Excel choking on big downloads * Now trapping error resulting from queries returning no cols * Application error nows shows details 20091105 * Bugfixes to upload, restrict upload to 1mm cells, automatically format dates * Renamed MainPublic (module) to PublicModule, changed around some scoping * Call1010 is now public * Removed line that deletes menu before close - need to make sure that is ok * Changed guid, gsid, gpswd to type string from variant * Rearranged login window tabstop * IsSessionCurrent actually validates the session with the server * Added public function GetSession - returns array with uid/sid/pswd 20091103 * Added Upload 20091021 * Help link to pdf from menu * Uninstall instructions on menu * Removed validation on "clear before pasting" to disregard whether or not destination range is in the clear before paste range - was causing trouble and not really necessary. Also relocated the clear to happen before the query runs, not after it succeeds and is about to paste. 20091015 * Added "server environment" option (prod/beta/test) * Always use SSL (https) * Minor change on positioning of 1010data menu - may fix issue observed where menus of other add-ins were disappearing * Added option to enable error logging and dump error log menu option * Option to add subtotals after results are pasted * Option to clear destination area before pasting * Option to import a session established in the GUI 20090922 * Added "About" menu 20090914 * Set Application.Calculation = xlCalculationAutomatic on login and ensureLoggedIn 20090316 * Added ListDir(), RunQSheet() * Changes to behavior of RunQSheets() and RunActiveQSheets() when quiet argument is true - progress bar does not show, errors are not reported to user * Changed generic object usage to use MSXML2.DOMDocument50 (should be no difference in behavior) 20081217 * Now using gw.k instead of gwbeta.k * Added public function Dump1010Log() - writes debug log to immediate window 20080922 * Wrote public function ImportSession * changed session timeout assumption from 30 min to 45 min 20080401 * Fixed issue with ClearCache - only attempts to clear cache if logged in * Introduced check in call1010 - raises error if global session vars aren't populated * Now assigning value to gServer in function IsSessionCurrent - seems to have been causing errors 20080218 * ==== All API calls now use gwbeta.k instead of gwtest.k ==== * VerifyVersion now uses VersionCtrlBeta.xml instead of VersionCtrl.xml * Minor tweak to VerifyVersion to deal with 404 and similar http errors * Fixed LabelLink_Click in NewVersionMsg UserForm to invoke proper global var for download link. * Modified LoginWindow UserForm - upon submitting, input fields are disabled 20080131 * New public function ClearCache with menu option * logout1010 now submits blank body so actual logout happens (was failing beforehand due to presumable api bug requiring blank input) * logout1010 now has an optional quiet argument * Removed wrapping of results from server in root tags due to recent API bug fix 20080107 * Fixed bug introduced with recent modification - was treating 1st column in getdata results as alpha field regardless of type 20080103 * Links to EULA and disclaimer from login page * No kill option on logging in 20080102 * Adds style attribute to HTML pasted when column format is alpha so Excel will treat as text and not lose leading zeros 20071227 * Added option for format=raw * Fixed bug (introduced with change to DestRange validation) that failed to disallow empty DestRange causing application error * Minor tweak to logic in IsValidQSheet * Added logging when entering/exiting functions * Ensured that functions returning gSucess/gFailure have proper default return value 20071221 * Minor tweak to the timing text that appears in status cell on qsheet Prior version: 20071213 Recent changes (since 20070815): 20071213 * PostToServer now sets the User-Agent of the http request * Added support to process "Columns" input field used in old Q-Sheets - templates should now be upward compatible * Fixed bug caused by merged cell provided as input for DestRange 20071204 * enhanced logging from login1010() so we have more info if login results in fatal error 20071023 * added 0.1 second pause before paste in ExecQuery to work around error generated in Parallels/Mac environment 20071022 * now updates status field to indicate number of rows resulting from query, not only number of rows retrieved 20071011 * commented out this line: gServer = GetUserSetting("Server") in Sub WriteToLog() to prevent infinite loop when that function call itself causes an error