.

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

As i use a TomTom GPS, i thought it was time to "scratch an itch" and use Excel as an editor/convertor program for the different types of POI files that i work with.
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 a corporate internet firewall and on top of that do not have the permission to install a third party program locally on their PC's.

Therefor a 'simple' local Excel workbook with 'macro's'/VBA 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.

This VBA - Workbook is tested in Excel 2007, 2010, 2013, 2016, 365 32Bit/64Bit English version.
For Mac systems i do not have a working solution as i use some 'Windows only' activeX libraries internally.

Main features

Changelog:

Bugs solved in version 5.3.0: offered for free and free download (see download link below)

Bugs solved / New features in version 5.2.6: offered for free and free download (see download link below)

Bugs solved in version 5.1.4:

New features in version 5.0:

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, 2019, 365 xlsm file is available here: Download (hotlinking to this file or to the download location is not possible)
This solution is stored in a compressed ZIP archive. Unzip and doubleclick to use/store your location lists. If your organisation does not support downloading / using external xlsm files - containing macro's - you could use it on you private computer ...

Screen shots / short explanation:

Opening

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 and to access the POI Data in the tabs.

Macros are disabled warning

Main menus

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

The location's tab is where you do the editing inside a location's tab.

Application overview

The Import/Export tab is where you import or export data in and out the current selected tab.

Application overview

Import / Export POI's

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

Importing is supported for:

  • TomTom ov2 files
  • TomTom MyDrive Export files
  • Google KML files
  • Google KMZ files
  • Google GPX files
  • Other POI Editor files (reorganizing)

The standard page column layout 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.

Location list types

Before import you can specify a table layout, or you can convert any existing layout to a different one.

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

Default Location PIO format:

Location format for track with additional elevation and time:

Location format with additional layers in the POI data:

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

Import / Export TomTom MyDrive

TomTom 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.

    TomTom MyDrive Export

    Google maps POI display

    Displaying a stored location in Google maps can be done in 3 ways

    • Doubleclick on the coördinates
    • Doubleclick on the description
    • Using the Show in Google Maps button

    If you use the lookup button in the ribbon: 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)

    Address lookup (Address->Coördinates) & Reverse address lookup (Coördinates->Address

    To set up Address Lookup and reverse address lookup, the settings menu and at least 1 API key has te be filled in.

    Settings button

    You can fill in up to 3 API keys to be able to perform address lookups. Only 1 provider can be active

    Settings: Google API key

    You can use the buttons after selecting a POI in the list. The icons will swith depenant of the chosen provider. If you did not fill in an API key the buttons turn grey.

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

    Geocoding lookup and reverse lookupGeocoding lookup and reverse lookupGeocoding lookup and reverse lookup

    If an address or a Coordinate exists you will get a confirmation warning.

    Geolocation Address Lookup warning Geolocation Address2 Lookup warning