Microsoft applications

Converting Access macros to VBA

Microsoft Access contains two main ways of automating non-trivial tasks: macros and Visual Basic for Applications. The former is an Access specific way of building up multiple commands to be run on your database in what is probably a relatively easy way if you just need a simple task doing. The latter is a programming language that you can use throughout the Microsoft Office suite and beyond. Harder to learn, but infinitely more flexible and useful. So much so that rumours had been heard that at some point macros will be phased out of Access.

Not yet though. Nonetheless, to the more conventional programmers they may be fairly cryptic and are certainly limited in scope. So what happens if you inherit an old-skool database full of macros but you want to speak VBA?

Deal or No Deal - Microsoft Excel edition

Countdown beware; there's a newish favourite daytime gameshow in town to take the shallow attention of you non nine-to-fivers: Deal or No Deal. If nothing else, at least it keeps Noel Edmonds of the cruel streets of England.

In case you've been asleep for years (being in foreign parts is no excuse - there are at least 23 national versions of the show). The premise of the game is as follows: There are a number of boxes with varying amounts of money in, from 1p to £250,000. The contestant picks one box, and it is opened to reveal what it contained, hence giving the contestant the knowledge of what is left to play for. At some point in the game, a mysterious "banker" rings up and offers the contestant a specific amount of money if they will take it and leave.

Linked queries in Access

Back to the (Microsoft) Office...Today's cunning tip is that of linking queries between two Access databases.

It is easy to link tables in Access; that is to say that your Access application can use, edit, add and delete data that physically exists in a different Access application's tables. This allows you to have multiple independent front-ends for the same background data for instance, gain the ability to develop new forms and reports whilst other people are using existing ones, or have an application that uses data from two different databases. Do this via the "Link table" option you get, when you create a new table (Click the Insert menu, then Table). However at first sight it isn't possible to do this for queries.

Word mail merge sometimes doesn't show Access queries

It is common practice to want to perform a mail merge from Microsoft Word, using data from Microsoft Access. Normally, this is extremely easy, especially if you use the Mail Merge Wizard that you can find under the "Tools" menu after selecting "Letters and Mailings" (in Word 2002 anyway). It will lead you to choose first the Access database you want to get the data from, and then the table or query you want to use.

Sometimes however when it asks you what table or query you want to use, the one you carefully handcrafted for this purpose isn't it the list. This happens mostly if it is a parameter query (a query that the user is expected to type something into to generate the data), but sometimes erm...just for fun.

Deciding what function to use at runtime in VB

Good coding practice in VBA (or Visual Basic itself) suggests that you should use functions and subroutines rather than one big long list of code. However, sometimes you may not know in advance the name of the procedure you want to call. This is a problem because you can't use a variable to call a function.

Actually, you can. Just use the "run" method. As an illustration of how it works, the following 2 bits of code do the same thing, both calling the function func1. The difference is that in the second one we didn't have to decide on the function to run before the code started running.

Faking page headers on a Microsoft Access subreport

In a tedious Microsoft Access report featuring pages and pages of meaningless numbers in a table you quite often want the table headings (the bit labelling the column) to repeat itself at the top of each page. That way the recipient has no excuse for not knowing what a hundred pages of mind-bogglingly dull numbers mean.

Normally Access provides a Page Header section of the report for exactly this purpose. If you can't see it then in the report design view, go to the View menu and select "Page Header/Footer". Whatever you write in this section will appear at the top of each page.

Paint possibilities

Powerdraw: 500 hours of someone's lifePowerdraw: 500 hours of someone's lifePaint. The sheer straightforward simplicity of its name belies the incredible power of that program sitting in the Accessories bit of your Start menu (Microsoft Windows users only). You know, the one almost like Photoshop, but cheaper. The one part of Windows that never crashes. There's the Poorhouse thinking it was only useful for erm...."basic diagrams" such as a picture of a circle, an artist's impression of a square or an extremely bad version of your signature done in the style of a 1980s-computery spray can.

But no, the wonderful illustration accompanying this article was reputedly, believe it or not, done in Paint by the artist known as Diamonster. A tiny amount of creative accounting occurred via the use of a Photoshop blur effect at the end, but otherwise apparently it was pieced together pixel-by-pixel in MS Paint. To be fair it did take over five hundred hours. Time well spent. You can see a couple of the interim steps here and here.

Winmail.dat annoyances

Ever received a mostly-blank email that contained an attachment called "winmail.dat"? Or maybe you sent one and cursed the foolish recipient for gibbering on about some winmail.dat file when you know you didn't attach such a thing.

If so, no hallucinations necessarily took part in that experience. It's another treat from the Microsoft guys. On occasions Microsoft Outlook will try and send the message all nice and pretty in some weird-ass Microsoft Transport Neutral Encapsulation Format (MS-TNEF) that not much else understands. This is an attempt to convey formatting information such as fonts, colours and all the rest of the modern jiggery-pokery that largely is a distraction from the actual content of the email. If the recipient's program doesn't understand what MS-TNEF is (i.e. it isn't Outlook) that the formatted version comes as an attachment called winmail.dat. No big deal - except that if you have deliberately attached a file to the email (e.g. a PDF file, a Word file and so on) then that is also hidden inside the MS-TNEF winmail.dat file and as such any programs that don't do the MS-TNEF malarkey can't even see the attached file, let alone open it.

docmd.transfertext doesn't work so well either

Undeterred by the previous significantly buggy annoyance of docmd.transferspreadsheet, the Poorhouse was commanded to transfer some more facts 'n' figures; this time in the ultimate old-skool format, a flat text file. The info within was to be featured in a Microsoft Access table.

In order to transfer data from a text file - for example a comma separated values (csv) file or a fixed length file – Access VBA includes the command TransferText. The syntax is as follows:
TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

Mission (im)possible: Calculating the difference between two dates in Microsoft Excel

It came upon the midnight clear
That glorious song of old
I want to calculate the difference between 2 dates in Excel
Or be left out in the cold.

Yes, the Poorhouse was confronted with the above situation; namely that given a date1 and a date2 in Microsoft Excel we needed to know the difference. Not the difference in terms of random Excel microseconds since 2nd August 1957 type numbers, but an actual, readable "3 years 2 months" type difference.

Perhaps this doesn’t seem like quite the craziest of plans; surely people are forever wanting to calculate someone's age, length of service and so on? But as far as Mr Excel is concerned it is a task as arcane and bewildering as the search for the Holy Grail.

Syndicate content