.

Excel as a TomTom POI - Points Of Interest (ov2) file editor / converter

As i use a Tom Tom GPS a lot, i thought it was time to "scratch an itch" and use Excel as an editor/convertor program for the different types of POI files.
There are of course enough online solutions available and also series of installable Windows programs, but as a lot of Office workers are blocked by an internet firewall and also do not have the permission to install a third party programlocally on their PC's.

Therefor a 'simple' local Excel workbook with 'macro's' could be a solution to do some quick editing and exporting to manage these specific POI files directly in Excel.
As the TomTom POI files (ov2) are encoded as a binary format, you cannot simply use Notepad for this task. Even file formats like KML and KMZ are not easily processed using Excel only.

This VBA - Workbook is tested in Excel 2007, 2010, 2013 and 2016 32Bit (still testing for 64Bit) Windows 7/8.1/10 English version.
For Mac systems i do not have a working solution as i use Windows only activeX libraries.

Main features

Changelog:

New features in version 5.0: offered for free and free download (see link below)

Bugs solved, new features in version 4.43:

Bugs solved in version 4.40:

Bugs solved & new functionalities in version 4.30:

Bugs solved & new functionalities in version 4.20:

Bugs solved in version 4.11:

Added capabilities in version 4.00:

Capabilities of version 3.00:

Possible developments in the future, if time permits ...

The download of this Ms Excel 2007, 2010, 2013, 2016 xlsm file is available here: Download (hotlinking to this file / download location is not possible)
This solution is stored in a compressed ZIP archive.

Screen shots / short explanation:

Macros are disabled warning

If you open this Excel Workbook tool for the first time, you'll see this screen.

It's displaying the security message that pop's up every time you open up a new or unknown Excel Workbook containing 'Macro's'

If you click on 'Enable Content', you'll have the possibility to add this Workbook to the list of Trusted documents.

After this step the POI editor will be working normally, and will open normally, without displaying the warning as in the next screen.

This is needed to use all the buttons and functionality of this file.

Application overview

Application overview

The empty Excel POI editor Workbook is opened normally (if Macro's/ VBA are activated)

There is one empty "Locations" sheet already available where the imported locations are inserted or where you can manually insert or copy/paste your location data.

On top you have two tabs: the Location Tab and the Import - Export Tab

Import possibilities

If you import an ov2 file from your TomTom GPS, the results are always appended in the current Excel table. The loaded location records are ADDED below existing locations.

Importing is supported from:

  • TomTom ov2 files
  • TomTom MyDrive Export
  • Google KML files
  • Google KMZ files
  • Google GPX files

The standard columns are Longitude, Latitude, Description. But this can be changed.

In the description you can further specify a telephone number by using the '>' separator inside the description.

You can also use the 'Add Telephone Suffix' button on the selected row.

Exporting is supported for the following formats:

  • OV2 for TomTom GPS
  • CSV Text file for Garmin GPS systems and other compatible systems.
  • KML Text file for interaction with Google Maps
  • GPX format for Garmin GPS systems and others.

Before import you can specify a table layout

  • Location: with trackname, longtitude, lattitude and description columns
  • Tracks: with trackname, longtitude, lattitude, elevation and time columns
  • Layers: with Layername, longtitude, lattitude and description columns

You can add a table type OR change a tabletype afterwards.

After import, changing to another table type is possible but will NOT display the data that was not imported due to other columns.

Each page type will have it's own colour

  • Location: RED
  • Layers: BLUE
  • Tracks: GREEN

TomTom MyDrive Export

MyDrive data is available after you log in om the TomTom site

  • Go to the settings (gear/cog icon on the bottom)
  • Select MyDrive tab on top
  • Click on "Download your MyDrive data"
  • This will start the download of your MyDrive data, containing your Favorite locations you have created.

    A double click on a row cell will open your favorite browser on Google maps and it will pinpoint the exact location of your Point of interest. (POI)

    You can also use the button: Show Selected Row in Google Maps.

    The type of map that is opening is defined by the choice in the Excel Ribbon:

    • Normal (Google Maps)
    • Earth (Google Street view)

    Different tabs possible to load multiple location files

    It's possible to add new Sheets to you Excel Spreadsheet. This sheet can be filled with another import or another set of POI's.

    Each sheet is exported separately to another file.

    Adding, Deleting and Clearing a sheet can is done with the buttons in the Table group on the ribbon.

    Each page type will have it's own colour

    • Location: RED
    • Layers: BLUE
    • Tracks: GREEN

    Settings button go to settings

    Settings: Google API key fill in your google geolocation API key

    Geocoding lookup and reverse lookup use the buttons after selecting a POI

    Geolocation Address Looku^p

    For all geocoding functionalities you have to get a google geocoding API key and insert this in the settings.

    This is needed is because the tool is using these google services to do the lookup for you.

    After that you can lookp the lattitude and longtitude of an address or do a reverse lookup by looking up the address of a longtitude and lattitude location point.

    Adresses must be inserted in the Description column eand they must be between [ ].

    Also note that with a free API key you will be limited to 2500 lokups per day.

    If you have a longtitude or langtitude you can reverse lookup this address