Visual Basic

Converting postcodes to longitudes and latitudes via Mappoint - Microsoft Access application

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.

Converting postcodes to longitudes and latitudes via Mappoint 2006 in VBA

The Poorhouse recently had to convert thousands of UK postcodes into their equivalent latitudes and longitudes. Much as atlases plus rulers are fun, it got tedious quickly. Luckily there was a copy of Microsoft Mappoint Europe 2006 lying around nearby, and it turns out it is pretty much super easy to do using its API.

Avoiding the 401 authorization required error with Google calendar API

Using the previously-described procedure to interact with Google Calendar's API from Visual Basic 6 led to a seemingly strange phenomena at first.

Whilst trying to add an event for the first time, during the second POST operation (which includes sending the Auth code to http://www.google.com/calendar/feeds/default/private/full) rather than the event being added, Google replied with a 401 Authorization Required error like this:

HTTP/1.1 401 Authorization required
WWW-Authenticate: GoogleLogin realm="https://www.google.com/accounts"

Using Google Calendar API via Visual Basic 6

Google Calendar is a lovely looking and acting "free online shareable calendar service", allowing you to maintain your personal schedule, share it with others, and view public calendars.

Google has also provided an API such that your programs and websites can make use of it remotely; for instance to view, add or delete events. There are special client libraries to let you use it from the Java or C# .NET programming languages. It also supports communication via standard XML, to allow any other suitable language to get involved.

Visual Studio for free

Don't know if the Poorhouse is just the last one to notice anything but it seems Microsoft are offering a cutdown but still useful version of Visual Studio 2005 and SQL Server 2005 for free, under guise of the Visual Studio Express editions.

The package includes:

  • Visual Web Developer 2005 Express Edition
  • Visual Basic 2005 Express Edition
  • Visual C# 2005 Express Edition
  • Visual C++ 2005 Express Edition
  • Visual J# 2005 Express Edition
  • SQL Server 2005 Express Edition

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.

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.

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)

When docmd.transferspreadsheet commands go wrong...

Slightly lazy VBA programmers might enjoy using the less-than-cryptically-named docmd.transferspreadsheet command when trying to automatically move information from an Access database to a spreadsheet or vice versa. It allows you to work with spreadsheet ranges and so on with minimum faffing. It also doesn't work so great and can lead to "unexpected results" when importing from a spreadsheet.

Syndicate content