In this article, Eric Frost shows how maps created with Microsoft MapPoint 2010 can be automatically published to the web and displayed on mobile devices.

This is a fairly simple Excel macro which automatically runs when the macro workbook is opened and creates maps and copies the graphics via FTP to a web server. The images are sized to 800 x 480 which happens to be the dimensions of my HTC EVO phone. If publishing to another device such as an iPad (1024 by 768 ) you could modify the dimensions in the Excel macro.



This project consists of four files. One macro-enabled Excel workbook (.xlsm), one normal data workbook (.xlsx), and two MapPoint .ptm files. For whatever reason, MapPoint does not recognize nor is able to import directly from .xlsm files, thus the need for a separate Excel data workbook.

For this exercise, the data being mapped is random data generated for U.S. states and all the counties in Illinois - two separate maps. Take a look at the maps here -- http://www.mp2kmag.com/htc_evo/. These are the maps that are automatically created and published by the macro contained in the Mobile Example Macro.xlsm workbook.

The data is created in the Mobile Example Random Data.xlsx workbook using the RANDBETWEEN function. The first thing the macro does it open up this workbook and update the random data values, then saves the workbook.

Code:

    'open rand data workbook and save
    Workbooks.Open Filename:=ThisWorkbook.Path & "\Mobile Example Random Data.xlsx"
    Workbooks("Mobile Example Random Data.xlsx").Activate
    Range("StateData").Calculate
    Range("CountyData").Calculate
    
    ActiveWorkbook.Save
    ActiveWorkbook.Close

For real-world data, you could imagine this data might be sales coming from a corporate database, and xml feed with daily updated weather data (XML data feeds - Current Conditions - NOAA's National Weather Service), economic data scraped from the web e.g. Unemployment rates - Unemployment rates by state from CNNMoney), or perhaps manually prepared.

The two MapPoint .ptm files DatabyCounty.ptm and DatabyState.ptm each have linked datasets pointing to the data workbook. Once the randomly generated data has been updated in the previous step, the next thing the macro needs to do is open up both .ptm files and update the linked dataset using the .UpdateLink method.

Code:

    'open ptm files, update linked data, and save
    Dim MPApp As MapPoint.Application
    Dim objMap As MapPoint.Map
    Set MPApp = CreateObject("MapPoint.Application")

    MPApp.Visible = True
    Set objMap = MPApp.ActiveMap
    MPApp.OpenMap (ThisWorkbook.Path & "\DatabyCounty.ptm")
    objMap.DataSets(2).UpdateLink
    Dim objSW As MapPoint.SavedWebPage
    Set objSW = objMap.SavedWebPages.Add("DatabyCounty", _
      objMap.Location, "DatabyCounty", _
      True, False, True, 800, 480, False, True, False, True)
    objSW.Save
    objSW.AutoResave = True
    objMap.Save
    objMap.SaveAs "DatabyCounty", geoFormatHTMLMap, True
    
    objMap.Saved = True
    
    MPApp.OpenMap (ThisWorkbook.Path & "\DatabyState.ptm")
    objMap.DataSets(2).UpdateLink
    Set objSW = objMap.SavedWebPages.Add("DatabyState", _
      objMap.Location, "DatabyState", _
      True, False, True, 800, 480, False, True, False, True)
    objSW.Save
    objSW.AutoResave = True
    objMap.Save
    objMap.SaveAs "DatabyState", geoFormatHTMLMap, True
    
    objMap.Saved = True
    
    MPApp.Quit
    Set MPApp = Nothing

You can see in the code above, the program is also creating a saved web page for each map. This is one method for getting a map image file of the MapPoint map. It may not be the most elegant way as a bunch of extra files and folders are created that we are not going to use, but it's simple and it works.

You can see in the code above where the image dimensions are set. This helps it display properly without being grainy or requiring the mobile user to have to zoom in on the image.

The last thing to be done is upload the images. This is accomplished by creating a couple of script files which are run in a command shell. Again, not the most elegant way to accomplish FTP within Excel VBA, but it's simple and it works.

Code:

    'upload images
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("c:\temp\script.dat", True)
    a.writeline "user" 'put in your ftp user name
    a.writeline "pass" 'and password
    a.writeline "cd public_html"
    a.writeline "cd htc_evo"
    a.writeline "bin"
    a.writeline "put " & Chr(34) & "C:\Work\2011_01_04_HTC_EVO\Data by County_files\image_map.gif" & Chr(34)
    a.writeline "rename image_map.gif county_map.gif"
    a.writeline "put " & Chr(34) & "C:\Work\2011_01_04_HTC_EVO\Data by State_files\image_map.gif" & Chr(34)
    a.writeline "rename image_map.gif state_map.gif"
    a.writeline "quit"
    a.Close

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("c:\temp\upload.bat", True)
    a.writeline "ftp -i -s:c:\temp\script.dat www.example.com"
    a.Close

    dRetVal = Shell("C:\temp\upload.bat ", 0)

I welcome comments, suggestions, extensions on the above solution for automatically publishing MapPoint maps to the web formatted for mobile devices. I am also eager to hear what uses you can put this to (e.g. sending a list of stops to drivers, sending updated leads in a particular city to a salesforce, etc.) and would also be glad to help implement something for your organization.

All four files referenced above are zipped up and available for download here. Note that this will not work right out of the box as I have removed my ftp user name and password from the script, but if you have a web server and ftp site it would be trivial to put those in.

Have Fun and Happy Mapping!

Eric Frost

By the way, here are some live links to the maps.

As I periodically open the macro, these map images are recreated and changed!





Eric