Получение адреса и координат из Yandex и Google
Краткое описание
Новая версия макроса для Excel (VBA) для быстрого и удобного получения гео-координат/адресов из Yandex и Google. В качестве исходных данных на листе указывается список адресов (для получения координат) или список координат (для обратного геокодирования). Результаты запроса будут выведены в соседние ячейки.
Подробное описание
Некоторое время назад на сайте был опубликован макрос для получения координат из Yandex. В текущей статье публикуем новый (доработанный) макрос для работы с координатами и адресами. Изменения в новом макросе: Помимо указанных изменений, новый макрос содержит ряд других улучшений кода, в той или иной степени влияющих на корректность результата. По опыту, при тестировании макроса сервис Yandex показал более точный результат определения координат. Google, в свою очередь, показал более высокую скорость работы. Также не забывайте, что оба сервиса хоть и служат одной и той же цели, но результат, полученный в каждом из них, немного отличается. Отличие не большое, но оно есть и, возможно, его стоит учесть. Поэтому, если этот момент вам принципиально важен, обязательно вручную проверьте результат в разных поисковых системах. Важно! В случае неработоспособности данного макроса, перед написанием комментария и/или отправки писем на мой адрес обязательно ознакомьтесь со списком основных проблем и способами их решения.
Несмотря на небольшой размер своего кода, макрос в удобном режиме позволяет по заданному списку адресов получать их geo-координаты (широту и долготу).
Для работы с сервисом Яндекса используйте лист "yandex", для работы с Гуглом - лист "google". Предыдущая версия работала только с Яндекс.
Это значит, что теперь не нужно получать ключ разработчика для корректной работы макроса. Теперь всё работает без KEY. Разумеется, общие ограничения сервисов по прежнему имеют место - это около 25 000 запросов в сутки с одного IP. При необходимости узнать более подробно о действующих ограничениях, следуйте по ссылкам: лимиты для яндекса, лимиты для гугл.
Для работы с обратным геокодированием необходимо соблюдать несколько важных нюансов, касающихся формата записи строки с координатами:
- для Yandex координаты необходимо указывать в формате (через запятую, без пробелов): долгота,широта
- для Google координаты необходимо указывать в формате (через запятую, без пробелов): широта,долгота
В приложенном файле с макросом на соответствующих листах есть примеры как для Yandex, так и для Google.
Статистика начинает отображаться после запуска макроса. Наблюдать статистику можно в строке статус бара Excel.
В предыдущей версии макроса типы были строго заданы как MSXML2.DOMDocument и MSXML2.IXMLDOMNodeList. Как следствие, возникали ошибки в процессе работы, если на ПК пользователя не было соответствующих библиотек "Microsoft XML".
Соответствующее значения API-ключа указывается на листе "Настройки".
В целом, для Yandex принципиально ничего не изменилось, а вот у Google с августа 2018 г. действуют новые условия получения API (необходимо регистрировать аккаунт Google Cloud Platform и привязывать к нему свою б/карту; после данных настроек суточный бесплатный лимит по-прежнему работает).
Подскажите пожалуйста, я работаю оценщиком и постоянно сталкиваюсь с такой проблемой: для оценки 1-ой квартиры мне нужно подобрать 5 аналогов, в конце своего отчета я всегда добавляю скрин яндекс карты расположения аналогов и оцениваемой квартиры, нанося их на карту вручную, помечая маркерами от 1 до 5, возможно ли написать код который будет считывать 6 строк с адресами в ячейках ексель и выводить их в виде интерактивной карты или статической…
Если вдруг не получили письмо, связаться со мной можно по адресу info@excelstore.pro
Столь необходимая программа для логиста.
Подскажите а есть возможность выведения всех геокоординируемых точек на карту.Готов оплатить данную услугу т.к пользоваться приходится постоянно
Если разработка актуальна, связаться со мной можно по адресу info@excelstore.pro
подскажите, как сделать, что бы долгота и широта выводились в одну ячейку без пробелов через запятую?
Вам будет достаточно поправить этот кусок кода:
Где вместо:
написать:
Уточните пожалуйста, в чём может быть причина результата конвертации «нет данных», в т.ч. по адресам-примерам в исходном файле.
Возможно, есть требования к формату адресов?
Примеры в приложенном файле вполне работоспособны. Только что еще раз проверил.
Формат строк для своих адресов соответственно можно указывать по аналогии.
Если же у вас не заработало даже на тех примерах, что в файле, то причины могут быть разные:
1) файервол или антивирус блокируют интернет соединение с яндексом/гуглом
2) на вашем ПК отсутствует библиотека MSXML2.DOMDocument
3) возможно что-то иное, исходя из специфики настроек вашего ПК/ОС/офиса
Уже и подключал разные версии MSXML и 3 и 6, проверял дебагером как отрабатывает функция (смутило что массив получается пустой). Доступ к интернету есть, ограничений нет (пробовал другой макрос для карт Гугл, он работает).
Может есть еще какие-то уже найденные нюансы из-за которых макрос не работает?
Проверять работу макроса рекомендую на адресах, указанных в файле. Если с ними работает, то проблем быть не должно. Макрос у меня и моих ближайших коллег работает. Некоторым людям дорабатывал макрос, никаких проблем с его работоспособностью также не возникало.
Попробуйте на другом ПК. Скорее всего, дело в существующих библиотеках в вашей операционке.
Действительно, причина была в настройках ПК.
Всё работает и очень гибко в части адресов.
Помогло решить задачку на работе :)
Так чтобы мне вывести не text, а все компоненты name в Component
Подскажите, пожалуйста, как получить «адрес Yandex» и «адрес Google» по-английски?
Для Google по аналогии, только параметр по другому называется. Детали здесь: developers.google.com/maps/documentation/geocoding/intro
Коллега поделился этой ссылкой со мной.
А можно ли посчитать дистанцию между двумя адресами?
А вы не знаете, можно ли перенастроить этот макрос, чтобы он работал с украинским языком? Сейчас он выдает все на русском и достаточно шумно ищет украинские адреса. Спасибо!
Для Яндекса попробуйте поменять параметр «lang» в запросе. Сейчас стоит lang=ru_RU, а вы укажите lang=uk_UA. Подробнее здесь: tech.yandex.ru/maps/doc/geocoder/desc/concepts/input_params-docpage/
Для Google по аналогии, только параметр по другому называется. Детали здесь: developers.google.com/maps/documentation/geocoding/intro
Помогите допилить код, пожалуйста.
Хочу избавиться от кнопки «получить координаты» и сделать автоматическое определение координат и адреса яндекс после нажатия Enter или при переходе в другую ячейку. Количество адресов для определения не большое: 1-10.
Если будут еще обращения с данной проблемой, возможно, доработаю макрос для возможности использования ключей. Но пока что вы первый за все время, кто написал об этом.
У меня список из 14000 адресов, к сожалению, за первый раз удалось снять с ключом около 3500 координат (гугл), хотя, ограничение гугла около 100 000 запросов в сутки и 50 запросов в секунду.
Также было бы хорошо проверять иногда, если это возможно, есть ли интернет и пропускать строки, для которых координаты уже найдены. (Сейчас скрипт пропускает пустые строки, вышел из положения сортировкой и удалением данных из строк с найденными координатами, предварительно сохраняя их в отдельном файле)
Благодарю за скрипт. Желаю всех благ.
Функционал по чеку коннекта и проверки дублей, подумаю, может и вправду добавлю, т.к. многие наверняка не удаляют дубли перед запуском макроса и не следят за стабильностью сети.
Кстати, если вас быстро забанили, чтобы не усложнять задачу проксями, попробуйте работу макроса через интернет мобильного телефона (разумеется, если трафик лимита позволяет).
На счет парсинга street view пока не думал. Не было задачи, где бы это могло бы пригодится. Вы для себя в чем видите полезность этого парсинга?
Подскажите, пожалуйста, как вставить в Ваш макрос еще и высоту?
И как поменять формат вывода координат на следующий 50°40'46,461«N 95°48'26,533»W 123,45m
2. Формат вывода, как вариант, написать отдельную процедуру, которая будет преобразовывать значения широты и долготы из финального массива GlobalArray в нужный вам формат.
Подробности по почте info@excelstore.pro
Сам файл, который вы скачали, — это и есть файл с макросом. Открываете. Если эксель выводит окно с запросом, разрешить выполнение макроса или нет, нажимаете разрешить. Собственно всё. Панель разработчика в данном случае вообще без надобности.
Соответственно, отвечая на ваш вопрос: переопределите столбцы в массиве GlobalArray.
На macOS почему-то пишет, что данные загружены, но ничего не прогружается в координатах и тд :( что делать?
спасибо!
Макрос изначально создавался под Windows. Работа макроса в macOS не тестировалась.
Что делать? Попробовать разобраться/поправить самому либо найти программиста под данную ОС и заказать у него доработку. Я под macOS не пишу.
Как можно подтянуть районы города?
Если в XML эта информация есть, то нужно доработать разбор XML и вставку на лист. Если же XML эту информацию не содержит, то внедрить в макрос сторонний сервис, из которого можно получать необходимые сведения (например, по ранее полученным координатам).
Код макроса открыт, можете попробовать доработать. Либо обращайтесь на почту info@excelstore.pro, обсудим стоимость платной доработки под ваши требования.
Можете дописать макрос что бы вычислялось расстояние между двумя точками (а-ля маршруты в яндексе или гугле)? Готов оплатить.
Да, это возможно. Для платных разработок обращайтесь на почту info@excelstore.pro
Низкий Вам поклон за тот макрос. Вы спасли от дибильной работы 4 человек.
Ситуация была следующая.
По работе откатываем с радиоизмерительным оборудованием территории.
Пробег в день по городу в среднем 200км, но это в режиме гугломобиля. Пробег регистрируется записываемым треком в *.gpx
Пока были на аутсерсе все отчеты по гсм были в норме, а когда нас взяли в штат конторы, то нарисовалась бухгалтер и начала умничать.
Типа мне нужен заполненный путевой лист, за весь маршрут, за каждый день. А как ты его будешь писать если ты ездишь по территории, а не от точки к точке.
Благодаря Вам, я по выдернутым координатам получил адреса, и заполнил путевку. Правда объем ее вместо 2 листов, получился 10 =)
Макрос по яндексу похоже сломался — у меня возвращает «нет данных».
Для google все работает норм.
для Яндекса и для Гугла
Не забывайте, что у Яндекса и Гугла есть ограничение по количеству запросов в сутки. Возможно, с вашего IP данный порог был превышен.
Отличная разработка, спасибо большое!!!
Такой вопрос: Есть кто сможет безвозмездно помочь немного доработать данный макрос??? Необходимо ввести несколько параметров:
1.добавить функцию подсвечивания ячейки, в случае если введенный адрес имеется и в других районах (допустим: моем городе адрес Абая 45 имеется в 3 районах.
2.добавить ячейку, в котором указывается страна или город для поиска
и т.д. по мелочи(-_-)
Попробуйте на форумах по Excel или VBA запостить ваш вопрос. Больше вероятность, что кто-то откликнется. Либо можете попробовать самостоятельно внести необходимые правки. Макрос у вас есть, его код открыт.
Вопрос, Возможно ли сделать так чтобы координаты гугл выводились не a формате только цифры а с буквами долготы и широты?
Это легко. Код макроса открыт. Вам нужно немного скорректировать формат записи значений в финальном массиве. Перед тем, как произойдет запись данного массива на лист.
Там не много процедур и функций, попробуйте самостоятельно подправить. Будут вопросы — спрашивайте.
Просит либо пытаться дальше повторять попытки, либо указать другой путь к папке, содержащей пакет установки click2run.msi
В чем может быть проблема?
О какой установке идет речь?
К тему прикреплен файл архива в формате zip. В архиве обычный экселевский файл. При его открытии ничего доустанавливать не надо. Разумеется архиватор и эксель в вашем windows должны присутствовать.
На вкладке Яндекс — все нормально…
Как вариант, можете попробовать добавить ваш API KEY гугла в код макроса.
Проверьте по чек листу: excelstore.pro/faq/solving-problems-with-parsing-coordinates.html
Подскажите, возможно ли при обращении к той же API возвращать наименования организаций по введенному адресу?
Спасибо!
Можно ли доработать макрос, чтоб он выводил расстояние между населенными пунктами?
Чтобы расстояние считалось не по прямой, а с учетом дорог.
Готов заплатить за эту доработку.
Сейчас очень мало свободного времени под доработки. Если вопрос не срочный, напишите мне на почту info@excelstore.pro Как только появится окно, обязательно с вами свяжусь и обсудим детали.
А так, код макроса открыт. Без проблем можете взять его и попробовать доработать через фриланс или иные биржи онлайн работы.
Подскажите, пожалуйста, почему может не работать Google, в то время, как Yandex работает на ура!
Вижу, что говорили о API KEY, его ввести надо было видимо в макрос, но есть проблема: я вообще впервые столкнулся с API, Макросами и т.д. Поиски в интернете даже не вывели меня на получение этого API KEY. Прошу помощи, т.к. Поиски Google крайне необходимы. Заранее благодарен!
Да, дело в API KEY, который нужно получить и добавить в макрос.
Расписывать подробнее не буду, т.к. в любом случае все сводится к доработке макроса.
У меня сейчас нет свободного времени, попробуйте опубликовать ваш запрос на сайте фриланса. Там небольшая доработка.
Кто нибудь может помочь? Не работает поиск по Google, раньше все работало.
Может есть простое решение?
Заранее благодарю.
Решение — получить свой API ключ в гугле (получается бесплатно) и прописать полученный ключ в макросе, а точнее в строке, где формируется запрос гуглу.
я глюч зарегистрировал а куда вставить не пойму
Спасибо
excelstore.pro/examples-of-work/internet/get-the-address-and-coordinates-of-yandex-and-google.html#comment-409
Мой макрос в этом не поможет. Максимум — выдаст вам координаты по вашему списку адресов (крайних точек Москвы либо МО).
А так Яндекс вполне успешно выводить соответствующий полигон при запросе на картах (Москва либо МО). Остается только понять, как вытянуть эти координаты. Если у вас браузер хром либо мозилла попробуйте через панель разработчика (вкладка Network) вытянуть эту информацию.
Спасибо за файл.
Яндекс работает, а Гугл — показывает пустые строки.
Знаю там правила доступа без ключа изменились. Куда ключ можно вставить чтобы заработала вкладка гугла?
Сайт отличный, куча интересного, спасибо
Спасибо за отзыв.
Сегодня/завтра поправлю макрос, чтобы можно было использовать ключ. По результату отпишу здесь в комментариях.
Ссылку опубликовал в исходном посте выше (блок «Альтернативная версия файла»). В данном файле есть возможность на листе указать соответствующие API ключи (без необходимости корректировать VBA код).
Спасибо большое за программу, очень полезная.
Я хотел уточнить по гугл картам, я правильно понимаю, чтобы координаты начали корректно выгружаться, мне надо активировать платный аккаунт в гугле? Просто сейчас он не активирован и получение координат не происходит(
Да, все верно. Примерно год-два назад гугл ввел новые правила использования своего API. Таким образом, чтобы макрос работал, нужно активировать платный аккаунт гугл. В этом аккаунте видно в том числе количество использованных запросов. Если суточный лимит в 25000 запросов не будет исчерпан, то сервис бесплатный, иначе будут списания с привязанной карты.
сегодня еще раз попробовал, все тоже самое, скриншоты к сожалению не могу приложить
Есть какой то кошелек или еще какой то способ отблагодарить за данную программу?
P.S. Я же правильно понимаю, что в дальнейшем когда я настрою на своем сайте гугловскую карту, мне в любом случае API этот еще пригодится там, потому что без него, карта не будет работать?
42.12745,19.12745
42.295149,18.844295
42.286973,18.881717
42.276535,18.89777
Попытался воспользоваться вашим файлом, в адреса выдало саудовскую аравию, хотя координаты черногории, как быть?
18.84584,42.296752
19.12745,42.12745
Впрочем, если подойдет маршрут по прямой между двумя точками, то доработка очень простая. По большей части, добавить математические формулы.
Если же маршрут нужен по дорогам, то тут либо какой-либо доп сервис задействовать для расчета соответствующего расстояния либо заморачиваться с яндексом и html/js.
Давно хотел сделать, но все времени нет. За плату, если хотите, могу сделать. Если интересно, напишите на почту info@excelstore.pro
Возникла такая же проблема, как и у многих отписавшихся выше… макрос версии v.3 загружает часть данных, оставшаяся часть остается «без данных»… удаляю определившиеся, по новой прогоняю «без данных» и снова определяется только малая часть, т.е. с исходным адресом все в порядке, по отдельности каждый из них геокодируется. Версия v.2 вовсе выдает ошибку «13» type mismutch (все строки в коде менял по инструкции, ключ ставил свой, буквально на днях все работало корректно). Дело, думаю, и не в лимите яндекса… во-первых прогонял всего 2к строк, во-вторых с другого ip и с другим ключем, и спустя несколько дней, проблема никуда не девается;(
Оооочень нужен данный инструмент… В чем же может быть причина?
P.S. Есть ли возможность добавить OSM Nominatim, и пишите ли Вы на питоне (нужен плагин для QGis для пакетного геокодирования таблиц xlsx, xml)?
Макрос рабочий. По крайней мере, яндекс точно (и без ключа). Только что проверил.
Версия v.3 (она же выложена в шапке темы) может выдавать указанную ошибку, т.к. в редакторе VBA в Referens прописаны ссылки на библиотеки, к-ых на вашем ПК, возможно, нет (зависит от версии офиса). Измените Referens на корректные, исходя из вашей версии офиса, и всё должно быть нормально.
Если у вас макрос ранее работал, а сейчас нет, то это странно. Код макроса один и тот же и от запуска он не меняется. Возможно, дело все-таки в блокировке вашего IP/API.
OpenStreetMap в теории добавить можно. Но пока особо нет времени на это. Да и не уверен, что это особо кому-то нужно (кроме вас). К сожалению, посетителей у данного сайта мало.
На питоне пишу несложные скрипты.
Пользуюсь Вашим макросом на протяжении 2 лет, сам занимаюсь логистикой и макрос необходим для геокодирования большого количества точек, для последующего нанесения на карту.Сегодня при запросе выдал «Нет данных».Офис 2007.Подскажите как поправить ситуацию.Заранее благодарен.
Два года… Прилично так. Я уже и забыл, что сайту не первый год минует.
Если гугл, то там уже нужен апи ключ. Новые правила гугла.
Если проблема с яндексом, то скорее всего лимит с вашего ip превышен. Для теста расшарьте wifi c телефона и проверьте под другим ip.
Пробовала версию с ключом на тех же данных что в файле и на своих данных тоже.
Говорит run-time error 9, subscript is out of range.
Есть ли опция выложить в zip? rar не стоит на корп. компе.
Альтернативная версия макроса по ссылке в посте (где ключ указывается) как раз-таки в zip.
В последние пару дней, когда я пытаюсь геокодировать около 10000 русских адресов с помощью вашего GeoYandexGoogle, v.3, используя Яндекс.Карты, я получаю только географические координаты первых 500 адресов, для остальных появляется сообщение «нет данных».
Я работала с GeoYandexGoogle v.3 несколько раз за последние месяцы, и проблем не было. Также проблема не может быть в адресах или в ограничениях Яндекса (он установлен на 25000 запросов). Есть ли у вас какие-либо подсказки? Может ли быть так, что в Яндекс-картах теперь требуется ключ API?
Спасибо за ваше время и за то, что вы сделали GeoYandexGoogle v.3 доступным для всех нас!
Очень крутой файл, есть над чем подумать и чему научиться, Спасибо!
Начал с ним работать только на этой неделе и у меня такая же проблема, как у Катерины — нужно дынные по 7т. адресам, но данные подтягиваются только к первым 500 адресам, потом 'нет данных' и проскакивают одиночные данные. Если сразу запустить макрос, то сразу 'нет данных' и какие-то одиночные данные, спустя час отдыха подтягиваются примерно 200 адресов и 'нет данных'. Такая ситуация и на рабочем ПК и на домашнем. Такое ощущение, что количество запросов сократили до 500 за раз. Может кто знает точно, подскажите.
Но файл действительно суперский!
Если у вас IP динамический, значит, превышено общее кол-во запросов к яндексу. Как варианты:
а) попробуйте со статический IP
б) попробуйте версию макроса с указанием полученного вами API
в) попробовать альтернативные яндексу геокодеры, вплоть до разворачивания своего сервера для обработки, чтобы исключить блокировки (в этом случае придется еще тестировать достаточность установленных баз до необходимых вам адресам).
Это отличный макрос.
К сожалению, перестал работать даже с небольшим количеством адресов.
В 90% случаях пишет «Нет данных».
Есть решение для неопытных пользователей?
Посмотрите мой комментарий выше. Ссылка:
excelstore.pro/examples-of-work/internet/get-the-address-and-coordinates-of-yandex-and-google.html#comment-479
Особенно обратите внимание на пункт про использование версии макроса с API.
Только что проверил, без API блок наступает после примерно 40 строк. Это можно обойти, если увеличить паузу между запросами. Но это не удобно. С API же я без проблем получил данные по 1000 строк (без какого-либо увеличения времени на паузу).
Так что — все работает.
Читайте инструкцию/описание и пользуйтесь на здоровье. API в яндексе можно получить бесплатно.
Разобрался, большое спасибо!
Не понимаю, как получить API ключ. Уже всю голову сломал, из предыдущего поисковика нашел тему и ссылку yandex.ru/dev/locator/keys/get/ на какой-то локатор, это единственное место где мне удалось увидеть ключ длиной которая бы подходила. далее в кабинете JavaScript API и HTTP Геокодер в настройках дает ключ совершенно другой, и он не подходит — c1d8bcf7-daa1-4b23-be11-298a6148310e. Пытаюсь использовать файл 4, где нужно подставлять ключ на первой странице. ничего не помогает. Данные не выдает. Какой ключ корректный? Как в желтое поле в файле вставить ключ, чтобы он заработал? Какой ключ нужен? Помогите разобраться.
API от Локатор, конечно, не подойдет.
Нужный вам ключ можете получить здесь: developer.tech.yandex.ru/services/
Предварительно не забудьте авторизоваться в почте яндекса. При подключении из списка выбирайте «JavaScript API и HTTP Геокодер».
developer.tech.yandex.ru/services/
API интерфейсы ?
JavaScript API и HTTP Геокодер
Предоставляет ключ вида 54576088-f3eb-4013-a834-cca822b8ccd7,. С ним так же возвращает — «нет данных»
JavaScript API и HTTP Геокодер — да, он самый.
У вас что-то с API ключом. Проверил макрос с моим ключом — все работает. Проверяю с вашим — большая часть значений без данных. Посмотрите статистику по вашему ключу в кабинете яндекса, может по нему лимит суточный уже исчерпан.
Я же правильую версию использую…
Макрос да, корректный. Ключ нужно указывать в ячейке B2 на листе «настройки». Там ошибиться сложно.
PS. API ключи лучше не публикуйте в интернете. Т.к. любой их потом может найти и применить для себя. При этом суточный лимит запросов действует не на ключ, а на аккаунт.
Если какие-либо есть существующие ключи, заблокируйте лучше их и создайте себе новый.
подскажите чайнику, пожалуйста, куда добавить критерий kind=districs. Очень нужно, чтобы в адресе также выводился район.
Спасибо.
iURL = «geocode-maps.yandex.ru/1.x/?geocode=» & iAdress & "&format=xml&lang=ru_RU&results=1"
Если же хотите из XML получать какие-либо новые значения (к-ые макрос сейчас не выводит), то еще дополнительно нужно править блок «With XMLDoc.DocumentElement» и в основном макросе StartMacro скорректировать массив вывода.
Если хотите, можете подписаться на мой ютуб канал и поделиться ссылкой с друзьями и коллегами :)
Последнее время новых видео не много, но в меру сил и наличия свободного времени стараюсь публиковать.
Ссылка если что ниже под блоком комментариев.
Как лицензионное соглашение соблюсти или как карту показать в эксель с кластерами?
Если нужно распознавание именно Google, то там также без ключа не будет работать. Вы указывали ключ?
Похоже, перестал работать поиск по элементам XML-ответа Яндекс — .SelectSingleNode() возвращает пустое значение, при этом в .XML полный ответ от Яндекс.
Возможно, связано с обновлением библиотеки MSXML2.DOMDocument и требованием к NameSpace внутри XML-ответа
Чтобы заполнить координаты пришлось менять
strXML = .SelectSingleNode("//Point/pos").Text
на
strXML = .ChildNodes.Item(0).ChildNodes.Item(1).ChildNodes.Item(0).ChildNodes.Item(4).ChildNodes.Item(0).Text
К меня Mac-а нет, поэтому проверить не могу. На Windows все работает как и прежде.
А сможете подсказать как заполнить не только координаты, но и поле с адресом, как его возвращает Яндекс?
у меня mac os с последним excel, когда запуска макрос, выходит сообщение «Данные успешно загружены». но по факту все поля остаются пустыми что в яндекс, что в гугл…
Что нужно сделать, чтобы система заработала?
Посмотрите ответ Анатолия выше. Возможно, поможет в Вашем случае.
Есть небольшой вопрос — по несколько строк все отрабатывает. Но если через вставить несколько сот строк, то как правило, выдает «нет данных».
Если макрос понравился, можете подписаться на мою групп в YouTube.
По Вашему вопросу.
Используйте альтернативную версию макроса (v.4).
В данной версии есть возможность указать API. С указанным API работает нормально.
Возможной какое-то ограничение на частоту запросов. Где в макросе можно попробовать поставить задержку между обращениями?
Скачивайте альтернативную версию макроса (v.4).
В данной версии есть возможность указать API. С указанным API работает нормально.
По версии 3 хочу сказать, что работает странно, на двух IP адресах везде «нет данных», а с другого устройства один раз и не полностью отработал кажется яндекс, потом снова «нет данных». По 4-ой версии — не имею ключей.
Проанализировал код, выяснил как выглядит строка запроса и вставлял прямо в браузер. В Г и Я ответ один — нет доступа\много запросов. Как я понял политику Г — без ключа никак. Я — в скором времени прекратят анонимный доступ, т. е. тоже только с ключами.
Предложения:
Вместо «нет данных» выводить более информативные значения, что не просто нет данных, а именно нет доступа, приобретите ключ…
для Г:
//GeocodeResponse//status: REQUEST_DENIED
//GeocodeResponse//error_message: You must use an API key…
Для Я:
//error/error: Too Many Requests
//error/message: You are making too many requests to Yandex Maps API Service…
Заголовок вместо широты\долготы, а сообщения вместо адреса.
Также заметил «If XMLDoc.Load(iURL) = True Then» в модуле Г срабатывает, а в Я почему-то нет, когда как разница строк не большая, возможно играет роль "): XMLDoc.async = False" — в одном месте через :, в другом просто ниже строкой и в Dim тип есть, а где-то нет… потому в Я выше указанное предложение работать не будет, т. к. почему-то не считает страницу типа XML и не считывает данные параметры и их значения.
p.s. пришлось ручками узнать более 90 координат :((
Спасибо за Ваш развернутый комментарий.
При последующем обновлении макроса постараюсь учесть Ваши замечания.
На счет работы макроса. С недавних пор (год-два назад) работать без ключей стало проблематично. Яндекс возвращает результаты частично, а работа с Гугл без ключа вообще невозможна теперь.
Версия 4, если указать ключи, работает отлично. Ключ для Яндекса получить очень просто. Для Гугла получить ключ немного сложнее, но это не самое плохое — хуже, что теперь там надо обязательно привязывать кредитную карту. Бесплатные дневные лимиты есть и там и там, но когда кредитка подвязана, как-то не спокойно.
Постигла меня судьба наткнуться на ваш чудо макрос, имею ключ «Я», ввел его в версии 4, и в итоге во все ячейки выводит «нет данных».
Может придумали решение?! ОНО было бы сейчас очень уместно:!
В листе «Настройки» ввел свой API ключ Яндекса «trnsl.1.1.20191025T113300Z.9f6a57a64d78fa6c.4d515b14b48cdbfb415b0174f247b009ac5fbf7a» (4 версия файла).
На листе «Yandex» нажимаю на кнопку «Получить координаты» и в ячейках B2:D15 появляется сообщение «нет данных».
Подскажите, пожалуйста, в чем моя ошибка?
Подумал минут 10, прикинув х*й к носу, почитав код макроса я понял что формат ячейки куда вводите API должен быть «Общий».
1) Заходим в лист «настройки»
2) Вставляем API
3) Меняем формат ячейки на «Общий»
4) Нажимаем кнопку «Получить координаты»
5) Радуемся и благодарим святого автора данного макроса — Максима!!!
Что значит вставляем API
Дай бог здоровья вашим носу и х… ю!
Заработало-таки! Спасибище!
Есть возможность выложить на гугл диск или тут?
Спасибо.
см правила tech.yandex.ru/maps/geocoder/doc/desc/concepts/limits-docpage/#index__conditions
теперь яндекс не отдает просто так (403ForbiddenInvalid key)
Нужна web страница, принимающая массив адресов, отображающая их на карте и отдающая обратно в эксель xml с координатами.
К сожалению, не получается найти координаты. С ключем Яндекса и без. Пишет, что нет данных.
Только что проверил. Скачал свой же макрос отсюда, подставил ключ, прогнал 1200 адресов и все работает.
Проверяйте у себя.
А что еще может пойти не так? Тоже пишет нет данных.
Тоже нет данных, офис 19, система 64 битная.
Кстати, о какой активации речь? Я сколько ни создавал, API всегда был активен (работоспособен) сразу после его создания.
Возможно речь об этой активации: tech.yandex.ru/maps/geocoder/doc/desc/concepts/about-docpage/. Здесь внизу страницы пишут «Примечание. Ключ будет активирован в течение 15 минут после получения.»
Сегодня впервые получил API-ключ здесь: developer.tech.yandex.ru/services/. Выбрал пункт «JavaScript API и HTTP Геокодер». Вставил ключ в версию файла v.4. В личном кабинете Яндекса отображалась зелёная надпись, что ключ активен. Там же повыше была серая надпись «Израсходовано—». Видимо это надпись про лимит 25 000 обращений. Но макрос на все адреса выдавал «нет данных» и через 15 минут и через час.
Заработал макрос спустя часа три после получения ключа. Для 100 адресов успешно получил координаты. В ЛК Яндекса изменилась надпись на «Израсходовано 0» (вместо прочерка появился ноль). Интересно, что и спустя 3 часа, заработал макрос не сразу. Поначалу искал координаты выборочно, то для одних, то для других случайных адресов. С каждым разом для большего количества. Так за 5-10 запусков подряд макрос заработал в полную силу. Теперь при каждом запуске получает координаты всех адресов.
В Яндексе так надпись «Израсходовано 0» и осталась. То есть статистика не ведётся, хотя координаты получил через макрос раз 20. Возможно задержка в обновлении статистки Яндекса.
Я тестил макрос неоднократно, подобного не замечал. А на счет изменения «израсходовано», то это значение все-таки меняется, примерно в течение 1-2 часов идет обновление. Я правда на штучных значениях не проверял, но когда гонял макрос на тысячи адресов, то там это видно.
Будут вопросы, пишите.
гугл не проверял.
После уведомления Данные успешно загружены- ничего не происходит...., подскажите в чем может быть проблема
IP не заблокирован — это уже хорошо. Единственное, что приходит на ум — неправильно указан ключ в поле настроек экселя (может пробелы попали или еще что).
в токене изменил 1 символ, записано значение в поле В2, без тире тоже пробовал-не работало.
На счет «изменил 1 символ» — не понял, зачем его менять?
Ваш токен проверил в макросе. Отлично работает, данные в файле отображаются.
У вас есть объяснение этому
geocode-maps.yandex.ru/1.x/?apikey=cac98c74-acf3-4406-9353-e398aea4c82a&geocode=68.8643,32.9937
в ХМЛ получаем — Афганистан, провинция Пактика
но если заЯндексить 68.8643,32.9937 то городское поселение Кола вблизи Мурманска
а еще некоторые поля часто бывают пустыми.
Какое ваше мнение на этот счет?
excelstore.pro/faq/solving-problems-with-parsing-coordinates.html
Подскажите как можно добавить что бы макрос писал ближайшие станции метро?
Спасибо за Вашу работу, но есть три вопроса:
— как использовать его в гугл таблице?
— как сделать автоматическое исполнение скрипта при появлении новых данных в таблице?
— что надо установить на комп, чтобы появились координаты, тк у меня при нажатии на кнопку пишет Данные успешно загружены, но в таблице либо ничего нет (гугл), либо «нет данных» в Яндексе?
стоит Win7 pro и офис 2016
скриншоты здесь
photos.app.goo.gl/62JWvo1sPdsGckvt8
1. Никак. Разве что полностью перепишите данный макрос с VBA на JS.
2. Добавить в существующий макрос код, который будет при определенном событии (в вашем случае, это появление новых данных в таблице) запуск основной макрос для определения координат.
3. Ответ здесь: excelstore.pro/faq/solving-problems-with-parsing-coordinates.html
Ребят, можете помочь? Есть 12 тыс. адресов в формате индекс/город/улица/дом.
Все отлично работает, но на выходе я получаю ширину и долготу другой страны, хотя в списке только города России.
Если кто-то чем-то поможет, то буду очень благодарен.
Если проблема на всех адресах (в т.ч. что я указывал), то помочь не могу.
Если проблема только в ваших адресах, добавьте в начале адреса Россия. Если проблема сохранится, пришлите мне для примера какой-нибудь адрес. Почта: info@excelstore.pro
Подскажите почему на полученной карте в списке слева и на карте при выделении отметки вместо букв-квадратики?
Не очень понял, о чем речь. Расширение приложенного файла было и есть xlsm. Его менять без особой нужды не нужно. Но раз работает, отлично.
If XMLDoc.Load(iURL) = True Then With XMLDoc.DocumentElement strXML = .SelectSingleNode("//Point/pos").Text r = Split(strXML, " ", , vbTextCompare) tmpArray(1) = r(0): tmpArray(2) = r(1) tmpArray(3) = .SelectSingleNode("//metaDataProperty/GeocoderMetaData/text").Text End With Else tmpArray(1) = "??? ??????": tmpArray(2) = "??? ??????": tmpArray(3) = "??? ??????" Application.Wait Time:=Now + TimeSerial(0, 0, 0.5) '????? 0.5 ???. End IfСтрока iURL с ключем яндекс АПИ формируется нормально (если в браузере ее ввести то ответ яндекс выдает нормальный), но загрузки XMLDoc.Load(iURL) не происходит (False) и, соответственно, строки заполняются «нет данных». В чем может быть дело?Пришлите мне ваш эксель файл со строкой адреса, на которой ошибка, я посмотрю.
Что Яндекс, что Гугл сейчас без ключа не работает.
С Yandex все работает четко, проверял на прошлой неделе.
Возможно, API ключ не валидный. Пришлите несколько адресов для проверки (которые у вас определяются как «Нет данных»).
Россия, Москва, 1-я Тверская-Ямская, д.2 стр.1
Россия, Москва, Смоленский б-р, д.1/2
Россия, Москва, Зеленый пр-т, 54А
Россия, Москва, ул. Грекова, д.8
Россия, Москва, ул. Маршала Бирюзова, 14
Россия, Орехово-Зуево, Привокзальная пл. 5/1
Россия, Москва, ул. Малая Грузинская, д. 38
37.597118,55.770284
37.584649,55.738916
37.812794,55.751913
37.66256,55.886448
37.493219,55.793991
38.974891,55.796101
37.573025,55.76797
улица Энгельса, 22, село Поляны, Полянское сельское поселение, Рязанский район, Россия
А как сделать так, чтобы api ключ стал валидным?
В общем, еще раз сейчас проверил — макрос нормально отрабатывает.
Дело в вашем API.
Ничего специально с ключом делать не надо. Просто получаете его, как я показывал здесь: www.youtube.com/watch?v=HlHWwCA53yY
После получения ключ может быть активен не сразу, а спустя 1,2-5 часов. Бывали такие случаи. Основные причины, по которым макрос не работает я описывал здесь: excelstore.pro/faq/solving-problems-with-parsing-coordinates.html
Сделал все по инструкции, однако все равно макрос выдает «нет данных».
Win 10, Excel 2019.
Помогите решить проблему.
Все тестовые должны же находиться адреса? Или все верно?
Россия, Москва, 1-я Тверская-Ямская, д.2 стр.1
Россия, Москва, Смоленский б-р, д.1/2
Россия, Москва, Зеленый пр-т, 54А
Россия, Москва, ул. Грекова, д.8 ул. Грекова, 8, Москва, Россия, 127224 37.6625584 55.8865774
Россия, Москва, ул. Маршала Бирюзова, 14 Маршала Бирюзова ул., 14, Москва, Россия, 123060 37.4933269 55.7939510
Макрос выдает ошибку:
Run-time Error '9'
Subscript out of range
Подскажите, пожалуйста, как это победить?
халявный ключ работает у меня один раз в сутки — почти 2000 точек.
с утра опять еще разок можно файл на выгрузку готовить
Россия, Мантурово, д Хмелёвка Россия, Московская область, городской округ Лотошино, деревня Хмелевки
Россия, Мантурово, д Береговая Россия, Липецкая область, Хлевенский район, деревня Подгорное
Относятся к Костромской области. Как изменить запрос?
и второй вопрос- можно ли рядом с полученными координатами выводить информацию о почтовом индексе?
Одно пожелание — для Яндекса поменять местами колонки с широтой долготой- у них в шаблоне:
Широта Долгота Описание Подпись Номер метки
— а то я не обратил внимания и тупо долбился в открытую дверь )
Здоровья вам, ребята!
У меня был очень «грязный» список адресов, где помимо этих адресов в строках было куча комментариев и прочего «синтаксиса» — на УРА! отсев
Далее отсортировал и заново просеял пустые поля (их было 10 % при таком-то мусоре) — ОГОНЬ!)
Спасибо! На канал подписался, люблю автоматизацию без погружения в технические дебри. )) Только точный копипаст для задач «под вчера»
Ключ вставил (05c3c14d-bf18-4a3c-9a81-7c17b8cce767)
а ему что то не нравится. " apiYandex = Sheets(«настройки»).Cells(2, 2).Value"
Спустя 2 часа.
Удивительно, всё заработало, видимо на яндексе обнулился счетчик запросов )))
Подскажите, почему в google отрабатывается список адресов (видно обработке строк с таймаутом ошибки), но поля остаются пустыми? Хотя должно появляться «нет данных»
Пытаюсь добавить OSM Nominatim, то же самое — пустые поля, ошибок нет.
Precision EXACT / NEARBY / NUMBER / RANGE
Ваш макрос крайне полезный!
Но к сожалению Яндекс сделал ограничение
В API установлен суточный лимит на использование сервисов геокодирования, маршрутизации и панорам Яндекса. Для бесплатной версии API лимит запросов к HTTP Геокодеру составляет 1 000 запросов в сутки, а лимит использования JavaScript API — 25 000 запросов в сутки
Можете ли вы изменить ваш макрос, чтобы он использовал метод JavaScript API? А то ткущий метод дает блокировку спустя 1000 запросов.
Думаю, все будут благодарны
Ваш макрос крайне полезный!
Но к сожалению Яндекс сделал ограничение
В API установлен суточный лимит на использование сервисов геокодирования, маршрутизации и панорам Яндекса. Для бесплатной версии API лимит запросов к HTTP Геокодеру составляет 1 000 запросов в сутки, а лимит использования JavaScript API — 25 000 запросов в сутки
Можете ли вы изменить ваш макрос, чтобы он использовал метод JavaScript API? А то ткущий метод дает блокировку спустя 1000 запросов.
Думаю, все будут благодарны
Нажал кнопку на вкладке Яндекс «получить координаты»
Во всех столбцах выдает статус «нет данных»
Что делаю не так???
Office профессиональный плюс 2016
ОС ПК: Windows 10 Корпоративная 2016