Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Create pop-up map in Excel

    I have an Excel application that takes pairs of longitudes/latitudes and computes the distance between the two points (addresses). I have not only the long/lat but also the full address in each pair.

    I'd like to use the two pairs to create a pop-up map in Excel.

    Is this possible, does anyone know and, if so, how?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    I haven't done this but my guess is that if you look into the Google Maps interface you could probably pass the information you have to it and have it pop-up a browser window with the map. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts
    KW,

    There was a map making tool in office 97 but was removed in version 2003. If you have the earlier version, it is located on the insert menu. This site creates maps in Excel based on cell data. It may help you to do what you want.

    BatchGeo

    HTH,
    Maud

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Maud, I sort of remember that in the old version, but don't run it any longer. Up to 2010.
    I've used the BatchGeo and it's nice, but I wanted something automatic within Excel rather than have the user copy and paste and generate the map. Still digging for a way. There must be something.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts
    I have 97 on an old machine I haven't booted for years. This weekend, I'll dig it out and give it a try. Perhaps, I may be able to generate the coding/graphics for you. However, it sounds as though you want your users to generate maps on the fly
    Last edited by Maudibe; 2013-07-11 at 23:55.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Maud, you're right. What I'd like to do is have two addresses or two long/lat values and have a button that will pop-up a Google-Maps-Like map.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts
    KW,

    Question: The map that pops up, will it be the same map with just points in different positions or will it be a different map based on the points selected? Would it be possible to use the same map that could encompass all the points by decreasing the scale? If so, this can be done with VBA coding.

    Update
    I have done something like this before but it was a floor plan of an Intensive care Unit in a hospital. Bed assignments for the nurses dictated points plotted, paths between the points laid out, and the distanced calculated. Similarly, the latitude/longitude could easily be converted to pixels and the points plotted and the rest would be fairly simple.

    Maud
    Last edited by Maudibe; 2013-07-12 at 23:57.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I want to give either a UDF or VBA macro two zip codes or two longitude/latitude points and have a map show the two locations. Kinda like what maps.google coes when you want directions from one address to another.

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts

    Google directions using VBA

    KW,

    Below is some code that will pop up a Google map in the worksheet by selecting start and end destinations from combo boxes and clicking on the sheet's Get Directions button. The fully functional Google map will have the addresses filled in and presented with the directions and the map already calculated. The Combo boxes will repopulate when Map Sheet is activated. The tricky part was figuring the elements on the web page to send the combo box values to. You can have a different mechanism of selecting your addresses such as radio button, textboxes, etc.

    HTH,
    Maud

    Note: This was done in Excel 2010 using the Microsoft WebBrowser Control

    Maps1.png maps2.png

    Code:
    Private Sub CommandButton1_Click()
    'CHECK IF STARTING AND END ADDRESSES ARE THE SAME
    If ComboBox1.Value = ComboBox2.Value Then
        MsgBox "Starting and ending addresses cannot be the same.  Please re-enter."
        Exit Sub
    End If
    'NAVIGATE TO GOOGLE MAPS
    WebBrowser1.Visible = True
    WebBrowser1.Navigate2 "https://maps.google.com/maps?hl=en"
    Do
    DoEvents
    Loop Until WebBrowser1.ReadyState = 4
    'FILL IN FIELDS FROM COMBO BOXES AND GET DIRECTIONS
    WebBrowser1.Document.getelementbyid("d_launch").Click
    WebBrowser1.Document.getelementbyid("d_d").Value = ComboBox1.Value
    WebBrowser1.Document.getelementbyid("d_daddr").Value = ComboBox2.Value
    WebBrowser1.Document.getelementbyid("d_sub").Click
    Do
    DoEvents
    Loop Until WebBrowser1.ReadyState = 4
    End Sub
    
    
    Private Sub Worksheet_Activate()
    'START WITH HIDDEN BLANK BROWSER WINDOW
    WebBrowser1.Visible = False
    WebBrowser1.Navigate2 "about:blank"
    With Worksheets("Addresses")
    'REPOPULATE COMBO BOXES WITH ADDRESSES
    ComboBox1.Clear
    ComboBox2.Clear
    For I = 1 To 5
        ComboBox1.AddItem .Cells(I, 1).Value
    Next I
    For I = 1 To 5
        ComboBox2.AddItem .Cells(I, 1).Value
    Next I
    End With
    End Sub
    Update: I have revised the file to add unlimited additional addresses to the "Addresses" sheet beyond the 5 sample address limitation. Also, have enabled the ability to add latitude and longitude coordinates. Example: 60,-120 Please use GoogleMaps_Revised2.xlsm instead.
    Attached Files Attached Files
    Last edited by Maudibe; 2013-07-15 at 16:59.

  10. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    Alouso (2013-07-13),RetiredGeek (2013-07-13)

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    I get an invalid attachment message from the Lounge when I click on your attachment in the above post (FF 22, Office 2010, Win 8 Pro). You may need to zip the file due to the macros.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Try again RG,

    I was just uploading a revised file.

  13. #12
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    Maudibe

    How did you send data to the web site? I looked at the code lines getelementbyid("d_launch"). How did you know that? Are these names the same on all sites?

    A.

  14. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    The code works like a charm. Thanks for doing the heavy lifting on my suggestion.

    FYI: You only live about 17 miles from where I grew up!
    Last edited by RetiredGeek; 2013-07-13 at 16:50.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts
    RG,

    Thanks for the suggestion. Any ideas on making it a class object?

    You, Kweaver, and me used to play together when we were kids but your beard wasn't as thick then At one point, lived next door to you in Chalfont.

  16. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Alouso,

    To find the elements fields to send the data to is a bit tricky. You will need a bit of deduction but it is still hit or miss. First of all, the element/field names are not the same for every web site. If fact, they are quite different. This is how I found them. If you first go to Google maps, you will notice that there is no fields for a starting and ending address but there is a "Get Directions" button. If you click on View> Source, an editor will appear with the source code.

    Click on Edit> Find within the editor and search for "Get Directions". There might be multiple instances of it within the source code. In the line where it exists, look for id="something". Next to the words "Get Directions" is id="d_launch". See image 1. This is the identifier of the element "Get Directions" button. So I tried it in the code line:

    WebBrowser1.Document.getelementbyi("d_launch").Cli ck

    and it worked. When I ran the code, Google opened on the map page but now showing the start and end address textboxes as if I had clicked the "Get Directions" button. If the code produced an error, I would look for the next instance of "Get Directions"

    Source1.png

    The next step was to find the field IDs of the two address input textboxes. On the web site, if you hold your mouse over the textbox A, you will see a comment appear that displays "Start address". I opened an new editor showing the source code and did a search for "Start address" then looked for the element's id. If you look at the next image, you will see the search result and next to it, title="Start address" id="d_d". So I entered the id into a code line that will send the contents of start address combo box on the work sheet to the first textbox on Google maps:

    WebBrowser1.Document.getelementbyid("d_d").Value = ComboBox1.Value

    Source2.png

    I ran the code again and the input from ComboBox1 appeared. I repeated step 2 to find the next field using "End address" in my search and found id="d_daddr" then added it to the code line:

    WebBrowser1.Document.getelementbyid("d_daddr").Val ue = ComboBox2.Value

    Lastly, I need the id of the button on Google maps that would initiate the search. The button had the caption "Get Directions". So I did a search bypassing the button ID I used in step one and found id= "d_sub". Since this is a button, I inserted into the code line:

    WebBrowser1.Document.getelementbyid("d_sub").Click

    which was the element id needed to retrieve the results. So the basic steps are to follow the order you would use if you were using the web site but you use the source code to find the element's ID. Use any clues to find the elements: captions, mouseover comments, default textboxt text, order of appearance on the sheet, etc. Once you find them, it's trial and error. If one ID doesn't work, move to the next instance of the search.

    There is no guarantee that tomorrow Google will not revamp their website and change the IDs of the buttons and fields that this code uses. but at least if they do, we will have a method to correct them in our code.

    HTH,
    Maud

  17. The Following User Says Thank You to Maudibe For This Useful Post:

    Alouso (2013-07-14)

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •