Today I have released version 3.11.t beta 28 march 2006.

This new version contains some bugfixed (Export sheets as separate files in Excel 2000) and has an improved progress indicator. Furthermore the advanced sorting capabilities have been expanded. You can now sort dates directly by:
. Year-Month-Day
. Year-Day-Month
. Month-Day-Year
. Month-Year-Day
. Day-Year-Month
. Day-Month-Year

I have also found out that the way I was using this blog for the information on the new beta version wasn't used right. Therefore I have created a 'permanent' page with information on the new version.
I will post the new details in posts like this one and then refer to that page.

//www.asap-utilities.com/blog/index.php/beta/

Several people have sent me some good feedback on the beta versions (thanks!). I've used these suggestions to make improvements and now a new beta version is available: 3.11.t beta 28 march 2006.
For as far a I could test it, since beta q version 3.11 will also work on XP limited user accounts (after installed as administrator). The "could not connect to DLL" startup-errors which sometimes occurred should be gone in this new version.

The new version of ASAP Utilities is almost ready. Please help us to remove any last remaining bugs by downloading and testing the 3.11 beta version. Download, test, and let us know if you think it is good enough.

My thanks goes out to each and every one of you for your suggestions and bug-reports the past year. ASAP Utilities continues to grow and improve thanks to your support, your suggestions and your encouragement.

Download

Try the new beta version, date 22 march 2006:
ASAP Utilities 3.11.t beta setup.exe (update: no longer available).

Please send us your suggestions, bugs and comments.

User Guide

A brand new User Guide will show you how to get the best of ASAP Utilities. A concept version (118 pages) can be downloaded as PDF document. The document is optimized for duplex printing.
ASAP Utilities User Guide.pdf (2.9 MB pdf). (update: you can find the latest version here).

The major changes in this new version 3.11 beta

• 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. We will create 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:

1. Value
2. Number Format
3. Cell Color
4. Cell Color Indexnr.
5. Font Color
6. Font Color Indexnr.
7. Font Size
8. Font Name
9. Bold
10. Italic
11. Underline
12. Strikethrough
13. Text length
14. Shuffle (randomize order)
15. Formula
16. Email address host (@...)
17. Year-Month-Day
18. Year-Day-Month
19. Month-Day-Year
20. Month-Year-Day
21. Day-Year-Month
22. Day-Month-Year

• 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.
• Export » Export selected chart or range as image (file)...
  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.
• 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.
• Format » Advanced Transposer (improved)
  1. formulas will be better preserved and cell-references in the formulas will be automaticly updates as the cells are transposed.
  2. formatting of the cells will also be transposed.
  3. the number of columns you enter is saved and used when you run the utility again.
  4. the example is updated and improved.
  5. the question remove completely empty rows is removed, the procedure is improved so that the data is transposed without creating empty rows.
  6. we have built in a check to see if the amount of columns you enter will actually fit on the worksheet.
• 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,"-")
• 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 ...
• Hyperlink in cell to hyperlink 2 click has been renamed to make more clear what it does: "Activate hyperlinks (create from cell-value)"
• 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.
• 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 (ASAP Utilities):
  . The Netherlands (new)
  . Sweden (new)
  . Spain
  . Brazil
  . Portugal
• 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.
• 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 with and x pages wide) 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.
• Several small additions and improvements.

Bug fixes

• Information » Count duplicates in selection
  1. 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
  1. Now also handles cells with more than 255 characters correctly. It can now recognize cells with the maximum length of 32767 characters.
  2. 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
• 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.
• 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
• Range » Paste Special (with combinations)...
  Operations (like multiply) now function 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.
• 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 closed 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 now handle large text cells too that contain more than 255 characters.
• Text » Advanced character removal
  Can now 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
• Several minor bug fixes.
• Corrected some typing errors.

A few screenshots

Advanced sorting:

New menu with the shortcuts visible you assigned:

Apply formula with a history:

A new option to switch on/off the question that sometimes is asked before running the utility

More utilities can be undone

Protect all (selected) sheets improved:

Export worksheets a separate files

Installation notice

You must be logged in as an administrator or as a member of the Power Users group when installing ASAP Utilities. When the installation is finished every user on the computer is able to use ASAP Utilities.
Installation is easy. Best way is to make a backup of the current files in the ASAP Utilities folder and then run the installation.
If you have a previous version of ASAP Utilities installed, select that folder (the setup-program should detect it though), usually this is 'C:\Program Files\ASAP Utilities\'. If you are not sure about that location you can see it in ASAP Utilities. Start Excel and then:
ASAP Utilities » ASAP » Options