Ordnance Survey FAQ and Excel Mapping Routines

Migrating VBA/Office code to MAC

 

Background

This is an outline of some of the challenges encountered in porting an Excel/VBA application, OSGB,  from Windows to MAC.

The OSGB application imports gpx (cartography) files into Excel, compresses GPX data, exports gpx files for plotting on map applications, and exports kml files for plotting with Google Earth.  It performs conversions and calculations on the British Ordnance Survey mapping grid, and also accesses the Streetmap web page to translate UK postcodes to position coordinates.    See http://www.haroldstreet.org.uk/osgb/

It is issued as an addin which includes the main routines, and an Excel workbook which performs installation of the addin and gives examples of the functions.

Parts of it were originally developed in Visual Basic in the 1990s, were further developed under Excel 2003, then enhanced to use the ribbon interface of Excel 2007+ and Excel 2016 on Windows.

More recently, thanks to excellent cooperation with Ian Baines, it has been enhanced to operate on MAC with Excel 2016 and 2019.  I would not like to minimise the challenge of this task or Ian’s dedication in debugging code remotely.  More than 90 versions of the code or testbed programs were tried over a period of six months before we were satisfied that we had robust code.  Trial and error were sometimes our only tools due to major omissions, errors and half-truths in the documentation!    My son has helped with testing on Office 365. 

This page outlines some of the major challenges of this enhancement.

Which Version?

The vba construct Application.Version gives the version number.  Versions tried were:

MAC Office 2016.  As originally installed it gave version 15.32 which needed a (free) upgrade to take it to 16.16.  Without that, very basic functions such as Application.UserLibraryPath failed.

MAC Office 2019 version 16.43 which seems to be stable.

MAC Office 365 version 16.41.

What Worked?

The code for creating the ribbon interface was fine, as was all the code to manipulate and convert coordinates, compute distances, calculate magnetic variation, to create user forms, the undo functionality, to perform both recursive and iterative routines and to manipulate cells. 

All this worked without change which was both surprising and impressive!  However, joy turned to despair once file access was attempted.   Issues needing care or a major rewrite included:

Differences in the format of filenames. 

Path separators for MAC are / rather than \ of Windows.  Very easily accommodated.  Use Application.PathSeparator.

Spaces in filenames

Spaces in filenames are an issue for shell commands under both Windows and MAC and in applescript functions.  Either ‘wrap’ them in chr(34) characters or, in the case of MAC, prefix the spaces with the backslash character \.

How to install the add-in?

With XP + Excel 2003 – copy it to Application.UserLibraryPath.  No further action is needed.

For Excel 2007 or later, whether Windows 7, Windows 10 or MAC you need to also mark it as ‘installed’ in registry like this:

Dim ai As Excel.AddIn

Dim DestFile as string

DestFile= Application.UserLibraryPath & Application.PathSeparator &  “osgb.xlam”

Set ai = AddIns.Add(Filename:=DestFile)

ai.Installed = True

Note that with MAC + Excel 2016 version 15.32  Application.UserLibraryPath gives a compilation error.    Upgrade Excel to 16.16.

GetOpenFileName

This does not support the FileFilter parameter so file type checking will need to be done after the user has selected a file.  Apart from that, it appears to work provided you meet sandbox requirements.

GetSaveAsFileName.

Similarly, this does not support FileFilter.   However, far more seriously, we found GetSaveAsFileName to be quite unstable and caused Excel to freeze and lock-up.   It would seem to work first time but ‘poison’ Excel in the process and freeze on subsequent calls.

Choosing Files:   Applescript Solution

The solution to stability in choosing files was to drop down into applescript.  It needed a file in folder:      ~/Library/Application Scripts/com.microsoft.Excel/  (but note the pesky space in that folder path!)

The applescript construct choose file allows a file to be chosen for reading, and choose file name to choose one for writing so we simply used an applescript function (event?) for each type of file required.   It is typically called like this:

fname = AppleScriptTask(“osgb.applescript”, “ChooseGPXread,””)

but you will need to check the response that the right file suffix has been supplied.

Choosing a file in this way seems to avoid all the instability problems.

Placing the file in    ~/Library/Application Scripts/com.microsoft.Excel/ osgb.applescript proved challenging and we found no way of doing this directly from vba.  Our solution was to place the applescript file initially in a benign location (we used Application.ThisWorkbook.Path) then ask the user to invoke the file manually from Finder, whereupon the copy command at the start of the script did the job for us.

The contents of the applescript file is environment dependent as it includes the username and its own location so needs creating dynamically during installation.  The username can be extracted by splitting  Application.UserLibraryPath.

If you copy/paste this please beware of any line splitting by Word, particularly the shell copy.

The full file, osgb.applescript is:

do shell script “mkdir -p ‘/Users/<username>/Library/Application Scripts/com.microsoft.Excel ‘”

do shell script “cp ‘<the temporary location>/ osgb.applescript’  ‘/Users/<username>/Library/Application Scripts/com.microsoft.Excel/osgb.applescript’'”

display dialog “Please save the examples workbook to complete installation.” buttons {“ok”}

return

on Present()

return “present”

end Present

on ChooseGPXread()

set theDocument to ((choose file with prompt “Please select a gpx file” of type {“gpx”}) as string)

return POSIX path of theDocument

end ChooseGPXread

on ChooseGPXwrite()

set theDocument to ((choose file name with prompt “Please specify a .gpx file for writing”)  as string)

return POSIX path of theDocument

end ChooseGPXwrite

on ChooseKMLwrite()

set theDocument to ((choose file name with prompt “Please specify a .kml file for writing”) as string)

return POSIX path of theDocument

end ChooseKMLwrite

on ChoosePMwrite()

set theDocument to ((choose file name with prompt “Please specify a postmortem file (.pm) for writing”) as string)

return POSIX path of theDocument

end ChoosePMwrite

on osgbopen(f)

tell application “Finder” to open f as POSIX file

return 1

end osgbopen

The routine present allows you to check whether the applescript file needs to be supplied.  Eg:

Function AppleScriptNeeded() As Boolean

#If Mac Then

    On Error GoTo errh

    AppleScriptNeeded = False

    If (AppleScriptTask(“osgb.applescript”, “Present”, “”) <> “present”) Then

                AppleScriptNeeded = True

    End if

    Exit Function

errh:

    AppleScriptNeeded = True

#Else

    AppleScriptNeeded = False

#End If

End Function

The Office 365 system we tried this on did not have the applescript folder created during office installation, hence the initial mkdir in the applescript file.  This did not work initially – the write functions froze the system, but a month or so later it did work.  We believe that an intervening system restart fixed it.

Sandbox Implications

Excel 2016 onwards is run on MAC within a ‘sandbox’ which requires permissions to be granted for folder and file accesses outside the sandbox.   The Excel routine GrantAcesstoMultipleFiles can be used to ask the user for permissions.

Before opening any file, we adopted the strategy of issuing GrantAcesstoMultipleFiles for the folder holding the file and then, if the file exists, for that too. That is in in addition to the special measures which were needed to choose files.

We wonder whether there is a chicken and egg situation with GetFileName and GetSaveasFileName.  If you do not have permissions to access the folder then how can you read it in order to choose a file?

Our solution to choosing, writing to then opening a file (eg a kml file) was:

  • Choose file with applescript function ChooseKMLWrite.
  • Check file type.
  • Grant permissions on folder
  • Grant permissions on file (If it exists)
  • Open and write to the file
  • Grant permissions on the file
  • Open the file with the system dependent application with the applescript function  osgbopen.

Note that these are sufficient but perhaps not necessary steps.

Application.IsSandboxed

There is a vba function Application.IsSandboxed which sounds to be very useful.  Unfortunately, it isn’t!  

  1. It does not work – it does not return ‘true’ with Excel 2019/365 as one would expect
  2. Even if it did work, it would not be useful – it needs to be a compile time directive to exclude GrantAcesstoMultipleFiles calls in a non sandboxed environment

You might try:

       #if MAC_OFFICE_VERSION >=16 then

               GrantAcesstoMultipleFiles …….

      #end if

Reading a URL

We (ab)use the Streetmap web site to ‘scrape’ the location of postcodes in GB and this needed a different approach on MAC.  See the brilliant execShell() function courtesy of Robert Knight via StackOverflow:

https://stackoverflow.com/questions/15981960/how-do-i-issue-an-http-get-from-excel-vba-for-mac

Open a file with its native application:

Once we have written a gps or a kml file we ask the user whether it should be opened – typically with a mapping application like MemoryMap, Garmin Basecamp or Google Earth.

With windows we used:

Reply=shell(“explorer “ & chr(34) & filename & chr(34))

On MAC we tried:

Reply=shell(“open “ & chr(34) & filename & chr(34))

That was fine with Excel 2019 but not Excel 2016.  Instead, we had to delve down into the Applescript routine osgbopen.

Reply= AppleScriptTask(“osgb.applescript”, “osgbopen”,  chr(34) & filename & chr(34))

The chr(34) is necessary in case there are spaces in the filename.

References

http://www.haroldstreet.org.uk/osgb/

https://docs.microsoft.com/en-us/office/vba/api/overview/office-mac

https://macadmins.software/docs/UserContentIn2016.pdf

http://www.rondebruin.nl/mac/mac034.htm

https://warwick.ac.uk/fac/sci/systemsbiology/staff/dyer/software/excelvbafileopen/

https://forum.latenightsw.com/t/debugging-an-applescript-handler-in-vba/2705

http://youpresent.co.uk/developing-installers-for-office-mac-2016-application-add-ins/

http://stackoverflow.com/questions/6136798/vba-shell-function-in-office-2011-for-mac

https://stackoverflow.com/questions/15981960/how-do-i-issue-an-http-get-from-excel-vba-for-mac

Thanks

Thanks to Phil Newby for his generosity in hosting this content, to Ian Baines for his assistance in debugging on the MAC and my son for testing it with Office 365.

Contact

You can contact me with my first name dot second name on my UK Hotmail account to discuss the above.  No spam please!

Phil Brady 15 Dec 2020.

Phil Brady © 2020