Posts for 03/2010

30 March 2010 - 13:42

How to find (the first) capital(s) in an Excel cell?

Another interesting topic coming up through one of my clients.

I've found an enlightened discussion at one of these great Excel resource sites: http://www.dailydoseofexcel.com.
Especially the breakdown of the described first function and the elaboration on arrays is worth reading.

These are the two takes I've been going for:

{=MIN(FIND(CHAR(ROW(INDIRECT("65:90")));A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))}

The trick consists in finding the ASCII value of the first capital of a text-string composed out of the cell containing the evaluated text value combined with the 26 capitals of the Alphabet.

And then also a user defined function in VBA:

Function FirstCap(Cell As Range)
   For FirstCap = 1 To Len(Cell.Value)
      If Mid(Cell.Value, FirstCap, 1) Like "[A-Z]" Then Exit For
   Next FirstCap
End Function
' And =IF(firstcap(A1)>LEN(A1),NA(),firstcap(A1))
(1) Comments ·
26 March 2010 - 17:22

Corruptions during Access development

During a quite substantial development project in Access recently, I initially started developing in Access 2007. But as downgrading to the Access 2003 version the client used posed all kind of issues, I gave up and continued the development in Access 2003.

So far so good - well I thought, but not till rapidly ran into something I had never experienced. All kind of forms suddenly didn't want to load anymore as they became corrupted.
Randomly, apparently, although frequently linked to removing labels or moving fields with their labels.
And mostly with the laconic error message: "there isn't enough memory to perform this operation" .
It became really a desperating situation.... grrr :-(

I then found a message on http://www.access-programmers.co.uk/ which really saved me and pointed me to a method to save a form as a text file (!!!) and then retrieve it again... Although it didn't always work, this and regular (obsessional ;-) ) backups made me swim across this river.

The trick goes as follows. In the VIsual Basic Immediate Windows, use following code to save the form as a text file:
Application.SaveAsText acForm, "YourFormNameHere", "C:\Path\Filename.txt"
Then delete your corrupted form and retrieve it back using following code:
Application.LoadFromText acForm, "NewFormNameHere", "C:\Path\Filename.txt"

If the issue still continues, try doing a Compact and Repair before retrieving the form.
And a further comment on the forum pointing to a possible use of this technique:

If you have to provide documentation for your application you can use this technique to create text files that contain all the information about your forms. Then should anyone modify your form at any point or it becomes corrupt you can restore it from the text file.
You can be really clever and create a support database that would be similar to the MSysObjects table that contains a list of all your objects and store the contents of the text files into a memo field.

To close: you'll find a database corruption repair guide at everythingaccess.com
And some good tips to prevent corruption at the famous Allen Browns corner:http://www.allenbrowne.com/ser-25.html

(0) Comments ·
24 March 2010 - 17:25

Find the cell with the lowest value in an Excel sheet

The beauty of Excel is that there are many ways to achieve the same thing.
Take this topic e.g. : how to find the cell with the lowest value in an Excel sheet?

If you're after the exact location of that cell, and you can limit your search to a specific column or row with results, you could use a combination of the ADDRESS() and the MATCH() function which would give you something like:

=ADDRESS(MATCH(MIN(A:A);A:A;0);1;4;1)
=ADDRESS(2;MATCH(MIN(A2:Z2);A2:Z2;0);4)

Or you might be more picky as to which type of information you'd like to withdraw from that cell containing the minimum value -
it could be the range address, but also it's content, or it's column position, it's formatting or it's locked status. That's something you can do with the CELL() function -
it's first argument determines the type of info you'll get as a result. With our two examples:

to obtain the cell addresses::
=CELL("address";INDEX(A1:J2161;MATCH(MIN(A:A);A:A;0);1))
=CELL("address";INDEX(A1:J2161;2;MATCH(MIN(A2:Z2);A2:Z2;0)))

to obtain the cell values::
=CELL("contents";INDEX(A1:J2161;MATCH(MIN(A:A);A:A;0);1))
=CELL("address";INDEX(A1:J2161;2;MATCH(MIN(A2:Z2);A2:Z2;0)))

to obtain the cell lock status::
=CELL("protect";INDEX(A1:J2161;MATCH(MIN(A:A);A:A;0);1))
=CELL("protect";INDEX(A1:J2161;2;MATCH(MIN(A2:Z2);A2:Z2;0)))

If you need this approach regularly, you could also take the VBA road and insert a module with your own user defined function.
Like the one here found at http://www.excelforum.com :

Function MinCellRef(dsRange As Range)
Dim c As Range
Dim MinCell As Single

MinCell = Application.WorksheetFunction.Min(dsRange)

For Each c In dsRange
If c.Value = MinCell Then
MinCellRef = c.Address
Exit Function
End If
Next c
End Function

And if you want some VBA solution which highlights and formats the cell containing the minimum value, that's possible too : http://excelvbamacro.com

(0) Comments ·
20 March 2010 - 13:14

How to download flash from a web site

You can download flash videos from a website either with dedicated tools for that purpose or by going into the source code and tracking the path allowing you to then manually download the flash file e.g.
Obviously the latter requires a bit more technical experience. And even if you have that, it can still be a time consuming job, as many flash files are often encapsulated in all kind of codings.

A good place to start probably is wikiHow: http://www.wikihow.com/Save-Flash-Animation-from-Website .
In terms of the dedicated tools I've personally good experiences with Replay Media Catcher - amongst others I've tried are Fast Video Download, a Firefox plugin and Flash Saver (http://www.flash-screen.com/flashsaver/).

On the coding side: you'll find a very good description and approach with Firefox, free but a bit more technical, on following website: http://geeklad.com/download-flash-video-from-any-website

ps: Regarding Fast Video Download: although it's a great tool, you might run into the problem of dialog boxes starting to open extremely slow: http://support.mozilla.com/nl/forum/1/623540

(0) Comments ·
15 March 2010 - 17:10

Autosave in Excel 2007

For those who are searching the specific AutoSave feature in Excel: search no longer - it is really gone.
Well yes it was replaced by the AutoRecover feature, but it's behavior is sligthly different.

You'll need to use an add-in to get exactly what you had before, i.e. possiblity to autosave your Excel file at a specific interval on a specific place (under a specific name ?).
I've found one here: http://www.jkp-ads.com/download.asp

(0) Comments ·
10 March 2010 - 17:31

Templates location configuration in office 2007

Template location properties

If you're using a lot of templates in Office, it's a good idea to organize things a bit. This means changing the default template location and putting your templates in subfolders.
Because if you don't specify a specific location for your templates yourself, Office saves them in a very hard to tackle place in the profile folder of the user.
And if you have created sub-folders in the main template folder, Office is then going to show or represent these as separate tabs in the template dialog window.

In Office 2007 this still happens as in the older versions of the suite: rather mysteriously that is !
Because you have to go into the Word options to find the locations button - and only there ! - as it's unavailable in Excel or Powerpoint ...
So just go to the Advanced Options in the Word options dialog window, at the very bottom, you'll find the button File Locations giving you access to the default folders for the User Templates, as well as the Work group templates. Just choose the parent folder for you templates and presto.

Template location dialog box

All you have to do next is saving your Excel, Powerpoint or Word templates in this folder, or in sub-folders of this folder, and they will then automatically appear in the Template window.

(0) Comments ·
03 March 2010 - 14:57

Mailmerge with groups

Ever wondered or needed to do a mailmerge in MS Word with multiple group levels?
It is possible indeed; it involves customizing the merge fields a bit, but if that's what you need, you'll find the whole procedure through following links:

(0) Comments ·
Page 1 of 1 pages