Custom Number Formatting in Excel

Undoubtedly one of Excel's sneakiest features, custom formatting unlocks a whole array of possibilities for formatting where conditional formatting is either not necessary or not available, for example in a chart's axes or data labels.

Read Jon Peltier's post on the topic for a good treatise on the subject and check the Microsoft Knowledge Base page for a full list of syntax.

Posting blog articles with Word

I stumbled upon this feature in Word 2007 this morning. It seems that Microsoft and Google have agreed to play nicely for a change.

Posting from word is relatively easy - you select Blogger from the list of supported blog providers and give it your login details. Now you just type your post and hit the 'Publish' button and it will appear.

There are other options for blog providers, including Live Spaces, WordPress and SharePoint and also support for MetaWebLog and Atom APIs

Ideally I would have liked to give some screen grabs to demonstrate this but what I have found is that there is no easy way to get pictures to your blog. If you use Blogger though, you aren't able to upload pictures with your post.

It seems possible to use a third-party host such as PhotoBucket insofar as there are configuration options there to set the upload URLs but after much playing around, I haven't been able to get them working, mostly due to the fact that these sites don't allow for FTP uploading without a premium account.

Even manually uploading and getting a URL for the picture isn't helpful as Word doesn't seem to want to let you insert the picture from a weblink.

I guess I'll keep on playing with it...

Tagged / Labelled emails

I have a lot of email. A lot...

I used to file it in discrete folders inside folders inside other folders - stored away in case I should ever need it in future (and I often do). The trouble with the folders is it can take a while to find what you're looking for. In addition you eventually end up with a huge list of folders to navigate through in order to file something away.

I am talking about my work email here but the situation was closely emulated in my personal email. One day though, I got sick of the amount of spam I was receiving and decided to pack it in start afresh. I opened a shiny new gmail account and it was at this point my eyes were opened to tagged emails (called labels in gmail).

Now I have a flat archive with great search capability and things are tagged according to what they are. Ok so I still have a huge list of tags but everything is archived in the same folder so it takes no time to sweep email in there once it's categorised. Best of all, I can find things without trawling through ten folders first.

The next task was to categorise my work email and keep it up-to-date. More on that later.

Needless to say this new method (for me anyway) is more flexible and in many ways more powerful, especially when using Outlook's Search Folders.


Thanks to gmail and 43 Folders (link) where I first read about GTD and which shaped my thinking about this topic and others.

Use VBA to delete a file

This morning's annoyance was with Excel 2007. Somehow I'd created a file in the XLSTART folder (surprising as it's on a business drive to which I don't have access). To those of you that don't know, anything in this folder will be opened when excel starts.

So, my dilemma is this: How do I delete a file when I don't have access to it through Windows Explorer?

As I was in excel that got me into this mess, I figured it should be used to get out. A quick google revealed how to determine the name and path of the file I had open (link) and then how to perform basic file functions using VBA (link).

From that I put together a quick VBA macro to delete the file. I added a few message boxes along the way to check the path and then to give me the option to back out if I got cold feet at the last minute.

User names have been changed to protect the innocent.

Sub DeleteDefaultFile()

Dim Answer As String

MsgBox "Currently in " & CurDir
ChDir "C:\Users\######\AppData\Roaming\Microsoft\Excel\XLSTART\"
MsgBox "Now in " & CurDir

If Dir("C:\Users\######\AppData\Roaming\Microsoft\Excel\XLSTART\Sheet1.xls") <> "" Then
Answer = MsgBox("Delete the file?", vbYesNo)
Select Case Answer
Case vbYes
Kill "Sheet1.xls"
Case vbNo
MsgBox "User Aborted"
Exit Sub
End Select
Else: MsgBox "The file does not exist", vbOKOnly + vbExclamation
End If

End Sub

For some reason, inlining didn't copy to the blogger so it's all flat, even if I manually put the spaces in. Sorry.

A new blog...

So, this will be my third venture into the blogging fray. The principle remains the same - a place to collect problems and solutions as my ageing brain struggles to keep track of them.

With any luck they may even be picked up by search engines and broadcast to the world wide web where I may help other unfortunates in their struggles against poverty, injustice and Office 2007.

It should be pointed out now that I am not a professional (in anything) or an expert but I do know how to use google and therefore my knowledge is practically limitless. I'll try and cite references where I can and give credit where it's due...