Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert Data to US Map

    I had a request to convert data/superimpose the results of data to a United States Map. For example, in column A, I have Supplier Name, Column B, I have City, Column C, I have State. All of this is ORIGIN.

    In column F, I have Receiving Company, in Column G I have City, In Column H I have State and in Column K I have the Number of Loads.

    All fields above are text except number of loads. There is about 100 rows.

    Is it possible to take the aforementioned data and draw lines in (or superimpose over) a US Map and across the line somehow indicate the number of loads?[/B] For example, draw a line from Richmond, VA to Charlotte, NC showing Richmond as the origin and Charlotte as the destination with 100 listed as the number of loads and do the same for all rows of data. The line could point from Richmond to Charlotte with 100 listed over the top.

    Now, if it isn't possible to show the number of loads then the arrows showing "to" and "from" would be fine.

    I can attach sample data as necessary.

    Many thanks!

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    attach an example and we'll have a go

    zeddy

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

    The following workbook has some sample source and destination cities set up as you described. By selecting any cell in route (row of cells), the map will display a gradient line between the two cities indicating the direction of travel and an indicator for the number of loads. Clicking on a different route will display the city connection and number of loads specific to that route. I didn't spend a lot of time on this to make it more interactive but it will get you on a running start.

    HTH,
    Maud

    map1.png
    Attached Files Attached Files

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

    RetiredGeek (2015-06-20)

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

    Very Very Cool!

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Thanks RG. If I had more time I would have liked to have coded it so that the graphics are built from the data instead of coded by me based on the data.

    Maud

  7. #6
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Map with data

    Quote Originally Posted by Maudibe View Post
    Mitch,

    The following workbook has some sample source and destination cities set up as you described. By selecting any cell in route (row of cells), the map will display a gradient line between the two cities indicating the direction of travel and an indicator for the number of loads. Clicking on a different route will display the city connection and number of loads specific to that route. I didn't spend a lot of time on this to make it more interactive but it will get you on a running start.

    HTH,
    Maud

    map1.png
    This is mostly what I am looking for. Is it possible to show the graphics all the time without having to click in that row? For example, all of the lines are drawn all of the time along with the number of loads?

    Next, where is the logic that makes this work? I'd like to look at to better understand what this is doing.

    Thanks for a start.

    Mitch

  8. #7
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Zeddy, the one that was attached below is kind of what I am looking for; however, I'd like it to always show the lines between cities and always show the number of loads without having to go to that row.

    I'd also like the map to show the name of the cities and the worksheet to allow for data addition. This one is restricted to 10 cases and appears to be in VBA which I don't know...

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

    I disabled the code that toggles the lines. It is essentially now a spreadsheet with a map and lines. The next step would be to add code that looks at the data and adds to the maps as you make entries. If you have 2010 goto Home on he ribbon > Find & Select > Selection Pane.

    HTH,
    Maud

    map2.png
    Attached Files Attached Files

  10. #9
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Thanks

    Quote Originally Posted by Maudibe View Post
    Mitch,

    I disabled the code that toggles the lines. It is essentially now a spreadsheet with a map and lines. The next step would be to add code that looks at the data and adds to the maps as you make entries. If you have 2010 goto Home on he ribbon > Find & Select > Selection Pane.

    HTH,
    Maud

    map2.png
    Ok... an inch closer. Maybe someone w/VBA skills can add the part that "looks at the data and adds to the maps as you make entries". I do not know VBA so I can't get there from here.

    Thanks anyway!

    Mitch

  11. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Other than defining coordinates for the map, another possibility is tapping into the Google maps API. Something RG and I were toying with a while back

    http://windowssecrets.com/forums/sho...l=1#post912895

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

    I was looking at G.M. again today in relation to this. I couldn't find a way to "easily" do this. Now if you want to get into JavaScript programming it can be done. Any Takers?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    ..are we creating a free route-planning delivery tool here????
    Looking at Maud's lovely map, I was thinking of using this to display a record of 'drops' along a flightpath from drone-delivery craft like what I've seen amazing.com and eBade.com developing.
    I would post my code here, but it could be used for nefarious purposes, so I'd better not.

    zeddy

  14. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Mitch,

    Here is a fully interactive map with routes and loads. There is data validation dropdowns to add a source and destination. As you add or remove cities form the routes, the map will update. The route will not be mapped unless a source and destination city are filled in along with a load.

    This is a fun project

    Maud

    map3.png

    Code:
    Sub Controller()
    'DELEGATES PROCESSES
        Application.ScreenUpdating = False
        ClearShapes
        AddConnector
        Range("A2").Select
        Application.ScreenUpdating = True
    End Sub
    
    Public Sub ClearShapes()
    'REMOVE ALL CONNECTORS
        Dim sh As Shape
        For Each sh In ActiveSheet.Shapes
            If sh.Type = msoTextBox Or sh.Type = msoLine Then
                sh.Delete
                GoTo continue
            End If
            If Left(sh.Name, 14) = "Straight Arrow" Then
                sh.Select
                Selection.Cut
            End If
    continue:
        Next sh
    End Sub
    
    
    Public Sub AddConnector()
    '-------------------------------
    'DECLARE AND SET VARIABLES
        Dim ws1 As Worksheet, ws2 As Worksheet
        Dim XPos As Long, YPos As Long, LastRow As Long, EndRow As Long
        Set ws1 = Worksheets("Sheet1")
        Set ws2 = Worksheets("Sheet2")
        LastRow = ws1.Cells(Rows.Count, 2).End(xlUp).Row
        EndRow = ws2.Cells(Rows.Count, 5).End(xlUp).Row
    '-------------------------------
    'ADD NEW STRAIGHT CONNECTOR
        For I = 3 To LastRow
            If ws1.Cells(I, 2) = "" Or ws1.Cells(I, 5) = "" Or ws1.Cells(I, 7) = "" Then
                GoTo skip
            End If
    '-------------------------------
        'FIND CITY SHAPE INDEX
            StartRow = WorksheetFunction.Match(ws1.Cells(I, 2), ws2.Range("A4:A" & EndRow), 0)
            StopRow = WorksheetFunction.Match(ws1.Cells(I, 5), ws2.Range("A4:A" & EndRow), 0)
            Starts = WorksheetFunction.Index(ws2.Range("A4:E" & EndRow), StartRow, 5)
            Stops = WorksheetFunction.Index(ws2.Range("A4:E" & EndRow), StopRow, 5)
    '-------------------------------
        'ADD NEW CONNECTOR BETWEEN CITIES
            'ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 1000, _
                1000, 1000, 1000).Select
           ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 567, 117, 628.5, 161.25). _
            Select
            Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes("Oval " & Starts), 5
            Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes("Oval " & Stops), 5
            Selection.ShapeRange.RerouteConnections
    '-------------------------------
        'FIND TEXTBOX POSITION
            Y1 = WorksheetFunction.Index(ws2.Range("A4:E" & EndRow), _
                WorksheetFunction.Match(ws1.Cells(I, 2), ws2.Range("A4:A" & EndRow), 0), 3)
            X1 = WorksheetFunction.Index(ws2.Range("A4:E" & EndRow), _
                WorksheetFunction.Match(ws1.Cells(I, 2), ws2.Range("A4:A" & EndRow), 0), 4)
            Y2 = WorksheetFunction.Index(ws2.Range("A4:E" & EndRow), _
                WorksheetFunction.Match(ws1.Cells(I, 5), ws2.Range("A4:A" & EndRow), 0), 3)
            X2 = WorksheetFunction.Index(ws2.Range("A4:E" & EndRow), _
                WorksheetFunction.Match(ws1.Cells(I, 5), ws2.Range("A4:A" & EndRow), 0), 4)
            XPos = (X1 + X2) / 2
            YPos = (Y1 + Y2) / 2
    '-------------------------------
        'ADD TEXTBOX
            AddTextBoxes Cells(I, 7), XPos, YPos
    skip:
        Next I
    End Sub
    
    
    Public Sub AddTextBoxes(Load As Range, X As Long, Y As Long)
    'ADD AND CONFIGURE TEXTBOXES
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 480.75, 561, 35, _
        15).Select
    With Selection
    .ShapeRange.TextFrame2.TextRange.Font.Size = 9
    .ShapeRange(1).TextFrame2.TextRange.Characters.Text = Load
    .ShapeRange.TextFrame2.MarginLeft = 0
    .ShapeRange.TextFrame2.MarginRight = 0
    .ShapeRange.TextFrame2.MarginTop = 0
    .ShapeRange.TextFrame2.MarginBottom = 0
    .ShapeRange.ScaleWidth 0.7083333333, msoFalse, msoScaleFromTopLeft
    .ShapeRange.ScaleHeight 0.6086956522, msoFalse, msoScaleFromTopLeft
    .ShapeRange.TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
    .ShapeRange.Left = Y
    .ShapeRange.Top = X
    End With
    End Sub
    Attached Files Attached Files

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

    RetiredGeek (2015-06-21)

  16. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Working on a method to auto setup new cities with coordinates. Will post back shortly.

  17. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Top marks Maud!

    zeddy

Page 1 of 2 12 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
  •