Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    869
    Thanks
    142
    Thanked 10 Times in 9 Posts

    Replacement for Microsoft Map

    I'm looking for a replacement for the Microsoft Map program that shipped with early versions of Excel.

    It had a bunch of features, but the one I'm looking for was pretty simple: You input a list of ZIP codes, and the program draws an outline map of the U.S. with a dot in each of those ZIP codes. You could optionally vary the size of the dot, depending on how many instances of a given ZIP code were in your input list.

    The software I've been able to find offers extreme capability, but nothing this simple that I can discover. Also, most of the software is $99, and is designed to put something on your website, rather than just give you a map. It is extreme overkill for my needs, and doesn't even do what I want.

    Who can help me find this needle in a haystack?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Super Moderator
    Join Date
    Jun 2011
    Location
    New England
    Posts
    4,837
    Thanks
    172
    Thanked 668 Times in 589 Posts

  3. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    869
    Thanks
    142
    Thanked 10 Times in 9 Posts
    I must be on the wrong side of History. I have Excel 2010.

    Still looking. ;-(
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  4. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    869
    Thanks
    142
    Thanked 10 Times in 9 Posts
    Right now I'm putting together a manual workaround.

    I formerly used Microsoft Map to make this:
    CVBsMap.png

    Microsoft Map created the outline map and placed the red dots, based on ZIP codes that I input to Microsoft Map. There are about 100 dots, and I need another map with about 300 dots. The outline map was too jaggy for my taste. You can see it if you magnify the image. Also, not every existing ZIP code could be used, but I worked my way around that. Unfortunately, Microsoft Map no longer works, nor is available.

    My start of the manual workaround is this:
    StatesMapWithDots.png

    Not jaggy, size and color of dots are controllable by me, but I have to place the dots manually.

    Ideally, the map would be on my website. I would be able to click on one of the red dots and go to a place on my website. That is almost too much to ask, but everything is possible these days.

    Instead of clicking the dots to go to a specific city on my site, it would be OK to be able to click on one of the states to go to a specific state. The "hot spot" on the state would NOT have to precisely follow its borders. It could be pretty crudely defined, I'm thinking.
    Last edited by Lou Sander; 2017-01-04 at 17:06. Reason: Add clicking of state
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    869
    Thanks
    142
    Thanked 10 Times in 9 Posts
    Pretty amazing, at first look. I'll dig into it tomorrow or the next day. SOMEBODY is a pretty ingenious programmer!
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    869
    Thanks
    142
    Thanked 10 Times in 9 Posts
    It works, but there are bugs. When I open the worksheet, everything looks good. The Dallas dot is on Dallas, and the Houston dot is on Houston.

    When I add a ZIP (15222 for Pittsburgh), all the dots move in unison about 5mm to the East. The Pittsburgh dot is placed in PA, but it's considerably North and East of where it should be.

    I can move the map on my screen, making Dallas and Houston dots OK, but Pittsburgh is still out of position.

    Also, much of the whole thing is hidden, and I don't know/remember how to make it accessible.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,474
    Thanks
    31
    Thanked 63 Times in 59 Posts
    As usual, Maud comes through with a slick piece of coding!

    Lou, I have tried various zip codes, deleted, added others, etc., and haven't experienced any of the issues you reported.

  8. #8
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    869
    Thanks
    142
    Thanked 10 Times in 9 Posts
    Maud & KW,

    I am using two 720P TV sets as monitors. Screen resolution is 1366 x 768.

    It doesn't matter much what map is on my web site, as long as it's presentable. I sort of like the outline map, though. We are just showing people the general location of a bunch of cities. I COULD use the Google map to guide me in manual placement of dots on the outline map, if that were the way to go. Or I could use a screen shot of the Maud map with its dots. I'll be happy to use whatever is the best combination of ease and appearance.

    Here's how Maud's original map looks on my screen, before changing anything in the ZIP code table; as far as I can tell, every dot is just where it should be:

    MaudMapOriginal.PNG

    Here's how the map looks after I add ZIP 15237. The preexisting dots seem to have moved due East. The new 15237 dot seems to be East and North of where it should be. I've put a big blue dot approximately where 15237 actually is in SW Pennsylvania. Google Maps puts it at 40.54, -80.12. It is a suburb of Pittsburgh, whose Google Maps coordinates are 40.43, -80.05. Approximations of these are OK for my work.

    MaudMapPlus15237.PNG

    Interestingly, here's how the original map looks after I delete ZIP 85121. It looks like the dots are displaced by the same amount as when I add 15237:

    MaudMapLess85121.PNG

    BTW, after I add a ZIP code, I need to reload the worksheet to get things back the way they were. When I add other ZIP codes, the dots seem to be offset from their proper positions by about the same amount as 15237 (i.e. North and East), but I haven't measured anything.

    I'm betting that my screen resolution is at the bottom of this.
    Last edited by Lou Sander; 2017-01-05 at 14:43. Reason: Fix typo
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,930
    Thanks
    139
    Thanked 502 Times in 478 Posts
    Hi Maud/Lou

    Just for fun I tried to get the data for City, State, Latitude, Longitude without using a database as part of the workbook.
    This means the file size is much smaller (mostly the map picture).
    The attached file uses reverse geocoding with google to get the data.
    This will require an internet connexion.

    Took me a while to restrict the data to USA (I kept getting Bulgarian details instead of SAN FRANSISCO for zipcode 94101!!)
    So, when you enter a 5-digit zip code in column [B], the data is 'fetched' from the internet and populates the respective columns.

    (google allows 25,000 searches per day, so should be OK for the number of records required)

    zeddy
    Attached Files Attached Files

  10. #10
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    869
    Thanks
    142
    Thanked 10 Times in 9 Posts
    Nice idea. With Zeddy's file, there are still erratic dot placements on my system.

    When Zeddy's workbook is launched, the dots aren't in the right place. I watch Dallas and Houston for this (they are in Texas and are labeled on the map, and I'm hoping everyone can locate them. When Maud's file is launched, the red dots perfectly coincide with these two cities on the map. When Zeddy's file is launched, the red dots are 5mm or so West of the cities on the map. There is also an extra dot in Arizona.

    When I add 15237 to Zeddy's file, a red dot appears, but it is well East and North of the proper location. Here is Zeddy's image as the file opens:

    ZeddyOriginalMap.PNG
    Last edited by Lou Sander; 2017-01-06 at 18:52.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  11. #11
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    869
    Thanks
    142
    Thanked 10 Times in 9 Posts
    Here's more on Maud's version:

    I have another computer with a higher resolution monitor. I tried Maud's version with various resolutions, and the map seemed to behave the same, regardless of which one I used. Here are the resolutions I tried:

    1920x1080 (the native resolution of the monitor)
    1600x900
    1024x768

    This is in addition to the native 1366x768 resolution on my other system's monitors.

    I'm starting to think that the problem is something else than monitor resolution. (I'm not very knowledgeable in graphic cards, resolutions, etc. All I know is what I'm reporting here.)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  12. #12
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    869
    Thanks
    142
    Thanked 10 Times in 9 Posts
    I have great confidence in you!

    But please don't bust your hump excessively over my little project. (Though it might be useful to others. I think a lot of people would like to put pins into ZIP codes.)

    I've been fighting with Excel ZIP codes for a long time. Here's something I wrote in 2004 about debugging them:
    Attached Files Attached Files
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,930
    Thanks
    139
    Thanked 502 Times in 478 Posts
    Hi Lou/Maud

    Essentially, we can use a scatter-plot to draw the dots on the screen as a chart.
    The trick is then to have an appropriate map of the USA 'overlaid' on the chart dots.
    By appropriate scaling of the scatter-chart axes we can 'line-up' the dots with the map.
    (using x-axis as longitude and using latitude as Y-axis)

    See attached updated version, with a simpler map.
    I used a different sheet for the data, and the map.
    The file size has been reduced further.

    Do the 'dots' line up better??

    zeddy
    Attached Files Attached Files

  14. The Following User Says Thank You to zeddy For This Useful Post:

    Lou Sander (2017-01-08)

  15. #14
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    869
    Thanks
    142
    Thanked 10 Times in 9 Posts
    Zeddy - So far, it is looking pretty good. It's not perfectly accurate, but it's close. It doesn't seem to recognize ZIP codes with leading zeroes, unless you put a single quotation mark ahead of them. Formatting the column as Special > ZIP Code may fix that. See the "Debugging Excel..." attachment in my post above.

    I have an old ZIP Code Data Atlas that has ZIPs for just about anywhere, and shows them on state maps. I'll do some more testing using that.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  16. #15
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    869
    Thanks
    142
    Thanked 10 Times in 9 Posts
    Zeddy's workbook is looking good. The ZIP Code format does fix the leading zeroes problem. I added some challenging locations, and it handled them pretty well. It missed Key West, Florida, entirely, but that city is at the end of an archipelago that may not be on the map. At low magnification, Massena, NY looks to be in Canada, but at higher magnification (170%), it looks perfect. Chicago seems to be in Lake Michigan, but we'd like it to be there, anyway. It's the murder capital of the United States.

    Here's Zeddy'd workbook with my ZIPs added:
    Attached Files Attached Files
    Last edited by Lou Sander; 2017-01-07 at 18:42. Reason: Add ZIP formatting comment
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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