I use Mappoint a lot for getting journey distances, and I automate it using VBA running from Excel. However for some locations - particularly China, Russia, and the Balkans - Mappoint doesn't have route info, but Virtual Earth/Maps Live does (much better than Google in fact).

I have been trying to get the VBA code to call Virtual Earth, generate locations and a route and collect back the journey distance and time (though journey times look pretty crude). I don't need to see the map, I just want the return values.

I can generate a HTML file that does the calling but I cannot find how to get the return values, and ideally I would do it as a URL. I think this must be something others have done...
Any thoughts, advice or links? Anything welcomed.
John

I have not done this, but I think it would make a good article. Perhaps we could collaborate on something?

Eruc

Eric - I could sure due with a method at least. Maybe I can give a bit more on where I have got to (I realise this may be too much information).

A VBA procedure puts up a form asking for the waypoints and creates the following HTML/Javascript file (modified from Microsoft example):

(attached, simply change to .html from .txt)

When that is done the file is then launched and shows a map and then generates the route in this case StPetersburg to Warsaw. Again I have lifted code from another web example...

Code:

Private Const SEE_MASK_NOCLOSEPROCESS = &H40
Private Declare Function ShellExecuteEX Lib "shell32.dll" _
   Alias "ShellExecuteEx" (SEI As SHELLEXECUTEINFO) As Long
Declare Function GetSaveFileName Lib "comdlg32.dll" _
   Alias "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Long
' VBShellExecute launches the file specified by the FPath parameter
Public Sub VBShellExecute(FPath As String)
   Dim SEI As SHELLEXECUTEINFO
   Dim ans As String
 
   With SEI
     .cbSize = Len(SEI)
     .fMask = SEE_MASK_NOCLOSEPROCESS
     .hwnd = 0
     .lpVerb = "open"
     .lpFile = FPath
     .lpParameters = vbNullChar
     .lpDirectory = vbNullChar
     .nShow = 0
     .hInstApp = 0
     .lpIDList = 0
   End With
   Call ShellExecuteEX(SEI)
 
End Sub

So far so good
What I really want to achieve from here is
1) I want to be able to make the map and the IE call invisible
2) I cannot see how I can get the journey distance & time returned back to the VBA procedure.
3) I think there must be a more elegant way of doing the whole thing, with a relatively simple URL

Any thoughts welcomed.

John,

I f you usinf Excel vba, see my post #2045, for the code for a function theat returns "trip duration". You can invoke this in excel and point to a cell (or) cells that contain addresses. I also have similar function for "driving distance".

Hope this helps,

Trebormac

Hello