ASAP Utilities 3.11 beta 28 march released

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:
advanced sorting
New menu with the shortcuts visible you assigned:
New menu with the shortcuts visible you assigned
Apply formula with a history:
apply formula with a history
A new option to switch on/off the question that sometimes is asked before running the utility
new option to switch on/off the question that sometimes is asked before running the utility
More utilities can be undone
more utilities can be undone
Protect all (selected) sheets improved:
Protect all (selected) sheets improved
Export worksheets a separate files
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

73 comments

Ch.V.Madhu

Sir,

I want Organisation chart in ASAP-Beta.Please create coming version and i am facing one problem in excel.shortcut keys are not working. firtst one is ctrl+alt+I & ctrl+alt+L.

Edouard

I'm simply not capable of 'applying a formula'. No matter what I do (apply the formula to a value, another formula or a reference) I always get the same error in my both my Windows Server 2003 & my Xp set up with Excel 2003.

Suppose my formula is: *100 (or even – 100), the error reads:

Sorry your formula generates an error: Method Calculate van klasse Range is mislukt. Please re-check your formula: [*100].

Common mistakes are the incorrect use of decimal separators (…) thousandsseparator (…) list separators.

_(n.b. you'll notice there is no such separator)_

Paolo

hi, on my pc version 3.10 is running. I'm trying to install version 3.11 but I
received a "Startup Error": "Sorry, something went wrong , I can't connect to c:\program files\asap utilities\asap_utilities.dll". I checked "Trusted add-ins" but received the same error. I'm administrator, so I registered dll with no problems. Any idea? thanks for your support

Edward F. Voelsing Jr

I continually get the "Error" message. I would like to get the updates on a disk, if possible?

Thanking you in advance,

Ed Voelsing

AOlson

Bastien-

Great add-in! Look forward to seeing the bugs worked out on the 3.11 version.

The Merge Columns and Merge Rows macros do not work. For Merge Rows get a text box asking to select rows even though they were already selected. For Merge Columns, get the form for adding an intermediate character or linefeed, etc, but merge does not happen.

arc

i am not able to set up it says files are corrupted

Rob

I've used the older versions of ASAP utilities and it is a great add-in.

I tried the beta 3.11 version and after I installed it everytime I start Excel 2003, the installer for Visual Studio 2005 (which I also have installed) comes up and runs. I let it run all the way through which took some minutes hoping it would go away, but the next time I started Excel it came back. I tried repairing my VS 2005 install (which took about 30 minutes) and it didn't solve the issue. The only fix I found was to uninstall 3.11.

I didn't look into the issue very deeply, but there is probably a file that the 3.11 is installing that is older than the version in Visual Studio 2005.

I'm back to 3.10 which is fine for now.

J Mooney

FYI: Not sure what may be the cause, but have downloaded the 3.11 Beta version three times over 2 separate sessions (weeks apart). In all 3 downloads, [all over dial-up @ 45 kps], have had download end at 868,272 bytes, showing @ 85%. Download window indicated file size of 1 MB, which correlated well with the % download running value.

In all 3 cases, when ran the EXE, an error message popped up saying the file was corrupted [which could be the case if the download terminated early] but 3 times over 2 separate sessions? Seems odd to me. Figured it was at my end first 2 times, but as the third was an immediate re-start after the 2nd failure, I have just about ruled that out. Have had no similar problems with downloads on this pc or dial-up link. Its just slow, but line condition is good.

PC is

Bob Saunders

I have been using your ASAP utility for over a year and I love it!. I downloaded the 3.11 Beta a couple of weeks ago and encountered the following problem:

I have several text files I am trying to merge into a single worksheet. (I have done this same process for months with previous versions).

Import>Merge together in a new file>Text file, pipe (|) seperated>Merge into 1 sheet

Then I select the files I want merged. I usually pick dozens but for testing I was able to get the error with only 2. When I click "Merge files" I get the following error:

Runtime error "-2147221080 (800401a8)"
Method "~" of object "~" failed.

The files I am selecting are text files that have the extention ".excl" They actually do not have any pipe characters. I use that option because I want the rows imported into a single column and I have to use text-to-column to parse the data out… there is no delimiter in the row that is consistent.

Bob

Bob Saunders

Just quick note, I uninstalled the beta 3.11 and downloaded 3.10 and the retryed the steps above. It worked just fine. Thanks

Bob

Bastien

Hello all,

I have just put a new version online, 3.11.s d.d. 22 march 2006. This fixes most bugs currently reported and has a few new/enhanded utilities.

Thank you all for testing,

Kind regards,
Bastien

To answer a few comments (if not already done by email):

Hello Bob,

Please upgrade to the most recent version.

Hello J Mooney,

Perhaps something is wrong with your internet provider. The file online is complete. You could try the alternative download location which is on a different server.

Hello Rob,

This is strange. I don't have Visual Studio installer so I cannot reproduce it. However both 3.10 and 3.11 use the same setup program.

Hello arc,

Please download again, try the alternative download location or request the file by email.

Hello AOlson,

Please upgrade to the most recent version. If you still have problems, can you send me an example file? I am not able to reproduce the error.

Hello Paolo,

Can you test it again with the current version?

Hello Edouard,

Can you send me an example of the workbook in which you are getting the errors?

Paul Winter

Is there a way to assign the "Convert formulas to their values" macro (or any other ASAP macro) to a button on a toolbar?

Bastien

Hello Peter,

I find it easier to use keyboard shortcuts. By default the shortcut for
"Convert formulas to their values" is Control+Alt+P. You can assing your own shortcut keys in the favorites menu.

To use a button; you can do this by dragging an item off the ASAP Utilities toolbar. First create it:
ASAP Utilities » ASAP » (Re)build the ASAP Utilities toolbar

You can adjust the size of the toolbar by dragging its border.
If you wish to remove toolbar buttons you never use, select in the menu:
View » Toolbars » Customize.
You can also right-click on the toolbar and choose “Customize…”, the last item in the popup.
Then drag the unwanted buttons away from the toolbar or drag them to your own bar. When you're finished, click Close to close the Customize dialog box.

Kind regards,
Bastien Mensink

andy

Mr. Bastien,
I installed the beta release ASAU Utilities 3.11 and this is the outcome after installation.

Error message

Unable to execute file:
C:\Program Files\ASAP Utilities\install as addin.xls

ShellExecuteEx failed;code 2
The system cannot find the file specified

Bastien

Hello Andy,

This is strange. Usually running the "install as addin.xls" will start Excel and open the file.

The workaround now is to perform the installation again, and UNcheck the checkbox in the last screen that will ask you if you want to have ASAP Utilities installed as addin everytime Excel start.

Then after the installation is finished:

1. Run Microsoft Excel
2. On the tools menu, choose Add-Ins.
3. Use the "Browse…" button to locate the ASAP Utilities.xla file (default is C:\Program Files\ASAP Utilities\)
4. If Excel asks you to copy the file to the library folder, answer "no".
5. Check the "ASAP Utilities" check box.
6. Next to "Help" in the menu, there is an option called "ASAP Utilities".

Kind regards,
Bastien Mensink

Edouard

Bastien,

'applying formula' works in the newest beta version 3.11s, but not in version 3.10 (which I use at work). Every spreadsheet gives the same error. Even simply entering 12, 13, 14, 15 in cells A1 through A4 and subsequently highlighting these cells and applying the formula *12 gives the mentioned error.

Edouard

Paul Winter

To Bastien: Thank you for the reply. However the macro I am interested in "Convert formulas to their values" does not seem to be one of the standard buttons created when you build the ASAP toolbar. There appear to be a number of duplicate buttons but not the one I am interested in. I would like to specify the macro for a button by going through the Customize / Assign macro option but the only thing that shows up is ! (same for all buttons) so I don't know how to point it to a different macro. Maybe the only solution is for the ASAP authors to fix the Build Toolbar function.

Alex Rodriguez

Hello,

Thanks to your hard work, the world (at least the excel users of the world) is able to enjoy great benefits with your program. Thanks so much.

I ran into a snag with the latest beta version (3.11s). I just installed it and immediately tried the Export Worksheets as separate files (which is one I would be using very often, thanks for bringing this feature) and I ran into two problems:

First, when I tried it on a spreadsheet with about 14 tabs, it seems that it works, but when I go to where I specified to save it, the files aren't there. However, when I tried it again it says that the file already exists and if I want to overwrite them. Another issue here is that the radio buttons for selecting to overwrite is grayed-out. It only tells me that the file exists when I say ok and it proceeds to save the file, in which case it asks me to overwrite each file.

So, I thought, may be that because I just installed the upgrade, that I should close and re-open excel and then try again. That's when problem number two happened, when I load excel, the ASAP menu comes up while excel is loading, but shortly thereafter it disappears and doesn't come back when it finishes loading. I go to Tools\Add-ins, and the option is selected, however.

I tried re-installing and when excel opens, the menu is there, but the moment that I close excel and re-open it, again the menu disappears. I had to revert to the last beta that was working for me (3.11m).

I'm using Excel 2000 on Windows 2000.

Thanks much…………….Alex Rodriguez

BTW: I had sent this to you via email. Then I realized that posting it here would have been more appropriate. Thanks.

Bastien

Hello Alejandro,

Thank your for reporting this. I have been working on it this afternoon. I've also enable the options to choose to overwrite existing files, skip or ask. I cannot explain or reproduce the problems you got after the error.

At this moment I am also checking some other things.

Probably this weekend but definatly monday I will put a new version online.

Thank you for the quick testing en reporting,

Kind regards,
Bastien

Satish Dandekar

This is a fabulous improvement in an amazing utility. With out ASAP excel is rudimentary. With ASAP excel is extraordinary

Jared

It would be great if there was an easier repository for building custom fuctions than writing VB. For example, I've always wanted to create a function that would take an email address and turn it into a URL. I know how to build this in Excel with the formula editor, but if I had to do it in VB I would be stumped. I'm not a good coder.

Bastien

Hello Jared,

To turn email adresses into urls you can use ASAP Utilities. Select the cells with the emailaddresses and then in Excel choose:
ASAP Utilities » Web » Activate hyperlinks (create from cell-value)

Extra functions in Excel have to be written as User Defined Functions (UDF). If you cannot do that you might find sites where you can download add-in with addinional Excel functions. ASAP Utilities has a few of them, but there are sites. If you need special functions a lot, spending some time in the Visual Basic editor might help, it is not that hard en there are plenty of good examples available on the internet.

Oscar

Absolutely love ASAP Utilites. Use it daily!!!

I'm having trouble with Advance Sorting (Range\Advance Sorting). I use it a couple of times but now it only goes through the motion but doesn't change the order. Is there a was to put number an a cell depending on the color of the cell? to created a value to sort on. i.e. green = 1 blue =2, red=3 orange=4 etc…