When geographically mapping data, for business or (unlikely) pleasure, a common file format used is the ESRI “shapefile”, typically recognised by ending with .shp. This file format allows one to specify custom polygons, with associated data. For instance, the counties of the United Kingdon, the territories of your business, or the areas covered by sales reps - anything effectively 2-dimensional. Alongisde the polygon spec you can store data – an obvious example being the name of the county in case 1 above.

What is less joyous though is generally the packages that handle these files with any form of style and grace are not free, or even cheap. There are some freeish viewers of various quality out there but if you wish to automate any sort of activity on these often gigantic files then usually it doesn’t come cheap. However, the Poorhouse did discover a free GIS program called MapWindow which is probably not a bad program in itself, but most usefully it contains a free GIS ActiveX control complete with API. This means you can link to it with any application that can interact programmatically with such things, not least the Microsoft Office applications suite.

It has been a particularly geeky day for the Poorhouse today as it happens. Ugh. So let's celebrate with the latest of his Excel discoveries designed to allow an optimum amount of user laziness in between the awful clicking between cells, formulae that make sense only to Stephen Hawking and charts formatted so horrifically that it must have been intentional.

This one's a macro to solve the insanely annoying "Number stored as text" error, which usually occurs when copying and pasting data from elsewhere, or when an idiotic, deserves-to-be-spat-on colleague sends you something that is either exhibiting a remarkable level of stupidity or a deliberate wind-up.

The Poorhouse's discovery of the day: DocTranslate - A heavenly, and free, treat for those of us poor info-workers who deal with several countries, not all of which are polite enough to speak English as their first language.

Using the mighty power of Google Translate, this downloadable program takes as input a Microsoft Word or Powerpoint document and translates it to/from any combination of languages that Google knows. It comes out with some oddities due to the limitations of computer translation at the best of times (although if you’re good at languages you can apparently improve it further), but even so it rather beats one’s random guesses at what any of a particularly dull looking document might mean. Thank you, thank you, thank you.

Joys! Another post on the intricate un-wonders of Microsoft Excel. As all those who are unlucky enough to be Excel fiends know, there is enough about it that is a right royal pain in the ass. Anything that can make working with it a little easier has surely got to be welcome. Especially if it's free, given it's not always easy to get employers to actually give you anything involving £, and it feels morally wrong to make a personal investment into the mysterious lands of numbers in squares.

With that in mind, here's the Poorhouse's current top 3 free downloadable addons for others who spend double-digit hours a day inside this most gridular of programs. All guaranteed working for at least Excel 2003, and probably other versions too.

The Poorhouse spends increasing amounts of his time with his head buried in the pile of small grey rectanges that is Microsoft Excel. This mind-boggling time is sometimes more than it really needs to be due to strangely lacking features in this program of a billion obscure functions. Take for example the variable width column chart (aka as the a start towards a matrix or Marimekko chart).

One use of such a beast is to represent 2 dimensions of data within a simple bar format - the height of the bars is the classic key dimension, and the width is another. Below is an example of this in practice. This made-up chart shows the amount of income generated by sales of cars by the paint colour of the car on the Y-axis, and the quantity of cars sold itself is reflected in the width of the bar. The wider the bar, the more cars were sold. Blue cars clearly generated the most income - and the wide width of the bar shows that this could be because the largest proportion of cars sold were blue (in the real world of course the chart would be appropriately captioned...). [edit: this isn't a great example from the point of view of chart clarity, see the comments for why - but the principle of how to make the chart stands!]

Now, this isn't a built-in Excel chart type sadly and it's a faff to do, so below is a macro to 1-click do it for you.

...and here's another geeky one. Targeted at a even more niche audience, the Poorhouse imagines.

Anyone who has already experienced the "joy" of controlling Microsoft Publisher from another application via Visual Basic for Applications (VBA) may have noticed quite a bit of trial and error seems to be needed, unless that's just the way the Poorhouse codes. Which it is. Anyway, moving on...after an application was upgraded from Publisher XP to 2003 it suddenly stopped working.

Whenever the calling application (an Access database as it happens) tried to "autofit" - that is enlarge or reduce the size of text so it perfectly fits into a surrounding text-box using the "best fit" method - the following useful-as-ever Microsoft error message flashed up and crashed the program.

Method "autofittext" of object textframe failed
Run-time error -2147467259

Update frequency around here is not impressive, the Poorhouse knows. He's mainly been trapped behind a sea of Excel for a few millenia so it feels. In which case, let's liven things up, by discussing...Excel.

Whoop. Yep, here's a handy tip of something used in recent days. Not original or mindblowing but handy if you don't already know it.

No spreadsheet is complete without a few formulae to manipulate those magic numbers. A formula usually looks like "=A1 + A2" to add the contents of cells A1 and A2. But what if you don't know, at the point of writing your spreadsheet, exactly what cell you want to use? Rather, you want the cell used to vary depending on what has been input in another cell. There are a few different ways, but a quick, easy and perhaps dirty - albeit VBA free - one is utilising the wonders of INDIRECT.

For some time, you lovely commenters might have noticed you're apparently all spammers. Every word dropped out seems to trigger outrageous spam accusations upon the site and remains unpublished until the Poorhouse gets round to going through them all.

This is most unsatisfactory all round, so as of now a little bitty upgrade has taken place in the hope that at least some comments are allowed to be published sans your generous host's intervention. Chat away, and we'll see if it works or not.

Using the techniques mentioned on a previous article regarding converting postcodes (well, actually full addresses if required) to latitude and longitude via VBA, the Poorhouse conjured up a Microsoft Access application to do this en masse.

The Poorhouse spends a lot of time looking at dull grey grids of numbers. It's not a hobby per se, but it happens. As per pretty much any other normal business, these numbers appear a lot in Microsoft Excel where hours upon hours of top fun can be had moving them around a bit until they sort of hint at some sort of conclusion that makes you look good. But between staring at these dire digits comes time to prepare for staring at dire digits, which leads to magical shortcuts being discovered.