ActiveData For Excel - 5.0.504 Update
What's New In 5.0.504
ActiveData For Excel® Version 5.0.504 contains the following new features: Support for Windows 8 and Excel®2013. The Import -> Import ODBC Tables command now supports the use of a Where command to specify which rows to import.
The Cells -> Convert Selected Cells drop-down menu has been replaced with a floating dialog with a preview function.
The type of conversion is selected from a drop-down menu.
When you click the preview button, you're asked for any conversion parameters that may be required. Note: The date separator parameter for converting text cells into date cells is new in this version. Previously, it assumed a slash (/).
The converted values are displayed in the preview grid. If you're happy with the results, click the Convert button to convert the cells in your worksheet.
The worksheet cells are converted and the convert cells dialog is updated to reflect the new data in the current selection. The Convert Cells dialog floats on top of Excel. When you select different parts of your worksheet, the Convert Cells dialog is updated to reflect the new selection.
The new Worksheet drop-down menu has been added to the ribbon and the Duplicate Sheet, Cells -> Format Sheet and Sort To Sheet commands have been moved to this menu from their original positions.
A new Combine Sheets command has been added to the Worksheet menu. This command replaces the Workbook Navigator's Combine command button.
Most of the original items in the Rows drop-down menu have been moved to the new Tags drop-down menu and a number of new commands have been added.
The new commands are: Remove Blank Rows, Duplicate Row, Insert N Rows and Row View. The Duplicate Row command duplicates the current row in Excel. The Insert N Rows command allows you to insert 1 or more blank rows before the current row in Excel.
Row View is used to display rows containing more columns than can be shown on the screen at once. Each row is displayed vertically so you can see all of the columns in a single view.
The Columns drop-down menu has had a number of new commands added to it.
The new commands are "Remove Blank Columns", "Duplicate Column" and "Insert N Columns".
The "Duplicate Column" command duplicates the current column in Excel. It's useful for making a quick backup copy of a column before you mangle it with one of the cell conversion commands. The "Insert N Columns" command allows you to insert 1 or more blank columns before the current column in Excel.
Added the new Insert Row # command to the "Columns -> Arrange Columns" dialog. Some of the new Columns menu commands have been added to the Arrange Columns dialog.
The "Columns -> Column Properties" command has been renamed and moved to Cells -> Selected Cell Properties.
The Selected Cell Properties dialog shows the underlying values of the selected cells including the type (text, number or date), the cell's format and the cell's formula if there is one.
This dialog floats on top of Excel and is automatically updated when you change the selection in Excel.
The new Columns -> Column Properties command scans all of the columns in a sheet and then highlights potential problems such as missing column names in row 1, blank columns and columns that contain a mix of number, text and / or dates.
Here's an example using a worksheet that has a number of issues.
The command scans all of the columns in the sheet and produces the following dialog:
In the example above for column J (mixed data types - 1 number cell and 232 text cells), you can double-click the digit 1 on the numeric cells count row to see the first numeric cell. You can enter corrected values in the area at the bottom if it's only a few cells that need adjusting.
The Workbook Navigator has been streamlined and now uses drop-down menus instead of buttons to provide a less cluttered user interface.
The "Rows -> Tag Rows" menu has been moved to its own top level menu on the ribbon.
Row tagging now supports user defined tags in addition to a simple + sign.
Added the "Combine Tagged Rows" command to combine multiple rows based on tag type. This lets you take sheets formatted as reports (eg. sheets exported by Quickbooks) and turn them back into raw data rows by combing information from headers, totals and footers with each line item.
Here we're adding the customer name from the rows tagged as Header, to the right-hand side of the rows tagged as Detail.
And here we're adding the total amounts from the rows tagged as Total, to the right-hand side of the rows tagged as Detail.
The header and total rows are added to the right-hand side of the detail rows. The rows tagged as Detail can now be copied to a new sheet for detailed analysis using Tags -> Copy Tags.
The Tag Rows By Column Value command now uses regular expression syntax if you place you search term in between slashes: /^\w\w\-\d\d\d\d/
The ^ means that the cell must start with the pattern.
The \w means any word character.
The \- means a dash. The reason the \ is placed before the dash is that the – is used in certain regular expression matching functions and is considered a special character.
The \d means any digit.
So /^\w\w\-\d\d\d\d/ means match cells that begin (^) with 2 letters (\w\w) followed by a dash (\-) followed by 4 digits (\d\d\d\d)
Regular expressions can therefore be used to test for out of mask values.
Regular expressions can get very complicated but they're powerful. For example, you could re-write the above to look for any cell starting with 2 or more letters, followed by a dash, followed by 1 or more digits:
/^\w{2,}\-\d{1,}/
Some useful background links for regular expressions:
Introduction to Regular Expressions:https://msdn.microsoft.com/en-us/library/6wzad2b2(v=VS.85).aspx Regular Expression Tester:
https://www.regular-expressions.info/vbscriptexample.html VBScript Regular Expressions Cheat Sheets:
https://www.mikesdotnetting.com/Article/44/VBScript-Regular-Expressions-Cheat-Sheet
https://www.kingsley-hughes.com/tech/script/vbscript/regular_expressions_vbscript.php
