Получение гео-координат с Yandex карт


Краткое описание

Макрос для Excel (VBA) для быстрого и удобного получения гео-координат из Yandex. В качестве исходных данных на листе указывается список адресов. Результат (широта и долгота) будет выведен в соседние ячейки.


Подробное описание

Наверное многие из знают, что такое карты Yandex и Google. Может быть у вас даже были (или есть) задачи, когда по списку адресов нужно получить координаты точек на карте (широту и долготу)?

Специально для данной цели нами был написан макрос в экселе для получения координат с Yandex карт.

Работать с макросом очень просто:

  1. указываете список адресов на листе,
  2. нажимаете кнопку,
  3. получаете результат.

При желании, макрос несложно доработать на обратный порядок, то есть по списку координат получать адреса.

Также обратите внимание, что одни и те же адреса могут выдавать немного разные координаты в Yandex и Google. Отличие не большое, но оно есть и, возможно, его стоит учесть. Поэтому, если этот момент вам принципиально важен, обязательно вручную проверьте результат в разных поисковых системах.

Важно!
Данная версия абсолютно функциональна, но имеет ряд ограничений, описанных ниже в комментариях.
На текущий момент на сайте опубликована новая версия данного макроса, существеенно доработанная и улучшенная: ссылка на страницу.

Вернуться к списку примеров

Комментарии ()

  1. Ul 30 октября 2015, 15:02 # 0
    Яхууу, спасибо большое.
    Подлечил ваш калькулятор
    1. Oles 18 ноября 2015, 18:43 # 0
      Молодчина! Респект и уважуха!
      1. essential 19 февраля 2016, 16:52(Комментарий был изменён) # 0
        Не работает :(

        User defined type not defined?

        Function ParserXML(ByVal iAdress As String)
        Dim XMLDoc As New MSXML2.DOMDocument, nodeList As MSXML2.IXMLDOMNodeList
        Как подлечить?

        1. essential 06 марта 2016, 09:20 # 0
          Подчинил.
          Надо подключить библиотеку Microsoft XML v3.0 вместо Microsoft XML v6.0, которая включена по умолчанию на Office.
          Для этого в редакторе MS VBA выберите Tools > References и найдите в списке Microsoft XML v3.0.
          1. Maxim 08 марта 2016, 20:20(Комментарий был изменён) # 0
            essential, спасибо!
            Скорее всего, зависит от версии вашего офиса. У меня на Excel 2007 и Excel 2013 код из файла работает без каких-либо модификаций. Но подключаемые по умолчанию библиотеки, конечно, могут отличаться у разных пользователей.
            Еще, как вариант, можно
            Dim XMLDoc As New MSXML2.DOMDocument, nodeList As MSXML2.IXMLDOMNodeList
            заменить на
            Dim XMLDoc As Object, nodeList As Object
            Set XMLDoc = CreateObject("MSXML2.DOMDocument")
            Тогда в MS VBA «Tools > References» вообще можно ничего не подключать.

            На днях выложу обновленную версию макроса, без привязки к References + с возможностью выбора режима работы: Yandex или Google сервис использовать для анализа.
            1. Kos 09 марта 2016, 23:44 # 0
              круто! ждемс!
              1. Александр 16 ноября 2016, 15:20 # 0
                На днях выложу обновленную версию макроса
                А есть???
          2. Александр 15 марта 2016, 17:59 # 0
            Не работает.
            Выдает ошибку Run-Time error 13.
            Я в этом деле совсем 0.
            Подскажите как это работает?
            1. Maxim 17 марта 2016, 07:07 # 0
              Уточните, какая у вас версия ОС (Windows 7, Windows 8, Mac OC и тп), разрядность системы (Windows x64 или x32), версия Microsoft Office (2007, 2010, 2013, 2016).
              1. Владимир 29 ноября 2016, 07:54 # 0
                У меня Windows 10 64х, на которой стоит MS Office 2010.
                Сначала макрос выдавал ошибку User defined type not defined. После замены Microsoft XML v6.0 на Microsoft XML v3.0, как описано выше, появилась ошибка Run-Time error 13 и жёлтым выделяется строка GlobalArray(i, 2) = tmpArray(2).
            2. Дмитрий 21 мая 2016, 00:23 # 0
              Спасибо! Очень выручили! :)
              1. Алексей 25 мая 2016, 18:19 # 0
                Спасибо, отличная работа.
                Скажите, утилит полностью бесплатен? Может быть есть какой то срок работы или иное ограничение?
                Можно ли получить бан от Yandex?
                1. Maxim 05 июня 2016, 11:17 # 0
                  Сама утилита (макрос) бесплатная. БАН от яндекса не получите, разве что при большом количестве адресов временное (суточное) ограничение на выгрузку гео-данных. Но по опыту, данное ограничение достаточно велико, чтобы его превысить.
                2. Ленар 09 июня 2016, 09:32 # 0
                  Добрый день!
                  Подскажите пожалуйста, что в макросе необходимо доработать, чтобы произвести обратную операцию — получение адреса по введенным координатам?
                  Заранее благодарен!
                  1. Maxim 09 июня 2016, 22:22(Комментарий был изменён) # 0
                    Добрый день.
                    Первое, это изменить строку запроса (вместо адреса передавать координаты):
                    iURL = "http://geocode-maps.yandex.ru/1.x/?geocode=" & iAdress & "&key=...==&results=1"
                    И немного доработать функцию ParserXML(), чтобы она корректно извлекала полученные значения адресов.
                    1. Варвара 17 ноября 2016, 12:55 # 0
                      А есть уже работающая версия перевода координат в адреса? Из адресов прекрасно переводит в координаты, но очень нужно наоборот!
                      1. George 16 февраля 2017, 22:28 # 0
                        Приветствую, Maxim!!!

                        Можно все-таки немного про то как и что поменять в функции ParserXML(), чтобы она корректно извлекала полученные значения адресов???
                        Я не программист, а очень нужна эта обратная функция

                        Заранее благодарен!
                        1. Maxim 17 февраля 2017, 00:05 # 0
                          Для не программиста, объяснение будет сложноватое. Попробуйте так, возможно, получится: excelstore.pro/examples-of-work/internet/getting-geo-coordinates-with-yandex-maps.html#comment-76
                          Я же, в свою очередь, в ближайшее время (на этой недели) таки найду время и доработаю макрос.
                    2. Елена 21 июня 2016, 19:28(Комментарий был изменён) # 0
                      В апреле скачивала всё работало (правда после замены в соответствии с комментарием от 08 марта 2016, 20:20), а теперь не хочет…
                      Пишет run-time error '13' type mismatch
                      Выделяет жёлтым строку: GlobalArray(i, 2) = tmpArray(2) 'адрес по версии яндекса
                      Можете помочь?
                      виндоус 10, 32 разрядная опер.система процессор x64, Офис 2007

                      ПОЖАЛУЙСТА!
                      1. Симон 30 июня 2016, 10:35 # 0
                        Аналогичная проблема. Офис 2010
                      2. Станислав 01 июля 2016, 13:23 # 0
                        Посоветуете что-нибудь Елене и Симону?
                        То же самое: месяц назад всё работало, сегодня нет.

                        виндоус ХР, 32, офис 07
                        Спасибо
                        1. Владимир 03 июля 2016, 01:53 # 0
                          присоединяюсь к вопрошающим «несоответсвие типов», помогите при оказии советом
                          1. Владимир 03 июля 2016, 09:08 # 0
                            Разобрался в чем проблема — ключ разработчика невалиден, используйте либо свой уже созданный, либо получите по ссылке
                            : tech.yandex.ru/maps/keys/

                            Ключик необходимо вставлять в строку
                            iURL = "http://geocode-maps.yandex.ru/1.x/?geocode=" & iAdress & "&key=AMsJFVIBAAAA6b1EZgIARt3xxKvqDXFSuzYNLrfjJlyduEsAAAAAAAAAAABtEHh2MEhJiiNct5e0zKVyv2ggKA==&results=1"
                            — замените набор символов
                            AMsJFVIBAAAA6b1EZgIARt3xxKvqDXFSuzYNLrfjJlyduEsAAAAAAAAAAABtEHh2MEhJiiNct5e0zKVyv2ggKA==
                            на свой ключ и всё заработает!

                            Кто скачал файл впервые

                            Dim XMLDoc As New MSXML2.DOMDocument, nodeList As MSXML2.IXMLDOMNodeList
                            заменить на
                            Dim XMLDoc As Object, nodeList As Object
                            Set XMLDoc = CreateObject("MSXML2.DOMDocument")
                            1. Станислав 05 июля 2016, 11:34 # 0
                              Что-то ничего не выходит: жму кнопку «Получить координаты», выскакивает окошко «Данные успешно загружены», а на самом деле в ячейках Широта и Долгота — пустота
                              Владимир, а вы код здесь не выложите, посмотреть, может у меня чё не так.
                            2. Дмитрий 05 июля 2016, 17:42 # 0
                              После всех изменений наконец-то все заработало. Огромное спасибо. Работа сокращена с 6 месяцев до 2-х недель.
                              Бывают адреса, которые не читаются и на таких действительно остаются пустые строчки. Но такое очень редко
                              1. Дмитрий 07 июля 2016, 08:49 # 0
                                Тоже самое что и у Станислава: жму кнопку «Получить координаты», выскакивает окошко «Данные успешно загружены», а на самом деле в ячейках Широта и Долгота — пустота
                                Помогите разобраться…
                                1. Ленар 07 июля 2016, 17:22 # 0
                                  Добрый день дамы и господа!
                                  Ввод валидного API ключа действительно помог. Заметил в файле «GeoYandex, v.2.xlsb» ошибку: столбец C — должен быть «долготой», столбец D — «широтой».
                                  1. Гость 27 июля 2016, 11:09 # 0
                                    Выдает ошибку
                                    run-time error '13' type mismatch
                                    1. Валерий 05 августа 2016, 23:38 # 0
                                      И ключ обновлял и строки менял, вчера работало — сегодня уже нет
                                      run-time error '13' type mismatch
                                      Помогите пофиксить
                                      1. Валерий 07 августа 2016, 19:39 # 0
                                        Разобрался, ограничения яндекса на 25000 запросов в сутки
                                      2. Максим 02 сентября 2016, 18:45 # 0
                                        вставил адрес «Россия, Москва, Малая Грузинская улица, 38», после запуска в строке появился адрес «Россия, Московская область, Балашиха, потребительский кооператив садоводческое товарищество Металлург-1, Малая Грузинская улица»
                                        Но это совсем в другой стороне… КАК БЫТЬ?
                                        1. Алексей 29 января 2017, 18:10 # 0
                                          Аналогичная проблема, не те координаты по некоторым адресам выдает. Точнее определяется адрес не точно, но если тот же адрес через сам яндекс вбивать, все ок.
                                          Как решить эту неточность?
                                          1. Maxim 13 марта 2017, 16:04 # 0
                                            Проверьте, как будет работать на новой версии макроса: excelstore.pro/examples-of-work/internet/get-the-address-and-coordinates-of-yandex-and-google.html
                                          2. MAX 13 сентября 2016, 19:27(Комментарий был изменён) # 0
                                            может подскажет кто? от Яндекса получаю две записи по одному запросу "-found-2-/found-" и данные выводятся по последней, НО правильная первая запись… Что поменять… не могу что-то разложить эти данные.
                                            1. Diana 30 сентября 2016, 09:47 # 0
                                              А как добавить столбец не только с координатами, но и областью. А еще точность нужна (до какой точности он определяет).

                                              И 1000 строк геокодер просматривает 10-15 минут. Можно ли ускорить работу геокодера регулируя количество запросов по 1 адресу?

                                              Помогите пж.
                                              1. Maxim 17 февраля 2017, 00:01 # 0
                                                В excel-e нет многопоточности.
                                                Чтобы одномоментно делать много запросов, нужно другое решение (не vba).
                                              2. Diana 30 сентября 2016, 10:08 # 0
                                                Область, и район.
                                                1. Ленар 10 октября 2016, 22:02 # 0
                                                  Поделюсь наблюдением: для получения адреса позиции по имеющимся координатам, достаточно в поле исходного адреса ввести через запятую долготу и широту. В ячейке «Адрес Yandex» получим запрашиваемый адрес.
                                                  1. Артем 26 октября 2016, 06:01 # 0
                                                    Добрый день!
                                                    Ключ поменял,Dim XMLDoc As New MSXML2.DOMDocument, nodeList As MSXML2.IXMLDOMNodeList заменил на
                                                    Dim XMLDoc As Object, nodeList As Object
                                                    Set XMLDoc = CreateObject(«MSXML2.DOMDocument»)
                                                    Все равно желтым посвечивает:
                                                    GlobalArray(i, 2) = tmpArray(2) 'адрес по версии яндекса
                                                    1. Maxim 13 марта 2017, 16:04 # 0
                                                      Добрый день. Проверьте, как будет работать на новой версии макроса: excelstore.pro/examples-of-work/internet/get-the-address-and-coordinates-of-yandex-and-google.html
                                                      Если всё-равно не работает, уточните адрес, который пытаетесь геокодировать.
                                                    2. Алиса 28 декабря 2016, 17:03 # 0
                                                      После всех изменений пишет что данные загружены, но их нет.
                                                      Подскажите в чем дело.
                                                      1. Арсений 24 января 2017, 18:01 # 0
                                                        Спасибо!
                                                        1. Maxim 19 февраля 2017, 02:59(Комментарий был изменён) # 0
                                                          Опубликовал новую версию макроса для работы с координатами и адресами.
                                                          Версия существенно доработана и улучшена. Все комментарии в данном посте постарался максимально учесть.
                                                          Ссылка на страницу: excelstore.pro/examples-of-work/internet/get-the-address-and-coordinates-of-yandex-and-google.html
                                                          1. Роман 03 мая 2017, 12:29 # 0
                                                            Максим, добрый день!
                                                            Скачал последнюю версию файла: ошибка уже не возникает, но в результате выдаётся «нет данных».
                                                            Как это можно поправить?
                                                            Спасибо.
                                                            1. Maxim 06 мая 2017, 17:06 # 0
                                                              Добрый день, Роман.
                                                              Актуальная версия макроса здесь: excelstore.pro/examples-of-work/internet/get-the-address-and-coordinates-of-yandex-and-google.html
                                                              Об этом сказано и в последнем комментарии на данной странице и в тексте самого описания.
                                                            2. Елена 05 мая 2017, 15:47(Комментарий был изменён) # 0
                                                              Вау спасибо вам большое за такой макрос!!!
                                                              Я про версию 3.
                                                              (Этот вариант не выдает данных)
                                                              1. Maxim 06 мая 2017, 17:07 # 0
                                                                Да, актуальная версия макроса опубликована здесь: excelstore.pro/examples-of-work/internet/get-the-address-and-coordinates-of-yandex-and-google.html
                                                                Если макрос пригодился, буду признателен, если поделитесь ссылкой на наш сайт или на указанную страницу с программой (в соц. сетях, на форумах и тд.)
                                                              2. Владимир 29 мая 2017, 12:47 # 0
                                                                Просто блистательно!!!
                                                                1. Паша 02 февраля 2018, 18:25 # 0
                                                                  Спасибо автору!!! Полезнейшая вещь.
                                                                  1. Павел 28 февраля 2018, 11:08 # 0
                                                                    А можно ближайшее метро получить?
                                                                    1. Maxim 28 февраля 2018, 15:58 # 0
                                                                      В текущей реализации макроса нет, т.к. возвращаемые результаты гугла/яндекса не содержат данную информацию. Только если через другой API. Могу доработать за плату по вашему заданию. Если актуально, пишите на info@excelstore.pro
                                                                    2. Марк 10 октября 2018, 19:52 # 0
                                                                      СПАСИБО ОГРОМНОЕ!!!
                                                                      ОЧЕНЬ ВЫРУЧИЛИ!
                                                                      1. Maxim 11 октября 2018, 09:25 # 0
                                                                        Пожалуйста