Filed under: ASAP Utilities on April 28th, 2008 by Bastien | No Comments »
I have just put a pre-release of the new version of ASAP Utilities online.
This new version contains a few improved utilities and a few bug fixes.
Filed under: ASAP Utilities on April 28th, 2008 by Bastien | No Comments »
Filed under: ASAP Utilities on January 31st, 2008 by Bastien | 4 Comments »
Last week version 4.2.2. was released. Unfortunately this version contains a few bugs. I have solved these and a new version will be released soon.
The most annoying bug is in the utility "Range » Find / Replace in all sheets...". When you pressed the "Find all" button you immediately got Error 401: Application-defined or object-defined error which makes the utility unusable.
You can download a pre-release of version 4.2.3 where these bugs are fixed.
Filed under: ASAP Utilities on January 31st, 2008 by Bastien | 4 Comments »
Filed under: ASAP Utilities on January 22nd, 2008 by Bastien | 3 Comments »
ASAP Utilities version 4.2.2 (January 22, 2008) has just been released!
I would like to thank everybody for their feedback!
Among other minor updates, these utilities have been improved:
- Export » Export selected chart or range as image (file)...
- Export » Export worksheets as separate files...
- Import » Insert (delimited) text file...
- Fill » List filenames in folder...
- Fill » Quick numbering of cells...
- Numbers » Round numbers (not formatting, but changing)...
- Sheets » Add sheets (with the names defined in selected cells)...
- Sheets » Sort all sheets...
- Web » Decode all URL-encoded data
Some errors have been corrected in the following utilities:
- Count duplicates
- Formula » Change reference style (e.g. A1 to $A,1 etc.)...
- Export » Export selection or active sheet as new file...
- Numbers » Convert "textnumbers" to numbers
- Export » Export selection or active sheet as new file...
- Export » Export selected chart or range as image (file)...
- Web » Activate hyperlinks (create from cell values)
- Export » Export selection or active sheet as new file...
- Format » Copy a sheet's page and print settings...
- Fill » List filenames in folder...
- Web » Activate hyperlinks (create from cell values)
- Range » Copy formulas without changing their references
Filed under: ASAP Utilities on January 22nd, 2008 by Bastien | 3 Comments »
Filed under: ASAP Utilities on January 9th, 2008 by Bastien | 2 Comments »
I have just released the fifth pre-release of the new version of ASAP Utilities 4.2.2 which contains several new features and bug fixes.
This new version is scheduled to be finished by the end of next week (January 18). Minor updates and bug fixes will be done. Bigger updates will be reserved for the release after this. If you have any comments on this pre-release, please let me know. Thank you.
Filed under: ASAP Utilities on January 9th, 2008 by Bastien | 2 Comments »
Filed under: General on December 20th, 2007 by Bastien | Comments Off
Do you read the website "Daily Dose of Excel"? If not, it is a very interesting website about Excel. Frequently updated and many interesting discussions.
Today I want to share an article with you:
This week Dick Kusleika posted a very interesting article where he quoted Charles Williams from DecisionModels.com. Charles Williams is an absolute expert in the field of the performance of Excel models.
Recently Charles found out that Google Desktop can significantly slow down Excel:
Charles Williams - For the last few weeks I have been trying to find out why clearing a large range of cells caused Excel to hang on some PCs but not on others. This does not happen with Excel 97, but does with Excel 2000, 2002, 2003 and 2007.
It turns out that its not just Clear, but also Delete or transferring data from a variant to a range, or even just selecting a large range of cells. The further down the sheet and to the right the slower it gets. And the more recent the Excel version the slower it gets.Someone on the newsgroups discovered that, when using VBA, you could bypass this problem by switching off EnableEvents, and then someone else discovered that the culprit was Google Desktop Search.
The problem actually turns out to be the Google Desktop Office Com Addin. When you deactivate this you get a miraculous speedup.
With Excel 2007 it is fairly easy to deactivate:
Office Button–>Excel Options–>Addins->Com Addins and deselect Google Desktop Office Addin
With earlier versions of Excel you have to customise a toolbar and add the Com Addins dialog to it.
View–>Toolbars–>Customise–>Commands tab–>Tools then about halfway down you will find Com Addins, select and drag to the toolbar of your choice.
Then you can uncheck Google Desktop Office addin.
If you have multiple versions of Excel installed you only have to do this once.
Presumably this COM addin sets up one or more application-level events to monitor things like Selection Change and Worksheet change and then tries to trap the change in order to index it.If you want to measure this effect you can download a Variant Benchmark Timer from my website that allows you to run a read and write benchmark with and without EnableEvents.
DecisionModels.com/Downloads
This represents an interesting new twist in the Google-Microsoft wars!
I could reproduce the delay when I removed data/cleared cells in a (very) large dataset. However with small datasets of no more then 10.000 cells I didn't see any significant differences in speed. Removing over 1 million cells (thank you Excel 2007) however took more then several minutes with Google Desktop, compared to next-to-nothing without having it installed.
If you have any comments on this subject, please post them in the original article.
Filed under: General on December 20th, 2007 by Bastien | Comments Off
Filed under: Excel 2007 info on December 6th, 2007 by Bastien | 1 Comment »
For your information, next Tuesday the first Service Pack for Office 2007 will be released.
Service Pack 1 for the 2007 Microsoft Office system will be available for download on December 11th. We will be sharing more details at that time, but the improvements are focused on stability, performance and security.
I'm very curious if the people from Microsoft stay focusing on bugs and security holes only or if they also do something with all the suggestions and complaints in the various newsgroups and weblogs.
Office Download page:
http://office.microsoft.com/en-us/downloads/FX101321101033.aspx
Filed under: Excel 2007 info on December 6th, 2007 by Bastien | 1 Comment »
Filed under: ASAP Utilities on November 28th, 2007 by Bastien | 10 Comments »
I have just released a pre-release of the new version of ASAP Utilities which contains several extended utilities (mainly import/export) and a few bug fixes.
I would like to thank everybody for their feedback!
Read more...
Filed under: ASAP Utilities on November 28th, 2007 by Bastien | 10 Comments »
Filed under: ASAP Utilities on November 20th, 2007 by Bastien | 12 Comments »
Did you know that ASAP Utilities is sexy? I didn't too but I found out it is yesterday when I read the following review on the UK website Dooyoo! by raehippychick.
I would like to share her enthusiasm with you.
Advantages: Brilliant for any Excel user - simple, easy to use, saves time and tempers
Disadvantages: None!
In 1999 Bastien Mensink of the Netherlands did an amazingly generous thing for all Excel users. He designed an add-in utility that does those irritating jobs that Excel does not do automatically. It does it beautifully, seamlessly and with a certain panache… hence my title…
A fellow Excel addict told me about this nifty little utility a few years back. She worked at the time for HMSO and I figured if they allowed the program on their computers it had to be pretty safe. I quickly googled off and found http://www.asap-utilities.com/ had a very quick read around and downloaded the latest version of ASAP Utilities. By the end of the day the time I had spent googling and downloading the app was saved twice over by using just a couple of the labour (and brain) saving functions on offer
My favourite clever tricks are:
# Playing with rows/columns
I often need to delete blank rows between rows with data etc in after playing about with my spreadsheets and before ASAP it was a time consuming process of highlighting, deleting and swearing because I’d accidentally deleted a row with formulae in! Now I just go to the drop down menu that appears after an install and I select what I want, sit back and look smug as my spreadsheet magically becomes tidier (not sure why I look smug as I didn’t actually write any of the code, but I do feel smug anyway!)
Also available here is the ability to delete alternate odd or even rows, and my personal favourite that started my obsession off: Merging rows/columns whilst keeping all your data! Woohoo! When you merge cells/rows/columns Excel only keeps the data from only the top right cell, so to merge anything before was time consuming and often involved copying to Word and a lot fiddling. Now you just highlight what you want merged, click on the menu and choose your separator… and Robert is your mother’s brother… you have a tidy spreadsheet with no loss of data or formulae
# Doing clever things to/with ranges
The top super use here for me is to remove duplicates. I use a helpdesk database that has a unique report number, but there can be many entries on each report, each one on its own line. So all I do now to see an entire report at a glance is to sort by report number, then date highlight the report number column and remove all duplicates from the selection. Bliss!
This part also does tricky things with advanced sorting and copy and pasting with various formats, deleting names from selections and workbooks, find and replace in all sheets… ooh, and a few more too
#Numbers, text, formats and fill…
My first experience here was the joy of discovering “insert before current value”. I was creating a database of our movie collection and wanted to put a leading set of characters in front of certain films, previously this was a case of click each cell and press F2, (or double-click) and type. Now this is automated… more time saved!
This area will also help you remove any leading zeroes, carriage returns, or other specified anomalies from your cells. You can apply a formula to highlighted cell; change the absolute referencing of formulae, fill down empty cells in a selection (the reverse of deleting duplicates)
Other things I find useful are changing the name of the spreadsheet without having the hassle of doing a save-as and delete. Saving all objects on a sheet, deleting all comments, ding things with hyperlinks, listing names, ranges, and other things… the list is seemingly endless!
I can hear people muttering about price in the background there. How much will such a magnificent thing cost me you ask? I’ll tell you – nothing, that’s right, nowt, zero, zip, diddly-squot. Absolutely nothing! You can purchase this gem with support for the paltry sum of 49 dollars. You should do this if you use it commercially (the first 90 days is considered a free trial), you do not want to have to upgrade twice a year or if you feel it is worth the money. Personally I shall be paying for this in a few months as I do feel it is worth it, but until then it is wonderful to know that someone ‘out there’ cares enough about us frustrated Excel-aholics to give us a time saving program for free
If you use Excel, go and download this right now. It will save you time, reduce your stress levels and make you popular with your work! Depending on what you use Excel for you will have different favourite features that you use regularly and will bring an a happy sigh of relief to you as you remember the bad old days when it took you half a day to do what now takes you only minutes!
Let’s face it, the only thing it doesn’t do is make the coffee, but once you start using it you’ll be so popular with your boss he’ll not only be making your coffee for you he’ll be buying you buns!
Summary: Does pretty much all the niggly things you wish Excel did
This review was written on September 18, 2007 on the website Dooyoo! by raehippychick:
http://www.dooyoo.co.uk/applications/asap-utilities/1061328/
Thank you raehippychick!
Filed under: ASAP Utilities on November 20th, 2007 by Bastien | 12 Comments »
Filed under: ASAP Utilities, Video on November 9th, 2007 by Bastien | 5 Comments »
The following video will show you how to how to fix trailing minus signs in Excel by using ASAP Utilities. This can be useful if you have imported data from a system that reports negative numbers with a trailing minus.
If you've ever worked with imported numbers you might have faced the problem that negative numbers have the minus at the right of the number in stead of on the left. For example it shows 150- in stead of -150. This especially can occur if you import or download data from financial systems. With the minus sign at the right Excel will not recognize the number as a negative number and you cannot use it in your calculations.
There are a few approaches to solve this. In Excel 2002 and 2003 you can specify this in Step 3 of the Text Import Wizard when you click on the "Advanced button" (the option "Trailing minus sign for negative numbers").
You can also use a worksheet function to convert the numbers in an extra column:
=IF(RIGHT(A1,1)="-",SUBSTITUTE(A1,"-","")*-1,A1)
Another easy and fast approach is to use ASAP Utilities to fix the numbers. Just select the cells and then in the Excel menu choose ASAP Utilities » Numbers » Move "-" from back to front (e.g. "65-" to "-65")
Numbers before:
|
Numbers after: |
Filed under: ASAP Utilities, Video on November 9th, 2007 by Bastien | 5 Comments »
Filed under: ASAP Utilities, Video on November 5th, 2007 by Bastien | No Comments »
The following video will show you how to remove all empty rows in Excel by using ASAP Utilities. This can be useful if you have a large amount of data imported or copied together that you now want to clean up.
An approach to remove all empty rows can be to sort the worksheet. However if you don't want the data to be sorted/shuffled you can use ASAP Utilities to detect and remove all rows that are completely empty (have no data). In the Excel menu choose ASAP Utilities » Columns & Rows » Remove all empty rows. Besides removing all empty rows, ASAP Utilities also contains a utility for removing all empty columns.
Example with empty rows:
Same example with empty rows removed:
Filed under: ASAP Utilities, Video on November 5th, 2007 by Bastien | No Comments »
