![]() | ||
ASAP Utilities version historyThe extensive features are the result of years of experience and development, combined with the feedback from many respected users at different levels of experience. Particularly the feedback from users has been very important, and will continue to be important in further development.Constant improvementSince its first release in 1999, ASAP Utilities has been improved and extended with many new features and a friendly interface. It is under constant development, so new features and improvements are being added all the time. The first year we put the program on the internet, nearly every month a new version was released. Nowadays a new version is released at least twice a year.The input we get from users all over the world is a great stimulation for us to make it a better product every time. Old versionsRegistered users who have purchased a license key can keep using and download older versions of ASAP Utilities.Version 4.2.4 (11 MARCH 2008)New features:- Export » Export selection or active sheet as new file...The default file extension for tab separated files is now tsv instead of txt (tsv stands for tab separated value) You can now choose whether you want to have the utility automatically closed after the export is created. - Export » Export worksheets as separate files... The default file extension for tab separated files is now tsv instead of txt (tsv stands for tab separated value) - Formula » Convert formulas to their values Improved so that it now works on filtered data too. Normally the utility changes the formulas to their calculated values in all selected cells (hidden or not). If you have selected cells in a filtered list only the visible cells are affected. - Web » Activate hyperlinks (create from cell values)... Improved, you can now choose to use the "regular" hyperlink or to use the =HYPERLINK() formula to create the hyperlinks. The formula- method is preferred for shared workbooks and when it involves a large amount of hyperlinks. - Columns & Rows » Put together rows or columns from several sheets... The column width of the original data is now copied too. - Export » Export selected chart or range as image (file)... You can now choose whether you want to have the utility automatically closed after the export is created. - Fill » Insert before current value... Fill » Insert after current value... You can now choose to skip empty cells in your selection. There is also a 'reset' button added that will clear the insert before and after values and reset the options to their defaults. Because your previous settings are always remembered this can be handy. - Web » Extract hyperlinks... Your last used settings are remembered. Bug fixes:- Format » Copy a sheet's page and print settings...In Excel 2007 this utility could make Excel crash. This is now fixed by creating a workaround for Excel 2007. - Format » Empty all headers and footers In Excel 2007 this utility could make Excel crash. This is now fixed by creating a workaround for Excel 2007. - Export » Export worksheets as separate files... Improved so that it ignores other applications/addins during the process. This solves a problem the add-in XLplus could cause when also installed. A few other minor bugs have been solved too. - Export » Export selection or active sheet as new file... Improved so that it ignores other applications/addins during the process. This solves a problem the add-in XLplus could cause when also installed. - Select » Conditional row and column select, hide or delete... The progress indicator when searching for duplicates wasn't properly shown, this is now fixed (and improved for removing duplicate) - Import » Insert Excel File... Fixed the incorrect message that the file was opened instead of imported. Further improved so that if you just started Excel with a blank workbook, the file is imported in stead of opened. - General If your worksheet contained wrong macro code (for example plain text instead of proper code) you got the error that a worksheet wasn't selected. This is because due to the bad macro code in the sheet certain actions cannot be done. The message is now updated so that it will inform you properly about this and suggest that you reopen your workbook without enabling the macros. Version 4.2.3 (19 FEBRUARY 2008)New features:- New worksheetfunction =ASAPISBOLD()Returns true of the referenced cell is bold - Fill » Add leading zeros... has been renamed to: Fill » Fill up with leading zeros... We think this is a better description of what the utility does; Fill the current value with leading zeros up to the total length you specify. For example if you enter 3, the number 1 becomes 001 and 11 becomes 011. If you enter 6, the number 1 becomes 000001. - Format » The smart difference... has been renamed to Format » Detect and visualize adjacent data/group changes to better reflect what the utility does - General Several minor improvements Bug fixes:- Range » Find / Replace in all sheets...When you pressed the "Find all" button you immediately got Error 401: Application-defined or object-defined error. This is now fixed. (this bug only existed in version 4.2.2.) - Favorites menu Fixed the bug where in some cases you got an error stating that ASAP Utilities can't access the favorites folder. - Import » Insert (delimited) text file... Fixed the bug when imported date values where not displayed as dates (cell formatting) but displayed as numbers instead. - Fill » List filenames in folder... Fixed the bug where in column B the filename was listed including its full path when the option to create hyperlinks to the files was checked. This is now corrected. - General Several minor bugfixes The favorites menu can now be located on a network path, such as \\server2\user\data\ Version 4.2.2 (22 JANUARY 2008)New features:- GeneralIf you install ASAP Utilities you can choose to have it automatically added to the Excel menu for each user that logs in on the computer. This is also handy when you log in with normal user-rights and run the setup as an administrator. - ASAP Utilities Options » ASAP Utilities options and settings... You can now specify to have the menu-items (Categories) numbered too. By default this is turned on. - Export » Export selected chart or range as image (file)... The dialog will now automatically be closed after the export is completed. - Export » Export worksheets as separate files... Improved, you can now specify in which file-format you want to save the sheets. If you have Excel 2000, 2002/XP or 2003 you can export the sheets in these formats: - Excel 97-2003 Workbook (*.xls) - CSV (Comma delimited) (*.csv) - Text (Tab delimited) (*.txt) - Text (Tab delimited unicode) (*.txt) - Text (Space delimited) (*.prn) - Text (Pipe delimited) (*.txt) - Text (Delimited by #) (*.txt) - DBF 4 (dBASE IV) (*.dbf) - DBF 3 (dBASE IV) (*.dbf) In Excel 2007 you can save the worksheets in the following file formats: - Excel 2007 Workbook (*.xlsx) - Excel 97-2003 Workbook (*.xls) - CSV (Comma delimited) (*.csv) - Text (Tab delimited) (*.txt) - Text (Tab delimited unicode) (*.txt) - Text (Space delimited) (*.prn) - Text (Pipe delimited) (*.txt) - Text (Delimited by #) (*.txt) - Import » Insert (delimited) text file... Improved, the import of pipe-delimited files has been implemented and you can now have the imported data automatically trimmed (remove leading and trailing spaces). You can also specify whether you want Excel to auto detect the field/column types (default) or if you want all the columns to be treated as text fields. This can be useful for example when Excel removes all leading zeros. - Fill » List filenames in folder... Added the option to only list files that were created or modified a number of days ago. - Fill » Quick numbering of cells... You can now specify how to increment the numbers. This used to be 1 (1,2,3 etc) but now you can enter your own value, for example 5 to icrement from 5 to 10, 15 etc. - Numbers » Round numbers (not formatting, but changing)... Improved speed - Sheets » Add sheets (with the names defined in selected cells)... You can now also choose to color the new sheet-tabs based on the cell colors. Colored tabs are available since Excel version 2002/XP. - Sheets » Sort all sheets in alphabetical order has been improved and renamed to: Sheets » Sort all sheets... You can specify to sort the sheets alphanumeric (text), numerically (numbers) or by their tab-color. You can also specify to sort the sheets ascending (A-Z) or descending (Z-A). To be able to sort by the tab-colors you need at leest Excel 2002/XP. - Web » Decode all URL-encoded data Decode all URL-encoded data in your cells. This will replace the '+' with a space and decode any %## encoding in the cells, which can be handy when you use Excel to analyze website statistics and keyword performance. For example this utility will decode data like: %22Keyword%2Binfo%2C.%2F~%26ok%3F to: 'Keyword+info,./~&ok? URL Encoding is used when placing text in a query string to avoid it being confused with the URL itself. It is normally used when the browser sends form data to a web server. URL Encoding replaces spaces with '+' signs, and unsafe ASCII characters with '%' followed by their hex equivalent. - General Several minor improvements Bug fixes:- Count duplicatesDidn't work correctly if one of the values in the list started with the less then (<) or greater then (>) sign. For example a list with values 8370N, 8384N, >82821N, >83381N incorrectly tagged the last two values as duplicates. The utility didn't work correctly with large numbers. Numbers such as 162000205922.002500324327 and 162000205922.002500324329 were incorrectly marked as duplicate. The speed of the utility has been significantly improved. - Formula » Change reference style (e.g. A1 to $A,1 etc.)... The option "Convert formulas to absolute rows and relative columns (A$1)" incorrectly changed the cell references to relative rows and absolute columns ($A1) - Export » Export selection or active sheet as new file... When you had a chart on a chartsheet and you had selected something within that chart, for example the chartarea, the export didn't work and produced an error. This is now fixed. A workaround was to not select the chartarea (click on the grey at the left/right to deselect it) and then do the export. - Numbers » Convert "textnumbers" to numbers Fixed a bug that caused some number not being correctly converted to real numbers you could use for calculations. This bug was introduced in version 4.2.0 when we updated it to handle fractions better. - Export » Export selection or active sheet as new file... Fixed the bug where the presented default file name displayed the name of the workbook reversed, such as 1kooB#Sheet1!B5_G26 instead of Book1#Sheet1!B5_G26 - Export » Export selected chart or range as image (file)... When you exported a chart in EMF format the chart dimensions in the exported file could be changed into the initial height and width of the chart. This bug is now fixed and appeared in Excel 2000, XP/2002 and 2003 This bug did not appear in Excel 2007. - Web » Activate hyperlinks (create from cell values) When you used the option not to change the font to the default hyperlink font (blue underlined), the cells were still underlined. In this version the underlinement will not change if you choose to keep the original cell formatting. - Export » Export selection or active sheet as new file... Improved the export in space-delimited format. Fixed the error in Excel 2007 when you choose to export the entire sheet. - Format » Copy a sheet's page and print settings... When you only choose the option "Fit to pages", the fit to x pages wide by y pages tall options were copied but "Fit to" button was not selected. This is because that option is saved in Excel with the zoom-setting. The utility has now been changed so that the zoom option is automatically selected too when you check the "Fit to pages" option. - Fill » List filenames in folder... Has been improved. It produced an error on shared workbooks if you selected the "Create hyperlinks to the files" option together with the option to create a new worksheet. - Web » Activate hyperlinks (create from cell values) Didn't work in shared workbooks. This is now fixed. This utility now uses the Excel =HYPERLINK() formula which is faster and much more flexible when it comes to large amounts of data then the previous technique which uses the right-click cell option to insert hyperlinks. We found out that Excel has an undocumented limit on the number of hyperlinks that are created that way. - Range » Copy formulas without changing their references Fixed the error when you copied the formulas to another worksheet. - General Created a workaround to make ASAP Utilities work better when Excel 2007 is started by downloading a template via the Microsoft Office website. - General Fixed a few small bugs. Version 4.2.1 (14 NOVEMBER 2007)New features:- Fill » Random numbering of cells (without duplicates)Improved speed - ASAP Utilities Options » (Re) build the ASAP Utilities favorites menu toolbar Create or update a toolbar with the buttons/tools from your favorites menu. - Format » Copy a sheet's page and print settings... Slightly improved speed. Furthermore it displays the current printer at the bottom. The speed of changing the page setup in Excel might depend on the printer you currently have active. I will write more about this... - =ASAPRandomPassword(length, use_symbols) This new function will return a strong password which contains of - both uppercase and lowercase letters - numbers - symbols, such as ~!@#$%^&*()[]\/<>:-=+_ lenght: The length of the password. If omitted the length will be 8 characters. If the length given is less then 8, still a password of 8 characters will be returned. use_symbols: Use symbols in the password. Recommended for strong passwords. If omitted the password will contain symbols (special characters) Examples of created passwords: =asaprandompassword() 4j:4Js29 =asaprandompassword(8,FALSE) dvU57E6u =asaprandompassword(8,TRUE) -\D5t0:3 =asaprandompassword(14,FALSE) 55GHo1sM85j9Le =asaprandompassword(14,TRUE) 89eTgLq\_46woE This function can be easy if you need a random password, or for example of you are a system administrator and need to create 50 random passwords for new email addresses. - Format » The smart difference... Contains a new option to apply colorbanding. Based on the information of data in a column you can have alternate colors to visualize the differences of data-groups. - General A few small improvements. Bug fixes:- Sheets » Freeze panes on multiple sheets at onceImproved. The wrong range was freezed when the selected range on the other sheet was not in the "visible area". This is now fixed - System » Save file and create backup Fixed the bug where in Excel 2007 the point was removed before the extention in the name of the backup. When the filename had an extension that was not 3 characters, for example xlsx in stead of xls it goes wrong. For example book1.xlsx became in the backup folder: Book1 (backup 2007-10-01 08.25.31)xlsx; no piont before xlsx. - Excel 2007 ribbon Removed the dynamic getScreentip for the buttons. This caused empty Visual Basic popups when you wanted to customize the ribbon (quick access toolbar, qat) and choose "all commands". The same goes for the getImage tag. If you first select the ASAP Utilities tab and then customize the ribbon it is okay, if you directly customize the ribbon you will still see one empty Visual Basic error. However this error has no effect on how things work. - Excel 2007 Fixed the message that appeared on some computers: "ASAP Utilities ribbon.xlam is now available for editing. Choose Read-Write to open it for editing" - Fixed a few small bugs and typing errors. Version 4.2.0 (24 SEPTEMBER 2007)New features:- GeneralASAP Utilities is now nicely integrated into the new Excel 2007 menu (ribbon) - Select » Conditional select cells... Added the option to select cells with a certain number format - Import » Insert (delimited) text file... Inserts a (delimited) text file at the position of the selected cells. This new import utility has several options you can set, but right now the main purpose is that the dates can be imported using your windows regional settings. - Fill » Insert current date and time with seconds Insert the current date and time into the active cell. Unlike Excel's built in shortcut "Control+:" this utility also inserts the seconds. (timestamp) By default this utility can be started with the shortcut Control+Shift+D - Fill » Insert current time with seconds Insert the current time into the active cell. Unlike Excel's built in shortcut "Control+;" this utility also inserts the seconds. (timestamp) By default this utility can be started with the shortcut Control+Shift+T - Export » Export selected chart or range as image (file)... New option to open the exported image in your default image-viewer immediately after the export Default filenames are now the chartnames or the address of the selected range. - Sheets » Freeze panes on multiple sheets at once Freeze panes on all or all selected sheets at once, based on your current selection. Normally in Excel you can only do this one sheet at a time. With this utility you can now for example freeze the first row on all sheets at once. - Sheets » Unfreeze panes on multiple sheets at once Unfreeze panes on all or all selected sheets at once. Bug fixes:- Format » The paper saver (split columns)...Fixed the error on start. - Fill » List filenames in folder... Error fixed when reading files from cd-rom or DVD (Last Accessed date not available). - Export » Export selection as HTML table to clipboard... Fixed 'Runtime error 91: Object variable or With block variable not set. - Numbers » Convert "textnumbers" to numbers Fixed a bug where fractions such as 1/2 were converted to dates. Fractions will now be detected and converted to fractions you can calculate with. - Export » Export selected chart or range as image (file)... Several minor improvements. Fixed the bug in combination with Excel 2007. The image quality however in Excel 2007, especially in combination with Windows Vista still needs some improvement compared to previous Excel version. I'm working on a solution for this. - Improved worksheet fuctions. ASAPSumByCellColor() ASAPCountByCellColor() ASAPSumByFontColor() ASAPCountByFontColor() If you work with more than 2 decimals the utility isn't 100% accurate after the fifth decimal of the result. This is corrected in this version. These functions can now use a cell-addres to specify the color in stead of only using the colorindex. Furthermore the accuracy of these functions is improved. So for example if you have a range of cells with 10 green cells you want to sum you can use the formula either as =ASAPSumByCellColor(A1:E24;4) or if cell D4 contains the color you can use: =ASAPSumByCellColor(A1:E24;D4) - Improved the progress bar of the utilities - Small improvements in the "Organize your favorite tools..." utility - Several small bugfixes Version 4.1.2 (16 JULY 2007)New features:- General - User GuideIn the main menu the item "User Guide" is added. This will launch the User Guide (pdf). The User Guide might be included in this setup-package. Otherwise you can download the ASAP Utilities User Guide (PDF) on our website. The User Guide will give you detailed information about the installation process and all other things you need to know about how the utilities work and how to get the best out of them. http://www.asap-utilities.com/documentation-user-guide.php - General - Renamed menu-items In the main menu the category "Favorites" has been renamed to "Favorites & shortcuts" In the main menu the category "ASAP" has been renamed to "ASAP Utilities options" In the main menu the category "Columns/Rows" has been renamed to "Columns & Rows" In the main menu the category "Objects/Comments" has been renamed to "Objects & Comments" - General - Optional numbered submenu-items The menu items in the submenus can now be numbered. You can turn this on/off with the setting "use numbered submenu items" that can be found in: ASAP Utilities > ASAP Utilities options > ASAP Utilities options and settings... By default this is turned on. - Range » Transpose data with formulas... Now copies not only the formatting of the cells but also cell comments and cell validation. - Export » Export selection or active sheet as new file... Added the option to export as a tab delimited unicode text file. - Format » The paper saver (split columns)... Added the option to copy the formatting of the cells too. Your last settings will be remembered. - Columns & Rows » Insert empty rows or columns... has been renamed to "Insert in-between empty rows or columns..." - Sheets » Protect all sheets... has been renamed to "Protect multiple sheets at once..." - Sheets » Unprotect all sheets... has been renamed to "Unprotect multiple sheets at once..." - Columns & Rows » Insert multiple empty rows at once... Allows you to easily insert multiple rows at once. This is normally not easily possible in Excel itself. Inserting multiple rows "the Excel way": If you want to insert multiple rows at once the standard procedure is to first select multiple rows and then use the "Insert" command. For example if you want to insert 10 rows between A1 and A2, you first have to select rows 2:11 and then insert the rows. Inserting multiple rows "the ASAP Utilities way": The above solution works for small amount of rows. However if you need to insert for example 800 rows this is not a workable solution. Therefore I have written the utility which lets you choose the amount of rows to insert below the selected cell/row. - Select » Conditional select cells... Improved. You can now use this utility also to select entire columns or rows. Furthermore in addition to the "select like (text)" option I have added the following criteria: . begins with . does not begin with . ends with . does not end with . contains . does not contain - Columns & Rows » Delete all hidden rows and columns Improved progress display. Improved speed (when pagebreaks are turned on) - Columns & Rows » Remove all empty rows Improved speed (when pagebreaks are turned on) - Columns & Rows » Remove all empty columns Improved speed (when pagebreaks are turned on) - Text » Convert dates... Contains a new option to change the case of the new 'text dates'. This way you can for example set the new date to be in uppercase. - ASAP Utilities Options » Start the last used utility again... Start the last used utility from ASAP Utilities. This will re-start the last run utility used in ASAP Utilities. For example if the last utility you used was 'Export selection as HTML table to clipboard...' this tool will start that utility. - Convert hyperlinks to =HYPERLINK() formulas Convert all hyperlinks in cells to =HYPERLINK() formulas in the selected worksheets. The current hyperlink will be use for the hyperlink part. Hyperlink screentips will be lost because they are not supported by the =HYPERLINK() formula. Bug fixes:- Export » Export selection or active sheet as new file...The option "Copy to clipboard (tab delimited)" didn't work. - Select » Select adjacent cells in column with the same value Produced an error if your initial selection started on the first row. - Select » Conditional select cells... In some cases the "Expand current selection" next to the "entire sheet" option was invisible but checked which caused an incorrect message that no results were found. - Range » Copy formulas without changing their references Worked fine but produced an error message at the end of the process in Excel 2000. Bug fixed (error message) when your target selection was overlapping the source selection. - Formula » Change reference style (e.g. A1 to $A,1 etc.)... Produced an error if the length of the new formulas exceeded Excel's limitations. From now on this will be detected by ASAP Utilities and you will be asked if you want to continue and have the problem-formulas colored yellow. - Columns & Rows » Delete all hidden rows and columns Could produce an error in Excel 2007 when a workbook was opened in "compatability mode" and didn't have the expanded amount of rows and columns that new Excel 2007 files have. - Columns & Rows » Remove all empty rows Could produce an error in Excel 2007 when a workbook was opened in "compatability mode" and didn't have the expanded amount of rows and columns that new Excel 2007 files have. - Columns & Rows » Remove all empty columns Could produce an error in Excel 2007 when a workbook was opened in "compatability mode" and didn't have the expanded amount of rows and columns that new Excel 2007 files have. - Range » Advanced sorting... In some cases when your worksheet used all the available columns (up to IV) the sorting didn't work (nothing happend when you clicked the ok button). This is because ASAP Utilities needs to create a temporary column in your worksheet. If all the columns are used this couldn't be done and the sorting didn't work. In this new version you will get a warning upfront in such cases. - Text » Convert dates... Fixed a bug where the formatting was not correctly applied if you original dates were already dates Excel recognizes. - General A few (minor) bugfixes Version 4.1.1 (7 MAY 2007)New features:- Columns / Rows » Put together rows or columns from several sheets...Improved, now also copies the original column widths to the new sheet - Columns / Rows » View and manage hidden rows and columns... Display the column titles along with the column numbers - Select » Select cells in column with same value (above and below active cell) Has been renamed to: Select » Select adjacent cells in column with the same value Now works on protected sheets too. - Select » Select cells in column with same value (below active cell) Has been renamed to: Select » Select adjacent cells below active cell with the same value Now works on protected sheets too. - Format » Insert filename in header or footer... has been renamed to: Insert workbook's path and name in header, footer or cell... This utility is now also placed in the Fill submenu - Export » Copy sum of selection to clipboard Copy the calculated sum of selected cells to your clipboard. Just like you can see in the statusbar of Excel. - Export » Copy average of selection to clipboard Copy the calculated average of selected cells to your clipboard. Just like you can see in the statusbar of Excel. - Import » Merge files together in a new file... I've added the option to exclude the file extensions (such as .dbf or .txt) from the sheetnames. - System » Resize the Excel application... Resize the Excel application to fixed screen resolutions. This way you can easily test and see how your document or application will look on computers with different screen resolutions. However, the maximum size is limited (by Excel) to your current screen-resolution. - Columns / Rows » Merge column data (join cells)... This utility now remembers your last used settings - Columns / Rows » Merge row data (join cells)... This utility now remembers your last used settings - Columns / Rows » Merge column data (join cells)... Slightly improved. Bug fixes:- Numbers » Round numbers (not formatting, but changing)...If you entered a zero the operation was incorrectly canceled. This is now fixed so you can round numbers to zero decimals again. - Columns / Rows » Put together rows or columns from several sheets... Didn't work and produced an error if one of the selected sheets was protected. - Import » Merge files together in a new file... Accidentally canceled the operation when you clicked on the 'Pick files and merge' button. Furthermore the import didn't work as expected in Excel 2000. These two bugs were introduced in version 4.1.0 and are now fixed. - Select » Conditional select cells... Select all numbers between two values, for example all between 1 and 50, didn't work. This is now fixed. Select non-empty cells didn't select cells with formulas. This is now corrected Select cells with a certain font-size didn't work. This is now fixed. - Numbers » Use a significant number of decimals... Improved. - Range » Paste special with combinations Fixed a few bugs that were related to the arithmetic operations - Text » Convert dates... Better handling/support of dates with month names (mmm and mmmm) - General A few (minor) bugfixes Version 4.1.0 (19 MARCH 2007)New features:- GeneralImproved setup. Better approach for making ASAP Utilities installed as add-in in Excel and better removal upon uninstall. - Sheets » Summarize rows or columns from several sheets... has been renamed to: Sheets » Put together rows or columns from several sheets... - Select » Select cell with maximum value has been renamed to Select » Select cell with largest number - Export » Export selection as HTML table to clipboard... Added the option to specify an class for the TR-tag. You can also specify alternate use of the class. - Range » Advanced sorting... Two sort-options were added: . Day of the week (start on Sunday) . Day of the week (start on Monday) - New worksheet formula: =ASAPExtractFilename(FullFilename, optional separator) Extracts the file name from a full path & filename Example Cell B23 contains: D:\User Guide\screenshots\Sort random (shuffle).xls =ASAPExtractFilename(B23) will return "Sort random(shuffle).xls" - New worksheet formula: =ASAPExtractFolderName(FullFilename, optional separator) Extracts the folder name from a full path & filename Example Cell B23 contains: D:\User Guide\screenshots\Sort random (shuffle).xls =ASAPExtractFolderName(B23) will return "D:\User Guide\screenshots" - New worksheet formula: =ASAPGetFormula(FormulaCell) Returns the formula in a cell in local Excel language. Example: =GEMIDDELDE("A1:B12") Returns empty if the cell does not contain a formula - New worksheet formula: =ASAPGetFormulaInt(FormulaCell) Returns the formula in a cell in international notation. Example: =AVERAGE("A1:B12") Returns empty if the cell does not contain a formula - New worksheet formula: =ASAPMergeCells(inputrange, delimiter, skipemptycells) An easy alterative for the Excel =CONCATENATE() fuction. However the benefit of this ASAP Utilities function: - you can specify a range, for example A1:G1 - you only have to specify a delimiter once - by default empty cells will be ignored - New worksheet formula: =ASAPCountByCellColor(Lookin_Range, Color_Index_Nr) Counts the amount of cells in the selected range that have a certain cell color. - New worksheet formula: =ASAPCountByFontColor(Lookin_Range, Color_Index_Nr) Counts the amount of cells in the selected range that have a certain font color. - New worksheet function: =ASAPGetDomain(vFullURL, Optional bIncludeProtocol) Return the domainname from a given url. - Import » Merge files together in a new file... Option added for text files whether or not to treat consecutive delimiters as one - Select » Select cell with smallest number Select the cell with the smallest number in your selected range. It uses the =MIN() function to determine the smallest value and then selects that particular cell. If there are more cells that have the same minimum value, only the first cell is selected. - Formula » Apply formula to selected cells... Added an option to only apply the formula to non-hidden cells. This is useful in case you have a range with the autofilter used. - Fill » List filenames in folder... Improved. More information is given about the specified files and it the display of various folders and subfolders is more clear now. By default the autofilter is turned on on the list so you can easily make a selection, for example on certain file types. - Fill » Add leading zeros... Added the possibility to add 0 (zero) leading zeros. This changes the number to be displayed without the leading zeros. (The same result can be achieved by using the "Numbers » Convert "textnumbers" to numbers" utility.) - Select » Conditional select cells... Added the options to select unique values. - Vision Control Now always opens expanded, showing the extra options available Bug fixes:- Import » Insert Textfile separated by space...Didn't import correctly in Excel 2000. It separated the data on each space, in stead of treating multiple spaces (consecutive delimiters) as one. - Import » Merge multiple files in a new file. Fixed: Sometimes produced a runtime error 5 in Excel 2000. Fixed: It separated the data on each space in Excel 2000, in stead of treating multiple spaces (consecutive delimiters) as one. - Select » Conditional select cells... Fixed a bug where in some cases the utility didn't find all cells because the option "Expand current selection" was checked but invisible and therefore couldn't be changed. Selecting duplicate values actually selected the unique values in stead of the duplicate once. This is now corrected. - Range » Custom formula error message... Fixed: When you entered a non-numeric value quotes were saved and then doubled. - Range » Empty duplicates in selection Fixed: Didn't properly remove all duplicate values if you range contained cells with errors too. The utility will now skip the cells that contain an error. - Information » Get active cell's page number Fixed: could produce an error on some workbooks wich were created in Excel 97. Also changed the spelling of pagenumber to page number - Sheets » Add sheets (with the names defined in selected cells)... Sometimes it produced an incorrect warning that a sheet already existed. This is now fixed. Furthermore I have expanded the way the program handles exisiting sheets. If one is found you can choose to continue with the rest of the cells or cancel the operation. - Formula » Change reference style (e.g. A1 to $A,1 etc.)... Fixed a small bug. Your selected range wasn't properly renewed in the form when you started the utility, in stead it displayed the range you last used before. - Fill » List filenames in folder... Fixed some bugs. In some occasions it didn't list all the files and it gave wrong file size information for files larget then 1.5 GB. The utility didn't work in Excel 2007, now it does. - Fill » Add leading zeros... Fixed a bug that caused extra zeros if you ran the utility on the same numbers again. - Object/comments » Select all objects Fixed a bug that sometime prevented it from selecting all objects/shapes - Export » Export selection or active sheet as new file... Fixed a bug that prevented this utility from starting in Excel 2007 - Range » Paste special (with combinations)... Fixed some problems with the arethmetic operations in certain combinations. - General Some minor bugfixes and spelling errors. Version 4.0.5 (8 NOVEMBER 2006)New features:- GeneralExcel XP/2002/2003: Improved information on how to solve the problems during the startup when no connection could be made with the DLL. http://www.asap-utilities.com/faq-questions-answers-detail.php?m=74 ASAP Utilities now contains a digital signature. This gives several benefits. For one example, it assures you that the files come from us and have not been tampered with. The company name you will see on the certificate is "eGate Internet Solutions v.o.f.", which is the name of the company behind ASAP Utilities. http://www.asap-utilities.com/contact.php Description of Microsoft about digital signatures: http://office.microsoft.com/en-us/assistance/HA011403071033.aspx - Formula » Insert 'international-style' formula… Insert an 'international-style' formula in all cells in your selection. Uses English worksheet formulas and a comma as list separator and a point as decimal separator. Useful since most examples on the internet or in Excel books are in 'international' English format with the English names for the worksheet functions and the comma as list separator and the point as decimal separator. Now you can copy that formula and have ASAP Utilities translate it automatically to your Excel version. For example the formula =SUMIF(A2:A13,">50")*1.19 will be translated to =SOM.ALS(A2:A13;">50")*1,19 in the Dutch version of Excel with the Dutch language settings in Windows. - New worksheet formula: ASAPGetFontName(Range) Returns the font name from a given cell. - New worksheet formula: ASAPGetFontSize(Range) Returns the font size from a given cell. - New worksheet formula: ASAPGetNumberformat(Range) Returns the number format from a given cell. - New worksheet formula: ASAPIsFormula(Range) Returns true if the given cell contains a formula or an array-formula, otherwise it returns false. - New worksheet formula: =ASAPCountChar(SearchText, SearchValue) This function returns the number of times the “SearchValue” occurs in the “SearchText”. This way you can for example count the number of commas in a cell. This function is case sensitive. - Select » Conditional select cells... Select cells that have a hidden value/formula - Numbers » Convert formulas to their values You have the option to disable the undo. Especially on large amount of data with a lot of formulas disabling the undo will significantly speed up the process. - a few small improvements Bug fixes:- Range » Sort random (shuffle)Produced an error if your worksheet had all columns used (256). ASAP Utilities needs to create a temporary extra column in order to perform its random sorting. If no extra column can be added you will now be kindly informed about this in stead of getting an error message. - Sheet » Protect all sheets... In Excel XP/2002/2003: The use of the options "edit objects" and "edit scenarios" was reversed, meaning you had to turn them off in order to activate them. This has now been correct. - Information » List all fonts in a new workbook Excel allows only a limited amount of formatting in a workbook. If you have a large amount of fonts installed you might run into Excels limit. I have experienced this when I had more than 500 different fonts installed. The error message has been updated to inform you about the possibility that the limit is reached. - Select » Conditional select cells... Improved handling of cells with errors in your selection. ASAP Utilities could produce an error for example if you wanted to select certain cells and there were cells with errors in your selection. - Select » Select cell in selection where value changes Improved. The utility could produce an error if your selection contained cells with an error. - Import » Insert textfile (with options)... Used to produce an error if you cancelled the import. This is now fixed. - Text » Convert dates... Remember settings improved. This now works too if for example the first two options in the to-date were empty. - Fill » Fill empty cells in selection Didn't properly reset the calculation status of your workbook. For example in some cases it set the calculation status to manual and didn't turn it back to automatic. - Export » Export selection as HTML table to clipboard... Used to truncate the subaddress (the part after the dash #) from a hyperlink. This is now corrected. An example of such an address is: http://www.asap-utilities.com/download-alternative-options.php#zip It used to be truncated to http://www.asap-utilities.com/download-alternative-options.php - Worksheetfunction: =ASAPGETHYPERLINK() Used to truncate the subaddress (the part after the dash #) from a hyperlink. - Text » Insert before current value... Now supports adding leading zeros again. - Text » Insert before current value... Now supports adding leading zeros again. - Text » Delete leading and trailing spaces No longer strips leading zeros from numeric values. Time values will no longer be converted to text-values which happened in some cases. - Text » Delete leading and trailing spaces No longer strips leading zeros from numeric values. Time values will no longer be converted to text-values which happened in some cases. - Text » Delete leading, trailing and excessive spaces No longer strips leading zeros from numeric values. - Text » Delete number of leading characters... No longer strips leading zeros from numeric values. - Text » Delete number of ending characters... No longer strips leading zeros from numeric values. - Text » Delete all after n'th character... No longer strips leading zeros from numeric values. - Select » Conditional select cells... Fixed a few bugs. Improved speed. - Select » Conditional row and column select, hide or delete... New option: Select every n'th row/column number - Sheets » Insert sheets(s) name in selected cell(s) This utility will now check to see if all your selected worksheets are unprotected before continuing. - Sheets » Create an index page with all sheets (clickable)... ASAP Utilities will now inform you if you have an existing index sheet that is hidden. - General On some computers the shortcuts did not work. This is now fixed. - Information » Fast calculation on selected cells... Now shows the resulting calculations formatted in your local country settings. This also fixes the bug in the "Copy to new workbook" which produced wrong results on computes which used the point as thousand separator in stead of decimal separator. - Select » Conditional row and column select, hide or delete... Fixed the error when you wanted to hide unique rows - Format » Copy a sheet's page and print settings... fixed the error that occurred when your workbook contained other sheets than just worksheets. For example you got an error if your workbook also contained a MS Excel 5.0 Dialog. - Import » Insert text files separated Excel 2000 only: Improved several text-import utilities that in some cases didn't split the data correctly. - Numbers » Delete the number of leading characters Fixed a bug that stopped ASAP Utilities from continueing when your selection contained cells with formulas. This utility will now work on both numeric and text-cells. Cells with (array)formulas in your selection will be replaced with their calculated values. - a few small bug fixes Version 4.0.4 (20 SEPTEMBER 2006)New features:- Delete leading and trailing spacesCells with (array)formulas and errors will be skipped. - Delete leading, trailing and excessive spaces Cells with (array)formulas and errors will be skipped. - Numbers » Spell numbers or amounts (EN, DE, NL)... Your last used settings will be remembered. If you use rupees (Rs, IRs or NRs) the currency will be listed at the front. This will also affect the worksheet function =ASAPSpellNumber() For example: =asapspellnumber(2450.25,"EN","TRUE","Rs","Rs",,"Paisa","Paisa Only") would produce "Two Thousand Four Hundred Fifty Rs and Twenty Five Paisa" but wil now produce: "Rs Two Thousand Four Hundred Fifty and Twenty Five Paisa" - I have changed the hotkey (accelerator) for the ASAP Utilities in the menu. By default this is the letter A (so you can access the ASAP Utilities menu with Alt+A.) However in some countries this conflicts with other menu items (such as Archivo, Data). Therefore, in the following countries the hotkey is the letter S: . Germany (new) . The Netherlands . Sweden . Spain . Brazil . Portugal - Fill » Insert before / after Your last used settings will be remembered. Bug fixes:- Text » delete leading / ending charactersIn version 4.0.3 I excluded numbers from being altered by this version. That has now been undone, making this utility work on numeric data too. I thought excluding numbers was a better approach but apparently a lot of people use this utility to edit numbers too. - Import » Merge files Produced an error if you had previously used the utility on files in a folder and this folder was no longer available. This is now fixed - Fill » Insert before / after When you apply this utility to numbers ASAP Utililities will no longer convert these numbers to "textnumbers" by adding an apostrophe in front. The insert before/after on formulas has been improved too so that your formulas will stay formulas. You can now for example change a formula from =12*B4 to =12*B4+SUM(A1:A10) - Export » HTML Export In april 2006 I've changed the NOBR setting in the HTML output from <nobr> in each cell to a stylesheet setting for each row: style="white-space : nowrap;". However I found out this doesn't work as good as the old-school <nobr> tag so I reversed this change and added the <nobr> again. Version 4.0.3 (29 AUGUST 2006)New features:- New worksheet function: =ASAPExtractNumbers(ref)This function extracts the numbers from a given range or value. - New worksheet function: =ASAPStripNumbers(ref) This function strips the numbers from a given range or value. - Some minor improvements to several utilities. Bug fixes:- a few bug fixes in the following utilities:. Apply formula to selected cells... . Convert dates... . Export selection or active sheet as new file... - a few small bugfixes and typing errors. Version 4.0.2 (3 AUGUST 2006)New features:- Fill » List filenameWhen creating a list of all filenames in a folder you can now choose whether you want to list the filename including its full path or just the filename (without its folder). - All menu-items now have accelerator keys An an accelerator key is a single character used as a shortcut for selecting the menu items. Pressing the ALT key, followed by the accelerator key gives focus to the menu-item (submenu) or executes the menu-command. The accelerator key is also called keyboard accelerator, shortcut key or keyboard shortcut. However they differ from the shortcuts you can use in the ASAP Utilities favorites menu. The shortcuts there give instant access to start the utility while the accelerator keys just provide a more convenient way to browse through the menu. Bug fixes:- a few small bugfixes and typing errors.Version 4.0.1 (31 JULY 2006)New features:- New worksheet formula: =ASAPSheetName()Returns the name of the sheet the formula is placed on. Bug fixes:- System » Rename current fileIf you specified a filename of a file that already existed that file was overwritten. ASAP Utilities will now ask you if you want to overwrite the existing file. In Excel 2000 the utility made Excel crash when the renamed file was about to be openend. This is now fixed. - Sheet » Copy a sheet’s page and print settings Fixed a bug during the startup of the utility which caused the utility not to work. - a few small bugfixes and typing errors. Version 4.0.0 (29 JUNE 2006)New features:- ASAP Utilities is now partly a COM Add-in (ASAP_Utilities.dll).This gives some substantial benefits: 1. Excel's startup with ASAP Utilities is (much) faster. 2. The utilities run faster 3. We previously had split the utility into several files (sheet.asap, format.asap, etc.). Whenever a utility was run the first time from one of these files, the file had to be opened, which in some cases caused some delay due to a virus scanner or slow network. This is gone now which improves speed. 4. The code is easier to maintain for us and upgrades can be provided much quicker. 5. Control+Shift shortcuts can be used (again) in the favorites menu. The only disadvantage is that Excel 97 is not supported by this new version. There is a special version for the people that still use Excel 97. - Expanded undo. Much more utilities now can be undone. - Range » Advanced sort... (new) Extend the sorting capabilities of Excel. You can now sort on 5 columns and we have extended the characteristics to sort on. You can sort on: . Value . Number Format . Cell Color . Cell Color Indexnr. . Font Color . Font Color Indexnr. . Font Size . Font Name . Bold . Italic . Underline . Strikethrough . Text length . Shuffle (randomize order) . Formula . Email address host (@...) . alphanumeric strings based on their numeric portions. . Year-Month-Day . Year-Day-Month . Month-Day-Year . Month-Year-Day . Day-Year-Month . Day-Month-Year - Export » Export selected chart or range as image (file)... (expanded) Now also supports the vector format EMF. Vector graphics in EMF (enhanced version of WMF) format can be scaled and fully edited in MS Office and other Windows applications. They generally produce the best quality for printing. - Range » Apply formula (improved) 1. The last 100 used formulas are remembered so you can easily re-use them. 2. Users of the non-English Excel versions can now type in the formula in their local language. For example in the Dutch version you used to have to use a point as decimal separator. You can now use the comma. You can also use the local version of worksheet formulas, for example if you have the Dutch version of Excel you can now enter '+SOM(G20:G40)' in stead of the English version: +SUM(G20:G40). 3. We have improved the error handling. You get a warning if an applied formula produces an error. 4. In the range of cells you select the formula will only be applied to cells that have no errors, are not empty and have a value of formula with a numeric or logical result (text cells will be skipped). Array formulas will also be skipped. - New worksheet function: =ASAPGetHyperlink(range, optional sNA) This function returns the hyperlink from a cell. The hyperlink can be one of the following: . existing file or web page . place in your document . e-mail address You can optionally specify a string value for the sNA, this text will be displayed for cells without hyperlinks. If omitted the formula will give an empty result for cells without hyperlinks. Example: =ASAPGETHYPERLINK(A2,"-") - Hyperlink in cell to hyperlink 2 click has been renamed to make more clear what it does: "Activate hyperlinks (create from cell-value)" - Web » Clean web imported data... 1. A new option is added: fix (text) numbers. Sometimes Excel doesn't recognize numbers properly and they are treated as text which often means they are left aligned and can't be used in calculations. This new option solves that. This new option does the same as ASAP Utilities » Numbers » Convert 'textnumbers' to numbers 2. Another new option is added: unmerge cells. This unmerges all merged cells in your selection. - I've added an option to switch on/off the question that sometimes is asked before running the utility, e.g. questions like "Do you want to convert all text characters in selected cells to UPPERCASE?" can now be turned off in the ASAP Utilities » ASAP » Options dialog. - Updated Menu The shortcuts assigned in the favorites menu will now also be displayed in the rest of the menu. Utilities that require additional input or show a form are now recognizable in the menu by the dots at the end of the description ... - I have changed the hotkey (accelerator) for the ASAP Utilities in the menu. By default this is the letter A (so you can access the ASAP Utilities menu with Alt+A.) However in some countries this conflicts with other menu items (such as Archivo, Data). Therefore, in the following countries the hotkey is the letter S: . The Netherlands (new) . Sweden (new) . Spain . Brazil . Portugal - Format » Advanced Transposer... (improved) . formulas will be better preserved and cell-references in the formulas will be automaticly updated as the cells are transposed. . formatting of the cells will also be transposed. . the number of columns you enter is saved and used when you run the utility again. . the example is updated and improved. . the question remove completely empty rows is removed, the procedure is improved so that the data is transposed without creating empty rows. . we have built in a check to see if the amount of columns you enter will actually fit on the worksheet. - Web » Clean Web Imported data... The layout is improved. Your last used settings will be remembered. Fixed the bug that could incorrectly convert dates. These cells are now skipped. - Export » Export selection as HTML table to clipboard... Your last used settings will be remembered. <strike> is added for text that is formatted as strikethrough. Works much faster on large tables (> 500 cells). The HTML code is improved, partly because current browsers support CSS better. - Export » Export selection or active sheet as new file... Your last used settings will be remembered. - Export » Export selected chart or range as image (file)... Your last used settings will be remembered. - Sheets » Protect all sheets The password is now by default masked. It also has the option to protect only selected or all sheets. - Sheets » Unprotect all sheets The password is now by default masked. It also has the option to unprotect only selected or all sheets. - Format » Copy a sheet's page and print settings... The settings for "fit pages" (x pages wide and x pages tall) can now be copied too. - Text » Convert dates... Your last used settings will be remembered. - Text » Make first character uppercase, the rest lowercase Makes the first character uppercase and the rest of the text lowercase: for example BASTIEN will become Bastien. - Sheets » Export worksheets as separate files... Save worksheets as separate files in a folder you select. - Web » Extract hyperlinks Hyperlinks from shapes/images can now be extracted too. - Text » Advanced character removal Besides removing the characters you can now also have them replaced with a certain character. - Select » Expand selection to last row The last cell will be reset first before expanding your selection to the last row. Will now also work on protected worksheets. - Select » Expand selection to last column The last cell will be reset first before expanding your selection to the last column. Will now also work on protected worksheets. - Export » Export selection as values to clipboard Will give you a warning message if your selection contains cells with an error. You can then choose to continue or correct the errors first. - System » Close all files except the current one Has been renamed to: System » Close multiple files at once... - Numbers » Change decimals (not formatting, but changing) Has been renamed to Numbers » Round numbers (not formatting, but changing)... - Range » Empty duplicates on selection. Now works on multiple columns. Cells with more then 255 characters are now supported too. - Fill » Insert filename... Now has an extra option to create hyperlinks to the files. A copy of this utility has been made in the "Information" menu with the name "List filenames in folder..." Your settings will be saved and if you have Excel 2002/XP or later the "pick a folder" dialog will start with your last used folder. - ASAP » Options... A new option is added where you can choose to use the F11 key to switch between fullscreen mode. By default the F11 key inserts a chart sheet in Excel and so will Alt+F1. - NEW: System » Reopen current file without saving changes. Closes the current workbook without saving changes and then opens the last saved copy of the file. - NEW: Text » Replace accented vowels (á, ë, etc.)... Replace the accented vowels in your selected cells with non-accented vowels. For example replace ë, é, è, ê with the vowel e. In some cases the accented characters are not allowed and now you can quickly replace these accented vowels. You can customize the settings. - Text » Advanced character removal... Contains a new option where you can specify whether you want to replace in cells with constants, formulas or both. - NEW: worksheet function =ASAPSPELLNUMBERS(). This is a worksheetfunction of the utility Numbers » Spell numbers or amounts (EN, DE, NL)... A detailed description of this function is given in the User Guide. - System » Close and delete current file. If possible the file will not be deleted but send to the Recycle Bin. If that is not possible you will get a question of you want to delete the file permanently. - System » Create a lot of folders. If you have Excel 2002/XP or later the "pick a folder" dialog will start with your last used folder. - Format » Delete all lines inside selection Has been renamed to Delete all borders inside selection. This utility has also been updated to really remove only the interior borders. It used to remove all borders in your selection. - This version will work in Excel 2007 beta too, although not all utilities have been fully tested in the new version of Excel (yet). - Sheets » Sort all sheets in alphabetical order Used to sort only the worksheets. It now sorts all sheets. - NEW: Columns / Rows » Remove all empty columns This utility will remove all empty columns in your selection. An empty column is defined as a column where none of the cells contains data. - Columns / Rows » Color each n'th row or column in selection... Your last used settings will be remembered. - Import » Merge files together in a new file... Your last used settings will be remembered, including your last used folder. - Range » Paste Special (with combinations)... Your last used settings will be remembered. - Format » Copy a sheet's page and print settings... Your last used settings will be remembered. - Range » Find / replace in all sheets... Your last used settings will be remembered. - Formula » Custom formula error message... Your last used settings will be remembered. - Several small additions and improvements. Bug fixes:- Information » Count duplicates in selection1. Now also handles cells with more than 255 characters correctly. It can now recognize cells with the maximum length of 32767 characters. 2. Now supports multiple selected ranges (multiple areas) - Select » Conditional select » duplicates Now also handles cells with more than 255 characters correctly. It can now recognize cells with the maximum length of 32767 characters. Select duplicates now works when you have selected multiple areas (non-adjacent cells). - Information » Fast calculation Better calculation of duplicates and unique values. It now also handles cells with more than 255 characters correctly. It can now recognize cells with the maximum length of 32767 characters. - Text » Convert to upper etc. keeps formulas, but converts them to uppercase (if you have text displayed). Is doesn't convert formula's to values anymore. - Text » Start first word with uppercase. [Now also converts the rest of the text to lowercase so for example BASTIEN becomes Bastien]--->[reversed to original] Start first word with uppercase now only changes the first character to uppercase. New utility Make first character uppercase, the rest lowercase will convert the first character to uppercase and the rest to lowercase: BASTIEN will become Bastien - Select » Select cells with maximum value. In some cases the cells with the maximum value were found but were not selected. This had something to do with the number of decimals the max value contained and the way they were displayed in Excel. E.g. a value of 21,723801267259 was displayed as 21,72380127 which didn't match when we used Excel's built in search function. We've changed the way of selecting the cell with the max value so it works in these occasions as well. It now also checks at the start if you selection contains numbers. - Objects/Comments » Delete all objects from current sheet Remove objects handles form/VBA buttons better. - Web » Clean web imported data... Remove objects handles form/VBA buttons better. Fixed the bug in the trim option, that could incorrectly convert dates. These cells are now not trimmed. - Improved handling of the location of the favorites menu. - Favorites menu The cursor stays highlighted after you add or remove a tool. The insert and delete button have an accelerator key (Alt+i and Alt+d) - Information » Count duplicates in selection. In some cases duplicates weren't correctly found (numbers with decimals), and I think also depending on your computer settings (different from US/international). - Fill » Overwrite filled cells in selection. Did not work when you entered a zero (0). This is now corrected. - Improved range rebuild when you select entire rows or columns where the first rows/columns are empty. - Select » Conditional column and row select, hide, delete... Remove duplicates in column fixed Hide duplicates in column fixed - Fill » Fill 'between values' linear in selection (horizontal) Didn't work, fixed it and is now working - Fill » Fill 'between values' linear in selection (vertical). was swiched in the menu with the horizontal version. That is now corrected and horizontal fill now works too. - Range » Paste Special (with combinations)... Operations (like multiply) now function too - Fill » Fill 'between values' linear in selection (vertical). was swiched in the menu with the horizontal version. That is now corrected and horizontal fill now works too. - Fill » Copy values to empty cells below filled cells in selection Resulted in an error when your selection included a cell in row number 1 and that cell was empty. This is now fixed. - General When you close Excel, and then chose "No", the ASAP Utilities menu was removed. This is now fixed. The ASAP Utilities menu will only be removed if you really close down Excel. - System » Rename current file... When you use this tool to rename a template file (xlt) the renamed file was opened as a workbook based on the template. This is now corrected. The renamed template will be opened for editing. - Text » Delete leading and trailing spaces Fixed the bug that could incorrectly convert dates. These cells are now skipped. - Text » Delete leading and trailing and excessive spaces Fixed the bug that could incorrectly convert dates. These cells are now skipped. - Range » Find / replace in all sheets... Can not handle large text cells too that contain more than 255 characters. - Text » Advanced character removal Can not handle large text cells too that contain more than 255 characters. - Select » Expand selection to last row Now support multiple selected areas - Select » Expand selection to last column Now support multiple selected areas - Select » Inverse selection Produced an error if you cancelled the selection of a cell. - Information » Count unique values in selection Produced an error if your selection contained cells with an error. - Numbers » Use a significant number of decimals Gave an error when you used it on cells without decimals. The speed of this utility is increased too. - Range » Find / Replace in all sheets... Bug fixed in combination with Excel 2003. - System » Clear recent files list in file menu Used to reset the amount of recent files back to Excel's default which is 4. This is now fixed and your setting will be kept. - Objects » Select all objects on current sheet Gave an error if the sheet contained comments. A workaround has been made to make it work in that occasion too. - Several (minor) bug fixes. - Corrected some typing errors. Version 3.10 (28 june 2005)New features:- Select » Conditional select. number (constant), e.g. 12 . number (formula), e.g. =12*2.20371 . number (link to sheet), e.g. =sheet1!A1 . number (link to file), e.g. ='[Test alignment.xls]Sheet1'!$A$1 - Range » Remove empty rows This could already be accomplished with the SELECT » Conditional row and column select, hide or delete function, but it is now available as a single utility. - Columns/Rows » Color each n'th row or column. Now remembers your last used color. - Columns/Rows » Merge column data (join cells) You can now specify how to handle empty cells. You can also specify if the cells have to be treated as formulas, values or formatted text. I've also added the option to merge with the tab- character of use a line-break. - Text » Insert before/after Improved combined insert before and/or after An apostrophe can now also be inserted before. This can be handy when you want do display your formulas as text. You can also specify if the cells have to be treated as formulas, values or formatted text. You can exclude hidden cells. (comes in handy when you're working with filtered ranges). - Fill » Quicknumber numbering of cells The cells were always aligned right. This is now optional. - Format » Wrap text Wraps the text in selected cells, which makes long lines break into multiple lines. - Format » Unwrap text Unwraps the text in selected cells. - Web » Clean webimported data Added the option to remove all cell-borders. - Web » Extract hyperlinks Extracts the hyperlink-address of the cell. This value can be placed in the cell itself or in the column next to it. - Export » Export selection or active sheet a new file Export range as picture, the image size has been expanded from 800x600 px to a maximum of 5000x5000 px - Several small additions. Bug fixes:- False Virus alert in sytem.asap - Gone ?Some virusscanners reported a virus in system.asap. Of course this was a false error, but it caused a lot of "stress". The reason was caused by the codeline: insertline() which was in the System » Macro/VBA information » Code washer part. Here you can choose to remove empty lines, commentlines and the indentation from your vba modules. Do do this we used the .insertline() command. Since this caused some virusscanners to panic, we've used another approach to accomplish the same result. - Fixed problems that could occur when the location of the favorites menu could not be found or was not properly set. - Selection Fixed an annoying bug that sometimes caused to change the selection of your cells. This could happen when you selected the entire sheet or entire columns or rows. - System » Remove all macros/VBA from current file On some systems this produced a compile error. This is now solved (at least on my test-systems). - Objects/Comments » Comment tools There was a bug in the "Create report in new file" feature as comments are reported (incorrect) multiple times: Comments of the first sheet were printed again as comments belonging to the second sheet if the second sheet does not contain any comments itself. The wrong duplicates were repeated until a sheet contained a comment again. - Export » HTML export Fixed a combination that produced incorrect HTML tags (color outside <td> tag) - Export » Export selection or active sheet a new file Fixed the bug that produced an error in Excel 2003 "The command could not be completed by using the range specified. Select a single cell within the range and try the command again." Version 3.09 (20 april 2005)New features:- MenuA few specific ASAP Utilities functions have been placed in the submenu ASAP. - ASAP :: Find you utility (search help) Search through the entire menu and descriptions of all ASAP Utilities. If you don't enter a search phrase it will list all tools. - Select :: Conditional select cells . select odd rows . select even rows . select where font is strikethrough . select visible cells - Select :: Conditional row and column select, hide or delete . select odd rows . select even rows - Range :: Find / Replace in all sheets Besides the normal text you can now also search and replace a few special characters: . line feed (alt+enter, chr(10)) -> use {lf} . carriage return, chr(13) -> use {cr} . horizontal tab, chr(9) -> use {t} - Columns/rows :: Delete all hidden rows and columns Removes all hidden rows and columns on the active sheet. Useful when you want to pass to clean up the file before distributing and delete all unnecessary information. Be careful when you have formula's with references to data in the hidden cells. - Export :: Export selection or activesheet as new file Added the option to directly export to clipboard in space-delimited format. - Information :: File and selection info. Returned the information in A1-reference style. New is that it now looks at your settings. If you use the R1C1 reference style it will show it in that format. - System :: Find and remove external links Search is now also performed in all rangenames. If you delete a range-name with an external reference and have used it on you sheet, the error value '#NAME' is displayed in the cell or cells that use the defined name. - Web :: Optimize web import Has been renamed to Clean web import Bug fixes:- Spell check on the menu and tools description- Numbers :: Convert formulas to their values a very very long lime when you selected then entire worksheet (A1:IV65536). - System :: Find and remove external links Returned an error when you selection on a worksheet was not a range but e.g. a chart. - Sheets :: Protect all sheets Returned an error when your workbook contained other sheets than just worksheets. Now you can also protect other sheet-types (like charts). Returned an error when you had a group of sheets selected. - Range :: Find / Replace in all sheets Returned an error on hidden sheets. These sheets are now excluded from you search. A label shows you if there are hidden sheets. - Fill :: Quick numbering of cells If your selection started on row nr 1 and you choose "restart numbering if the value in the column changes" the follwing error ocurred: "Application-defined or object-defined error. Version 3.08 (14 october 2004)New features:- Range :: Find / replace in all sheets.Allows you to easily search in all worksheets at once. The results are presented in a list. When you click on a result you immediately will be taken to that value. Replacements can be done one by one or in all worksheets at once. The result box can be expanded, depending on your screensize. - Numbers :: Numbers to words This function has been renamed and exanded. The new name is 'spell numbers or amounts (EN, NL, DE)' It spells out the numbers for you in English, Dutch or German. You can optionally choose to add a currency or type one yourself. The spelled numbers will always be rounded to two decimals. - Objects :: new name : Objects/Comments Added several comment tools. You can now easily: . create a document with all information contained in the comment fields. (sheetname - cell address - name - value - author - comment) . place the value of the comment in the next right column . move all comments to their default position . resize all comments to fit their content . change the font name and size in all comments. . replace text in comment fields. - Custom functions Added a new function which allows you to read the content of a cells comment. =ASAPGetComment(reference), i.g. =ASAPGetComment(B12) - Custom functions Added a new function that allows you to retreive the colornumber of a cell. This can be of use when you want to sort cells by their color. =ASAPCellColorIndex(reference), i.g. =ASAPCellColorIndex(B12) - System :: Find and remove external links When you have links in your file to other workbooks, Excel asks you whether you want to update your links or not. Sometimes you want to get rid of these links, but that is not always easy. This new tool searches your entire workbook for external links, presents them to you one by one and askes if you want to remove the link. The value in the cells with external links will be replaced by the actual value in stead of the link. - System :: Close all files except the current one Shows all open files in a list which allows you to quickly close all unnecessary files. Has a (default) option to notify to save before closing non-saved files. - Favorites menu Added an option to reset to default settings. These are the tools that used to have built-in shortcut-keys. Now immediately rebuilds the menu when loaded. - Columns / Rows :: "Merge columns" Now supports multiple selection areas - Export :: HTML export Improved layout with all the settings in one view. Added the option to specify a <td class="">. The generated code can now, besides your browser, also easily be opened with Notepad. You can specify whether you want blank cells to be left empty or to be filled with a Bug fixes:- Favorites menu Error in Favorites menu in Excel 2003 fixed. - Menu Error in menu-creation fixed when the menubar was protected. In this case the protection will temporary be removed, the ASAP Utilities menu will be created and then the menubar will be protected again. (Error # -2147467259 Method 'Add' of object 'CommandBarControls' failed) - Range - Copy formulas without changing their references, fixed the error in Excel 2000. - Sheets :: Show all sheets Fixed the error when your workbook contained charts or a MS Excel 5.0 Dailog. - Export :: export as CSV file Now correctly handles whether you want to export hidden cells or not. The previous version never exported hidden cells. - Text :: Convert dates An error occurred when trying to convert ancient date, i.g. 01-12-1753. The date-converter now checks for valid dates, since Excel doesn't recognize dates before 01-01-1900. Version 3.07b (7 may 2004)New features:- Text - Remove ending carriage returns (alt+enter)- Options - Dialog expanded with "contact information". If you encounter problems and need to contact us, please provide the information in this dialog. It will tell us information about your operating system and excel version, language settings and the installation path of ASAP Utilities. - Favorites menu - option to reset to default settings. These are the tools that used to have built-in shortcut-keys. Bug fixes:- Compile errors in hidden modules in Excel 97 fixed. Version 3.07 (20 april 2004)New features:- Export - Export selection or sheet as new fileCSV Export, conform according to the international standard or with you own specifications. - Menu, New in the Portuguese and Brazilian version of Excel, the shortcut for the ASAP Utilities menu will be changed from A (also Archivo) to S. - Menu - improved error handler with better description that will help to solve possible problems. - Menu - shortcuts can you all be assigned by yourself in the favorites menu. All existing built-in shortcuts have been removed. By default if not already exists a personal favorites menu will be installed with all the previous shortcuts. Instead of the Ctrl+Shift combination they are now a Ctrl+Alt combination - Text - Advanced character removal. Advanced utility that gives you control on any character, including invisible ones like linefeeds(enter). - System - Macro/VBA information. This utilties has the capability of deleting your code procedure by procedure. It also contains an option to export your VBA code. Will we expanded in future versions. Bug fixes:- Favorites menu, fixed the bug when you tried to changethe location of the personal menu file. - Fixed the bug that caused the search and replace box to be filled after the startup of ASAP Utilities. - Text - convert dates. Fixed the error when your selection contained empty cells. - Apply formula : turned off the screenupdating. - Handle lowercase/uppercase. Doesn't generate an error anymore if your selection contains empty cells. - Range - Copy formulas without changing their references, fixed the problems causing it not to work properly in Excel 2002/XP. - System - Remove all macro's from current file. Fixed the XL2002: Error Message: Run-Time Error '1004'. - During the startup ASAP Utilities will now check if all additional required files are available (the .asap files) Version 3.06 (23 september 2003)New features:- FavoritesCreate a submenu with your favorite tools. You can also assign your own shortcuts. - Range Paste both values and formatting at the same time. - Sheets add sheets with the names defined in selected cells. Now has the possibility to use a "template" sheet. - Object Show the flowchart and connecter commandbars. This works faster when you have to create a flowchart. - Range Conditional select now also selects odd or even numbers. Bug fixes:- XL2002: Error Message: Run-Time Error '1004': ProgrammaticAccess to the Visual Basic Project Is Not Trusted This happens when ASAP Utilities tries to access your vbProject. (see also: http://support.microsoft.com/default.aspx?scid=kb;en-us;282033) - sheet-index generator, chart indexes - convert dates (i.e. with spaces as separator) - Link to the website when a new version is available. - The trim function now also handles "internet spaces", chr(160) correct. - Fill empty cells in selection. Handles it correctly when you want to enter a zero in alle empty cells. Version 3.05 (12 januari 2003)New features:- SheetsSummarize sheets. Is now possible to create a dynamic consolidation of rows/columns on various sheets. Bug fix:- Fixed the compile error in Excel 97 for a fewfunctions (remove all macro's, optimize webimport..) Version 3.04 (9 januari 2003)New features:- SelectMove selection. Easily move your selection to left, right, up or down, using alt+l, alt+r, alt+u or alt+d - Range Conditional select. Select cells with a certain length (i.g. 8 characters long) - Range Pastespecial, with multiple options. I.e. paste both values and formatting. - Web Optimize webimport. Easily clean a tabled copied from the intenet into Excel. I.e. remove hyperlinks, font colors, etc. - Information Fast Calculation, smoothened and expanded. Count unique values added. Option added to copy the information to a new workbook. - System Rename file. The renamed file is added to the recent- files list. Rename file. The utility asks for a new filename in the folder where the file is currently saved. Bug fix:- TextConvert Dates, fixed buffer overflow, for example with a date like 20020508 - Fill Insert before and Insert after. Fixed the error when your selection contained formula's with errors. Now these cells will be skipped and the operation continues. - Numbers Convert formulas to their values. In some cases a pop-up appeared that workbook ??? could not be found. This is now solved. - Menu Fixed the bug that appeared on some system (compile error in hidden module menu). Version 3.03 (2 juli 2002)New features:- TextDate converter. This will convert a text-string-date to another formatted date. I.e. 010101 becomes 1 january 2001, or 2001-12-02 becomes 12 feb 2001. The conversion is very flexible. - Undo I've built an undo option in a few functions. In time more most function will get an undo. - Select Conditional select, added options to select cells that are NOT bold, underlined or italic. - Range Randomize list, by row. Easily randomize the order your list is shown. - Sheets Add sheet (inputbox will appear for the name) If you enter a name that exceeds 31 characters ASAP will automaticly reduce it to 31 characters. - Custom formulas ASAPCountShades() : Counts the number of cells that are colored within the given range. ASAPSumByFontColor() : Sums the cells with a given color in a given range. The color is Excels colorindex (1-56). ASAPFontColorIndex() : Returns the fonts colorindex number (1-56). ASAPFullFilename() : Returns the full path and filename of the active workbook ASAPFileName() : Returns the filename of the active workbook ASAPFilePath() ; Returns the path of the active workbook Bug fix:- SelectConditional select. Fixed the bug when trying to select text that was "not like" - Import Merge files together in a new file. The ability to import each file on a new sheet is now available. - HTML export removed double ">>" when exporting without a font attribute. - Range Hide errors updated, will now work with in all languages. - Text Make last character super- or subscript will no longer give you an error message if there is a numeric cell in the target selection. - Quicknumber Fixed the error when the leading zero's was less then the number-length. (i.e. 2 leading zero's on 100). - Update info Fixed the "error 13" on Excel 97 machines, when the update information appears. I've also added an option not to hide the update-reminder for a period of 10 days. Version 3.02 (31 JANUARY 2002)New features:- Conditional select.Select text and choose whether to match case or not. Did you know you can already use wildcards (*) in your search string? - Export New option let's you choose not to include hidden cells in your export - Apply Formula formula's will be preserved - Conditional Select select text and choose whether to match case or not. - Import Merge files. Easily combine multiple file into one file. - Range Hide or replace formula errors. In stead of having a formula error displayed as #N/A you can now easily use this utility to display a custom error-messages. - Color Rows and Columns when asked to continue, the cancel option now works. - HTML export. Hidden cells will now be excluded from the generated html-table Bug fix:- Faster start of ASAP Utilities- Close ASAP Utilities all submodules will first be closed and after that ASAP Utilities will be closed without errors - System Remove all VBA from current workbook now checks to see if the vba project is not protected. - HTML export. The font tag will now be closed (</font>). - Menu. The Chart menu will now have only the options available that apply to charts or general functions. - Menu In the spanish version of Excel, the shortcut for the ASAP Utilities menu will be changed from A (also Archivo) to S. | ||
|
Home
Sitemap
Search
List of all utilities
Printer friendly page
^ Back to Top
© 1999-2008 - Bastien Mensink - ASAP Utilities - Excel software - A Must in Every Office BV - The Netherlands
|