A distraction from the usual VBA code

As long as I’ve been developing macros in Word, I’ve also kept my toes warm by dipping into the odd bit of PowerPoint and Excel macro development.

The last few days have been interesting as there was a request for an Excel-based solution to import a set of data from Trello (exported via a Chrome plugin in Excel format). I then had to rejig the data, remove unwanted columns and split apart others. This then resulted in data that barely looked like the original data, but that was on purpose :)

The final stage was to loop through the ~250 Trello cards (in their slimmed down format) and export each bit of data into a Word document. That would have been fairly simple, but the difficulty was increased by the requirement for certain cells to act as headings, whilst ensuring everything else was in a normal font.

I got there in the end, with a few false starts here and there. It’s another ‘feather in the cap’ and I’ve learned more aspects of VBA.

I can now go back to tweaking v1.84j so that it’s ready, all being well, for the release of v1.85 before the end of the year.

New beta available for testing (v1.84i)

Here’s something that I’ve not done before – handing out a public beta of the toolkit before the final version is available.

I’ve added quite a lot of new stuff and I’d like some feedback, so grab it from the link below and copy it over the top of your existing v1.83.

You will need to update your configuration but only if you want to use / detect multiple languages in your Word documents.

Recent fixes (apart from the multiple languages previously mentioned) are:

  1. Fixing another Office 2010 annoyance where the conditional code check had disappeared for some reason. It’s back in there now.
  2. Fixing a problem in the Fix Fields routine where it didn’t recognise Index Entries as a separate type of field. I had assumed that this type of field had a ‘Result’ parameter – but it didn’t and it broke the routine. This was found in a test document sent to me by David Green and had been generated by MadCap Flare.


Download the latest beta (v1.84i).

Trello board

My Trello board is the best place to raise any bugs and request features.

The ‘Fix English UK’ function is now ‘Fix Language’

I had a bit of a brainstorm session on the Toolbox yesterday and this morning: the end result is that the old Fix English UK function is no more. It has been re-engineered as Fix Language and can now support 36 different languages.

Whilst this sounds a lot, it’s more down to how Word treats the language handling. It classes UK, US, New Zealand, South African and Australian types of English as separate languages, so this has bulked up the amount recognised.

According to Microsoft there are 205 recognised language types available. It makes my 36 look like a very small selection :)

However, it’s not all 100% working as I’d like. The language selection isn’t working with any selected areas as it currently only works with the whole document. More investigation work is needed before I can nail down why this isn’t quite working as planned.

Use of the function

This is all controlled by a drop-down box on the Configuration form.  Pick the language, click the Set button and, until you change the language to another, the Fix Language function will apply your chosen language to all the elements that it can find in your document.

I’ve also added some extra elements to the search. These are linked to my other new feature, the Image/Shape Browser. I noticed that you could set the language of the text frames, comments and call-out boxes. It seemed daft not to include them in the Fix Language function, so they’re included now.

The above tweak will allow anyone who uses Adobe PDF Writer to convert PDF files into Word documents to remove the default US English setting from their converted document.

Supported languages

So the next version of the toolbox will recognise and tweak your document to use the following language types:

English: Australian, Canadian, Irish, New Zealand, South African, United Kingdom and United States

European:  Basque, Belgian Dutch, Belgian French, Catalan, Croatian, Czech, Danish, Dutch, Estonian, Finnish, French, Gaelic (Irish), Gaelic (Scottish), German, German (Austrian), Greek, Hungarian, Icelandic, Italian, Latvian, Lithuanian, Portuguese, Spanish, Swedish, Swedish (Finnish), Swiss (French), Swiss (German), Swiss (Italian) and Welsh.

My Toolbox is now used worldwide (*)

(*) Well, a slight exaggeration as this is based purely on the number of nationalities that either asked for a copy of it and/or were given a copy of it :)

From the top of my head, mixed with some memories here and there, I handed out my Word Toolbox to people from the following nations:

America, Netherlands, Ireland, Germany, Denmark, Finland, Italy and (I think .. ) Belgium – but I could have read their flag image incorrectly :)

The next feature (after the Images/Shape Browser) will need to be the upgrade of ‘Fix UK English’ to become ‘Fix <your language>’.