ASAP Utilities

New version in progress: January 8, 2007

by Bastien 11 Comment

I'm working on a new version of ASAP Utilities that fixes a couple of bugs and has a few new features.

Please tell me what you think of it.

Download: ASAPUtilities_setup_4-0-6-RC1.exe

New

  • 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

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.
  • Formula » 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.

Related Posts

11 comments

sam

Dear Bastein,

Kindly consider release of 4.0.6 as pure XLA file in zip format rather than a XLA/DLL file as a setup

The mixed version is not usable in office…. I am forced to use 3.10E despite having Excel 2003

Best Regards
Sam

sam

Dear Bastien,

Here are a couple of suggestions for your utilities like Numbers to Text and Text To Numbers…etc..

Dont loop….use arrays….ofcourse you know this already… but judging by the time taken by your Num2Text and Text2Num….I think you are looping…..

Sub Text2Num()
Dim myRange
Dim lngctr As Long
myRange = Selection.Value
For lngctr = LBound(myRange) To UBound(myRange)
For c = 1 To Selection.Columns.Count
myRange(lngctr, c) = myRange(lngctr, c) * 1
Next
Next
Selection.Value = myRange

End Sub

Sub Num2Text()
Dim myRange
Dim lngctr As Long
myRange = Selection.Value
For lngctr = LBound(myRange) To UBound(myRange)
For c = 1 To Selection.Columns.Count
myRange(lngctr, c) = "'" & myRange(lngctr, c)
Next
Next
Selection.Value = myRange

End Sub
Sub TrimAll()
Resp = MsgBox("Make sure your range has only text is not filtered, has no formulas or dates, If you are not sure, press cancel", vbOKCancel)
If Resp = vbOK Then
Dim myRange
Dim lngctr As Long
myRange = Selection.Value
For lngctr = LBound(myRange) To UBound(myRange)
For c = 1 To Selection.Columns.Count

myRange(lngctr, c) = Application.WorksheetFunction.Trim(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Clean(Application.WorksheetFunction.Substitute(myRange(lngctr, c), Chr(127), Chr(7))), Chr(160), Chr(32)))
Next c
Next
Selection.Value = myRange
End If
End Sub

Kanwaljit

Hi Bastien,
May you live 1000 years. Import->List filenames in a folder gives the name of all the files (for selected file type). But what if we need the names of the worksheets present in those files too. Do consider in future versions.

Regards
Kanwaljit

Kanwaljit

Hi Bastien,
May you live 1000 years. I don't know whether it is a bug or not, but that may be unwanted in certain situations. My used data range is A1:P100. Cell P1:P100 contains 100 pictures inserted (one picture per cell). I then deleted the data in A71:P100. Then I used the option "Sheet->Remove unused empty ending rows/columns". But it deleted all the objects present in column P, which was not desired. To me column P was a used column and not empty. I don't know what the technical reason is, but it would have been a wish come to leave those Pictures intact.

Best Regards
Kanwaljit

Kanwaljit

Hi Bastien,
May you live 1000 years. Strange ! You didn't responded even to one of the comments. Seems engaged somewhere. Good luck.
Best Regards
Kanwaljit

Kanwaljit

Hi Bastien,
May you live 1000 years. Many a times we need to filter a list containing something like "Ends with ," or "begins with Mr.", where we want to delete only the First three letters or the last two letters. When we use the "Text->Delete number of leading/ending characters or Advance character removal", it deletes/removes those things on the hidden (by filter) rows too, which is not desirable. That is my wish.

Best Regards
Kanwaljit

Bastien

Hello Kanwaljit,

Thank you for your suggestions.
The spam on this weblog is a big problem and screening/approving all the comments took quite a while. I will incorporate some of your suggestions, especially the filters! I recently changed the "apply formula" utility too to exclude/include filtered data.

Hello Sam,
Although the speed is faster I choose to have better control and error-checking options when editing cells.

If things go as planned I will be able to spend more time on the further development and support of ASAP Utilities.

Kind regards,
Bastien Mensink

Kanwaljit

Hi Bastien,
Please check my comment dated 5th March. Is that a bug or point for future enhancement.

Regards
Kanwaljit

Bastien

Hello Kanwaljit,

If you have the properties of your object set to "move and size with cells" the removing of empty column will cause the objects to be resized. In you case I guess the objects became very small and therefore (nearly) invisible. I will see if I can improve this or inform you about this behaviour upfront.

You can change the way objects are moved or resized when you resize or remove columns:
Right click on the object » Format autoshape » Properties tab)

For now, the next time you should just use the "reset last cell utility":
ASAP Utilities » Sheets » Reset Excel's last cell

Kind regards,
Bastien

Kanwaljit

Hi Bastien,
I feel you are right. Because even when the empty columns were deleted the size of the file remained nearly unchanged, which means that the objects were not actually deleted. They were resized. Will look forward to the future improvements on this.

Thanks again.

Regards
Kanwaljit

Kanwaljit

Hi Bastien,
May you live 1000 years. One more wish :)
There should be one option in Text->Insert before / after current value, which allows us to
–insert after/before "n" number of characters in the text/value
–insert after/before a particular string/value

Regards
Kanwaljit