Excel Google Maps Distance and Travel Time Calculator with Directions API
Vložit
- čas přidán 24. 07. 2024
- You can use the Google Maps Directions API to calculate travel time and distance using a number of travel modes. You can used VBA-JSON to create an Excel function that provides this data in Microsoft Excel! In this video, we create both travel time and travel distance functions in Excel VBA.
Links:
Written tutorial: syntaxbytetutorials.com/excel...
VBA-JSON: github.com/VBA-tools/VBA-JSON
Directions API Start (Get an API Key): bit.ly/googlemapsgettingstarted
Recommended book: worldly.link/go/rqlppu - Věda a technologie
This tutorial was very helpful. I had a 1000+ addresses I had to evaluate for a school project, and as much of a learning curve as this was, it was still infinitely easier than trying to do it manually. As others have suggested, changing Dim Seconds and Dim meters from "Integer" to "Long" solved the problem of longer distances not calculating. Thank you so much for the guide!
THIS WAS AWWEEESSSOOOOMMMMEEEEE!! Excellent job! Thank you. I changed the ("duration")("value") to ("duration")("text") and it gives me distance in Miles!
did you do anything else besides cause it from value to text. when I tried it I get a #value error
This was sooooo super! I was able to create a sheet that tells me how much drive time I need to allot in order to get to my next facepainting gig on time! YOU ARE AWESOME!!! thank you
2 important points: 1 the difference you are getting in the applications verses the website appear to be driven by time of day. Try on web saying you are leaving at 3AM. 2 - I was doing a larger distance, and kept having an error. The Dim for meters needs to be long as my result was more than 64K.
Oh and thank you VERY much this was a fun Sunday learning, I really appreciated it.
Please pin this, it helped a ton. Was getting an error, and this fixed it.
Switching from Integer to Long solved my problems too. Great advice
Thank you for the explanation! Super clear and useful!
This works like a charm. I wanted to suggest changing "Integer" to "Long" as returning meters and seconds may end up exceeding the character limit for Integer. Alternatively you can write a conversion in the VBA.
Additionally, this seems to now violate Google's ToS for API as it is considered data scraping.
Interesting point about the ToS. I certainly wasn't intentionally showing something that would violate the ToS, although I don't believe the usage shown in the video does. Google defines scraping as "Customer will not export, extract, or otherwise scrape Google Maps Content for use outside the Services. For example, Customer will not: (i) pre-fetch, index, store, reshare, or rehost Google Maps Content outside the services; (ii) bulk download Google Maps tiles, Street View images, geocodes, directions, distance matrix results, roads information, places information, elevation values, and time zone details; (iii) copy and save business names, addresses, or user reviews; or (iv) use Google Maps Content with text-to-speech services." Probably the closest thing users would use this for is bulk downloading, but I'm not sure at which point you reach "bulk downloading". If you're doing 10 addresses, probably not. If you're doing 1000 addresses, well that may be a different story. Either way, I'm pretty sure all Google's going to do is shut off your API access.
Thanks for the tip, I had issues with the distance and Long did the trick!
Long solved my problem. If anyone else has long distances fail to calculate, this is the solution
hi there want to say thank you very much for this video. it was tremendously helpful for what i am trying to do. I will also tell you that i know not much about coding and vba etc but i just followed us instructions and got the result i needed. thanks for such a detailed video, thanks for your time, and thanks to the people below for commenting on some of the issues they had, it also helped me fix some issues.
This is awesome! Can you advise if I can add the "Depart at" value which includes the time and date? I need to know for example how long will it take me from PointA to PointB if I left PointA on March 15th 2022 at 3pm
Thanks alot mate, really helped me in simplifying one of my analysis containing 22K rows of data, which I had to otherwise do manually
Thanks a lot. I got it working in an hour. Saving me some real time. Well done.
Can u share it with me please ?
awesome work and description! thanks
Hi there, Your video is really a good one and you describe all the process very clear.
But after I try to use it on excel, it continuously asking for macro reference. As if the macro doesn't exist.
can you help me with this error?
Hi, i tried your code and get #value when tried the traveldistance and traveltime. I think its because the json format is different now, when i tried to open the link in browser there is no "leg" in there. can you help?
Thank you so much for this incredible helpful video!
Good day, Thank you for your video. I have general computer skills and was able to follow the steps and get it working. I was wondering if there was a way to modify the code so that it pulls the public transportation time and distance instead of driving for a project I am working on?
Should just be a matter of adding &mode=transit onto the URL in the code. You can find some more information here: developers.google.com/maps/documentation/directions/get-directions#TravelModes
This was SO helpful! How would you modify the code to display only the shortest travel distance? I also need to figure out a way to display the associated map for each record. Appreciate the guidance.
Hi there, thank you so much I found this very useful. Does anyone know what syntax I should use to create a flag if a Toll Road is used? I can see that "\u003eToll road\u003c/div\u003e" is returned in a "html_instructions" when a roll road is used. Or if it the code module could add 1 each time it finds the Toll Road used for directions? e.g if in 3 HTML_instructions '3' would be returned. meaning from point A-B 3 toll roads are used. Perhaps using the instr function?
Hey,
Have you found an answer to this yet?
I'm trying that too, I saw that the Google Directions API has instructions for Tollinfo but I still can't get the function right in the VBA module
Excellent video on how to do this! Some tips that helped me complete my worksheet were:
-For TravelDistance showing VALUE, remove "Dim meters As Integer" entirely
-Replace Integer with Long if you have far distances
-To convert seconds to an hour/minute format, add to the end of your TRAVELTIME code /86400 and then change the cells from General to Time. For example, this should make a route go from 9000 seconds to "2:30" (2hr, 30min)
I still have an error. I did transform the function in Sub to debug it. The problem appears in this line: "Set parsed = JsonConverter.ParseJson(response)". Any help would be appreciated.
@@FredCyrdotcom Did you find a solution for this? :/
Thanks so much for this great video. I'm having a small problem with my API - for some postcodes, it returns the travel Time but not the Distance. Do you know why this would be?
Apart from this, this was so straightforward and I know nothing about APIs, so I truly appreciate the help!
It is like a magic ... Super usefull & Super thanks !!!
You rock like Buddy Holly, man.
About 3 years later: March 26, 2023.... Could it be that despite the icon order for the methods, the walking distance was listed first in the JSON file? I don't know if you looked down the file to see if there was a "2.4 miles" vs the "1.7 miles" listed in the screen view.
Hiya,
Thank you this has really helped. I have read some comments and have sorted the LONG issue but now when trying to distances I get a #NAME error when using the distance. I can't figure out what I'm doing wrong :( any advice?
Thanks for the video! Interestingly enough, for longer routes (e.g. 200KM), I'm getting #N/A error in Excel (shorter ones are OK). Any idea what's that about? According to the JSON file, Google Maps provides the data, but feels like some parse error. Dim meters as Long didn't help either.
A bit late but maybe it still helps, I had the same problem, you have to change dim meters as Double. Float etc. are not possible in VBA
Thank you for sharing your knowledge, does this work the same way on Mac OS?
Edit: Got this exact method to work on my M1 mac by virtualizing windows through Parallels(it has a 14-day trial). If you get the value error then you probably need to enable either the directions or distance API, I just enabled both to be sure.
It should work as long as you follow the instructions to install VBA-JSON correctly on Mac. However, there are other users in the comments that have reported problems and I've never personally run the script on Mac.
Hi again. I'm not getting the ferry thing to work. Do you have any advice on altering the code to work? I've tried this without any results:
Dim parsed As Dictionary
Set parsed = JsonConverter.ParseJson(response)
Dim ferry As Long
Dim leg As Dictionary
For Each leg In parsed("routes")(1)("html_instructions")
ferry = ferry + leg("maneuver")("text")
ferry = ferry
Next Item
GETFERRY = ferry
Hi,
thank you for this. I really needed it for a data project I am working on. I wanted to ask is there a way to change the travel type, instead of by car, but by public transportation such as the metro?
Yes, you simply add &mode=transit to the URL. You would need to expand the parsing portion a bit to actually know what mode of public transit it wants to use.
This is great. I live in Mexico and street addresses are bad. Is there a way to do this use latitude and longitude instead?
thanks for the video and supporting notes where great for total novice. i had a list of 20 addresses and they all return accurate kms and time to home and office. except 1 which returned #value for kms to home only... but ok for time and kms to office.... any tips to resolve? many thanks
Try this instead of using "MSXML2.XMLHTTP" use "WinHttp.WinHttpRequest.5.1", this works fine for me.
You deserve a million subs
Hi, thanks for this amazing video!
I have more than 10k orgin/destination pairs. What do I have to incorporate into the code so that I do not cross the limit of "Maximum of 25 origins or 25 destinations per request"?
Hi, did u calculate the distances? i have the same issue, thanks!
@@PaulinaBran each cell is its own request
same
Great video and presentation! I did it exactly but I got a 424 error in the "Set parsed = jsonconvert.ParseJSON(response)" line. I am trying to figure out What I am missing? Could you help me? Maybe is the JsonConverter module code. Any other clue?
Hope you figured it out within the last 2 years, but just in case...it should be "Set parsed = JsonConverter.ParseJson(response). - you missed the 'er' after convert. I just did the exact same thing and happened to see your comment. Easy to miss.
This still works thanks
any advise when the error Request_denied shows up? what can be the issue and how to solve it?
Hi, I did it the exact way as the video, but when I use the formula It gives me a #VALUE! for the time and distance, I also wanted to know if this works for coordinates (I also tried using the same direction as the video but I have the same result)
I have the same problem. I noticed that when I type in the URL into Chrome it doesn't display the code like it does on his - It shows it all as plain text instead of using the Java formatting.
Have you managed to get that fixed?
I've got problem with this code, Duration is working well but Distance is giving me #ARG! problem. Can you help me with this?
Hi, I am getting a compile error - User-defined type not defined. And the text "parsed as dictionary is getting highlighted. Can you please help?
same
Loved the video. Like the distance and time travel calculation, how can i find the postal/zip codes of a particular address using the api key. Because my excel sheet contains 20k Plus string address. I want the zip/postal of these address mentioned in excel sheet.
Your best bet would probably be to use the geocoding API. That's a pretty common question I'm sure so maybe I'll make a video on it soon. developers.google.com/maps/documentation/geocoding/overview
Hi, I tried replicating this exactly but it doesn't work for some of the addresses. Any idea why that might be happening?
Hi there, what could be the reason that this works with adresses from the USA or Mexico, but it does not work with adresses from any European Country (Germany, UK, France) or for example Brazil?
Have you ever found an answer to this question? I have the same problem
@syntaxbyte Hi I keep getting an value error and the function line is highlighted in yellow, please can you help me on this been trying to figure it out but with no luck.
I was getting this, It's because I failed to add the Scripting and Win things
Thanks for the vid! I followed the steps all the way until you reached the travel time of 622. I get a #VALUE! It says that a value used in the formula is in the wrong data type. I rechecked the code and I cant see an issue there. I also tried different addresses. I also tried by changing the format of the cells to text instead of General to see if that did the trick. What am I missing?
Hi Bryan, this has been a commonly reported issue with the code presented in this video. The problem is that the time in seconds is often larger than 32767 which is the maximum value from an integer. You can try replacing Integer with Variant or Long and the #VALUE! error should go away. Thanks!
This is amazong thank, how do I put multiple desitnations in etc?
Hello this video is AMAZING! But I am having issues running the code. I have done exactly what you have done and I get an error function. The only difference between the steps in your video and what I am actually doing is that when you type "Set Parsed = JsonConverter.ParseJason" when I type "Set Parsed = JasonConverter" and then I place the period the files do not pop up. You get 7 options that pop up and you select the one under "Json Options" called "Parselso" (video minute 10:25) and I do not get anything. Its like VBA is not reading the file that I imported yet I did submit the file and I can see the code. I believe that has to be the source of the issue. PLEASE HELP!!!!!!!!!!!!!!!!!!!
hi, how could i do this with coordinates?
HI syntax byte, I need to query all shops near me and get thier contact data etc into excel ,
Can i do this in excel?
You could probably use the Google Places API to get the information you're looking for, though you'll want to be aware of quotas etc. If you don't need to regularly update this info you may just want to use something like Python and save what you query as a CSV to be used for analysis in Excel.
How do you use this for Mac Excel? I added in the VBA-Dictionary, but receive a #VALUE! in my cell. I am able to get distance and time copy and pasting my http address with my apikey, so I think there may be an issue with the http get request through IOS Excel vba.
@@pabloleonardo thanks does this work on macOS?
@@pabloleonardo Thank you
Great info. thank you. Is there a way you can do the same using bing maps API? Can you show the steps or the code?
I'm new to this, but I would assume you would just replace the strUrl = with the bing "..."
hello, I do exactly the same as the video, but the result is an error in excel. what is missing. I have excel 2013. thanks.
What error did you have?
Hi, thank you for a great video. I have one problem (at least) that someone might be able to help me. The problem, in the VBA are the lists in the function using comma (,) to separate each variable. However, in the European standard of Excel are they variable separated by a semicolon (;) which I THINK is the reason why I get the #VALUE problem. I've tried to use the addresses as in the video as well as others. No luck. I have tried to change the setting through the control panel etc but I'm not able to change the list setting in excel from (;) to (,). Any tips?
I also have a value problem, I would be interested in knowing the answer.
did you solve the problem?
Have the exact same issue...anyone of you able to solve the problem? :)
Thank you for this great info. I experience however a "Compile Error" "Syntax Error" which then highlights the first function line. Please let me know if there is a quick fix. Thanks
Can you share your code as a github gist?
@@syntaxbyte Syntax Error #165
Above is the submitted error code
That's the error code, yes, it just means it couldn't interpret something as valid VBA. Without seeing your code, I can't tell you what you may have incorrectly typed in. I would recommend just copy-pasting from my website if you haven't already.
I had the same error, I replaced all of the "&" with just an "&" and that worked for me
@@Jrandomefilmstuffoe this works for time though but still got error with distance
Thank you for the great tutorial. I have a similar question as Mohit below. I have multiple list of pairs I want to calculate the distance, but it seems like I get a #VALUE! error after the first row. How can I fix this problem? Thanks again for the amazing vid.
Hi, please do let me know in case you figure it out :)
@@mohitgupta5593 I don't know if this answer will help you, but I changed the object type. When I debugged the code I used to have an incomplete json responseText. Check if this is your case with the Local Window and copy your answer string value. In case you have an incomplete value of the json result change the object, instead of using "MSXML2.XMLHTTP" use "WinHttp.WinHttpRequest.5.1", this works fine for me.
@@CarlosGonzalez-fr6ek Yes! This fixed my issue!!!
Hello, I wanted to ask you one thing, is it possible to do that with coordinates? I would like to enter lattitude and longitude instead of exact address. Can you help me about this? Thank you.
Yes. You just enter the coordinates in the cell instead. Should be lat,lon. You don't need to change anything in VBA
@@syntaxbyte Very nice, but could you please share the template excel file with us? This would be wonderful. Thank you!
I did try and upload the file on my website but Wordpress seems to block it and I don't really know how to allow it. However, if you go to my written tutorial linked in the description the code is there and you can just copy and paste it into the VBA editor.
Hey! Great video. Got it working straight away. I have a follow-up question. Is there an easy way to pull out info about whether or not your route includes a ferry in a separate cell in excel? ☺️
You should be able to just pull that information from the same JSON file. Whether this is easy or not might depend a little on your personal programming ability.
@@syntaxbyte Thanks! I am also wondering about multiple routes. You say that you assume that the first route is correct. How can i get alternatives?
Thanks for the tutorial (and code!). I followed the instructions but im returning a couple specific errors. Traveldistance returns a #value no matter what i put in. And traveltime works but only up to a certain distance (I can go from tallahassee to atlanta fine, but not from tallahassee to houston). Any ideas? =TRAVELTIME(A16, A17, B14) Thats the formula im using (replace time with distance for the other) with A16 and A17 being tallahassee and houston
Hey Greg, try replacing Int with Long or Variant to fix the issue with TRAVELTIME. To be honest, I'm not sure what could be wrong with your traveldistance. To debug these things I usually log the JSON response and step through my code to see where things might not be lining up.
@@syntaxbyte I had been using this over the past few months with no issues, but recently noticed I am getting a #value issue on traveldistance as well. Is there anyway you could explain further on how to debug using the JSON response? it seems like something is off on my response (apologies, i am no coder)
Sure. It's a bit tough if you have no coding experience, but usually I start by logging the response to the console or using the MsgBox function to see the actually response. If it comes through pretty dirty just copy it and find a formatter online that can make it a bit more readable. The response is what the Google API is giving you. The rest of the code just interprets that response to pull the right information out of it. You want to kind of walk through your code line by line and make sure that the "JSON path" so to speak that you are using in your code and the response you are actually seeing line up. JSON is made up primarily of objects (key/value pairs) and arrays (lists of values). So where you see parsed("routes")(1)("legs") that means that inside the base object there should be an array called routes, which you are getting the first item of, and that item has a value (array) named legs. You can also try logging values within the foreach loop to see if it's adding them up right or if you're getting some kind of value in the loop that you don't want.
i created the api key and am doing the same thing in the video. copied his text and every thing and i get a #name? error for the result. anyone have any idea why?
What is limit to find in one day ?
Hi, first of all thanks for this video! I did everything as you showed and basically it worked. However I get really strange behavior with the distance calculation. What happens is the following: if I use a city as an origin, it calculates the distance perfectly for each destination that is less than 30 km away. Whenever i try a destination that is further away than that, the distance calculation gives me a #value error....??!! However the travel times calculation continues to work... Any idea how this is possible ? I dont think it has to do with the format of the cell in excel or something like that ? Please help because I really need this functionality.
Odd. I'd love to do more testing but I'm not in the same city as the computer I usually use for VBA that has this workbook on it. My suggestion would be to make that request in a browser and take a look at the JSON. Maybe there is some sort of structural change?
Try removing the Dim meters as Integer line.
@@stomean Heeeee ! that did the job ! cool ! thanks a lot ! Can you explain why it works as well ?
hi @Radical199 did you use the api key of your own ?
@@muhammadghous1827 yes I did !
Does this work when using excel on mac? I cant seem to locate the required references
same
Any chance that you might have found them? I am also using mac and can't locate the references.
Hi, Thanks for this tutorial! However I want to calculate the shortest distance between two places, how can I change this? Now it always takes the first option, but this is not always the shortest one 😅
You would need to modify the code to look through all solutions and then take the shortest one.
I am very new with all this. How can I open the JSON interface as seen in the video? Thanks!!!
I assume you are talking about the interface that was shown in Firefox. That comes with Firefox by default, and will appear when opening a JSON file with it. If you use a different browser, you could try Firefox for this use case or just install an extension.
@@syntaxbyte Thank you so much! Yes, that was what I was asking for! Again... I am really new on this! Lol.... and trying to get a txt file with only Directions format as shown on GoogleMaps and maybe to get some .jpg file with the Location point, and I think this is good chance to start with (?) Thanks again for answer!
Hello, does the api request get charged when I press enter continuously, how about creating a button to run when all addresses are done being entered, I think it will be costless
It will send off a new request each time the formula is evaluated, yes. You may wish to just change your calculation options.
I was getting a #VALUE error, because my API key had a "-" in the middle so the function was trying to evaluate this. I put the API in the function with a " " around the outside of it, and it worked.
Hi, great tutorial and I learned a lot! I am trying to go to one level deeper, for example, to get the walk from a location to a transit. I have adjusted the url already for mode transit, however i m having trouble understanding and editing this part:
For Each leg In parsed("routes")(1)("legs")
seconds = seconds + leg("duration")("value")
Next leg
Going back to google api json file, I wish to get the duration value of routes>legs>steps (i.e. one level deeper). Usually, that step is the walking from origin to nearest transit, which is exactly what I am looking for. How exactly can I edit the above code to return the first step duration value? Also, it would help a bit if you could elaborate on how this line works: "For Each leg In parsed("routes")(1)("legs")
", how does VBA read this code as?
Thanks a lot for your time and help!
Thanks for the question. That line begins a For Loop, which means the code in between For Each and Next will be run for every leg of the trip. You could do the same thing inside to loop the steps (nested for each loop). If you simply want to get the seconds for the first step, you could do seconds = seconds + leg("steps")(1)("duration")("value").
@@syntaxbyte Great, that was perfect. Thanks a lot for your explanation. I only started learning VBA a few days back in an attempt to do what you did in the video. Strangely, I was sort of close to the answer you gave. What I had written in my code was seconds + leg("steps")("duration")("value"). Obviously that gave me a #value error but I could not understand why it did give an error, and why adding a (1) to the equation solves it. Maybe to simplify my question, I'm trying to understand what (1) means? Thanks for your help!!
Since steps is an array (a list of multiple values of the same type), you need to specify an index which is what the (1) does. Most languages start at 0 but VBA sometimes starts at 1, as is the case here. So (1) gets the first step in the steps array. Arrays are symbolized in the JSON response by the square brackets [].
is this google maps specific or would this work for other map websites?
If you understand the code and the other map site has a JSON API you could use the same technique, because it works with virtually any JSON API. I think there was another commenter that had it up and running using the Bing API.
Hi, I copy your code exactly but still get error "#VALUE!" for travel time & travel distance. Anyone can help me? Many thanks
I had this code working but it has stopped in the last few weeks. I guess something has changed
Go into your code and change Int to Long where it said "Dim seconds As Int" and "Dim meters as Int"
Hello, thank's for the video, it is so interesting, you explained it in detail both through this video and the written version. I did in my excel ( I am using excel 2016), but it can't work, I do exactly the same as your code, and then I also copy your written code and running it in my excel, but I got #name? as a result. Can you help me figure this out? What did I do wrong? Thank You
Seems like Excel doesn't recognize the function. Make sure you are using Function not Sub, and make sure it's in an area the workbook can access.
@@syntaxbyte Thanks a lot for your help, it is work now, I forget to enable the Direction API when I copy the credential/key API.
Very helpful and I feel I'm close. I get that I have an error and brings me back to the module to the stringurl line, is there something wrong that I missed in copying and pasting from the blog?
strUrl = "maps.googleapis.com/maps/api/directions/json?origin=" & origin & "&destination=" & destination & "&key=" & apikey
Are all of those variables initialized? What does the error say?
I am encountering the same issue. Did you manage to make it work?
replace & with &
@@lolarenan359 hi lola, by any change you got it right? Im having the same issue
@@stuarthester2236 I did try it , but it is not working. any other suggestions?
here you can copy and paste the macro? Please!
All the code is on my website at syntaxbytetutorials.com, link is in the description.
HELP!! I have been working on my excel sheet for months now, It works it one cell only, the others all have a #value error. I have no idea why?
Pretty difficult to say without more information, but my guess is it's something to do with the input. In regular Excel, this is commonly due to an absolute vs relative cell referencing issue changing inputs you wanted to remain static between runs. I would recommend changing each input one by one to see if one is causing an issue with the code.
@@syntaxbyte I have done that over and over, can't figure out why. Can I share it with you somehow?? I am not a programmer at all, loved your video. BTW!
DO THIS WORK IN MACBOOK?? IF SO, HOW??
I keep getting an #Value message in the distance calculation. The time calculation is working perfectly. I have triple checked the code and everything looks correct. PLEASE help me!
Actually, after reading the comment from Radical199 below, I realized I am having exactly the same issue.
I just figured it out...you need to remove the Dim meters as Integer line. Now it's perfect! Thanks for the great vid. You just helped me tremendously.
@@stomean what does it mean?
Did you use the API key of your own ?
@@stomean I tried that because I have the same issue, but it doesn't work for me, any API functions I have to enable or something?
Hows it going boss, this was more than helpful with what I was trying to accomplish. One question for you though if you have the time. It was working perfectly fine yesterday, but now the TRAVELTIME and TRAVELDISTANCE are returning the #NAME? errors. I don't see many solutions for this in the comments, so I hope you could help.
For everyones reference with this issue, I figured it out, make sure you have macros enabled in the trust center under File and Options.
@@Bran554 Bless you! This was driving me nuts!
It is not working for me. I have tried all the steps that you have mentioned but do not know what I am doing wrong and how can I get this worked. Please help.
Thanks for the comment! Unless you tell me a bit more (such as an error message you are experiencing) I can't really help.
@@syntaxbyte I am getting this error when executing the formula in the worksheet. Error > i.imgur.com/YaEE6tm.jpg. I am sorry but I do not know VB Scripting just followed the instruction.
Not sure if you know or not but your website has a redirect hack it keeps redirecting me to load05 website
Hey thanks for the comment. My suspect is a certain ad unit on the site is causing the problem. I have tried that network before and ran into issues, but I thought it was probably with a different one of their units. I have disabled that unit for now. Feel free to give it another try. Only amazon ads now. I appreciate the informative comment.
@@syntaxbyte seems to be working now. And thanks for the video as well it works great for me.
The google api wants a credit card.... Dont have one... how do you get a key? Do you really need a key... why cant it just seach?
Use to be able to but now I don't think you can, you could try the Bing directions API instead as another user mentioned in their comment. Definitely don't need a credit card to sign up for it.
The distance problem is because you may have picked one of the few buildings in the world that doesn't have an address(house) number. United States Capitol resolves to "First St SE, Washington, DC 20004" which doesn't have an address number, giving unexpected behavior.
Seems to work perfecting, with the only thing I have noticed is google maps website always floors the duration value, even if it is 10.9333 it will still display 10, while the api returns 11 mins.
Hi everyone, I am new to this but I keep getting compile error syntax error anybody know how to fix this?
A syntax error means something you typed is not valid code. Without more information, there's no more help myself or anyone else can provide.
How can I add walking mode ?
You need to pass mode=driving, developers.google.com/maps/documentation/directions/overview#TravelModes
error appears !!! not defined by user
Do we have to pay for API key ?
You don’t pay for the key, the fees are based on how much you use and there is a free allowance. I believe you do need to add a credit card so they have a way to charge you if you go over the free limit
I keep getting a compile error. travel time =0 all the time
I successfully used this for years, but now I only get a value response in excel. I suspect the problem lies in the JSON Converter. Could you please try it again and see if you also have this problem? Does anyone else have issues? Last time, I successfully used this, was in May 2023. Now, the same file with the same data will not work anymore.
The issue were some special German characters ü, ö, ä. But I wonder why it only now caused problems. Maybe google changed something here? I just replace these characters with different ones in my excel file before sending them to the server.
I'm thinking that it may be giving you distance "as the crow flies" just straight over
Anyone who have tried to incorporate a specific departure time to the macro/formula?
You simply need to add departure_time to the URL. You can see optional parameters here: developers.google.com/maps/documentation/directions/get-directions#required-parameters
Pal the video is good… however there is too many water drinking sound behind 😂😂😂😂
Does this cost money to run the API? Or is this free?
I've never had to pay for my use. The API isn't free, it costs $0.005 per use but there is a $200/month credit available. So for a small amount of use, it is effectively free. See developers.google.com/maps/documentation/directions/usage-and-billing
It will cost you money.. it wants your credit card. Im just not cool with that. Every time you search it eats away at your little credit.. One day it will just bill you for more and more.... Its 1 cent a search now...
@@lanksterprice no it says it isnt going to charge you. Its just to verify that you are not a "robot"
OK READ THIS GUYS:
So he did not showed in the video. If you get #Value error please enable directions api. I am noobie as well but I got it work. So go here and read this topic: stackoverflow.com/questions/32994634/this-api-project-is-not-authorized-to-use-this-api-please-ensure-that-this-api
Also this page is usful to test if your directions api working. developers.google.com/maps/documentation/directions/get-api-key
Another thing is syntax error. To get rid of the syntax error simply replace all of the "&" with just an "&" in syntax
Hopefully it helps!
Thank you, this was my exact issue.
please help me
This does not work at all. Followed steps 5 times...
Thanks for watching the video, trying the steps, and commenting. I am certain given the number of people who have commented below that have experienced success that these steps work. However, many other commenters have also had difficulties or pointed out flaws in the code presented in this tutorial. Could you please share more information about the error you're getting? It's the only way to receive any actual help. Otherwise, you are ranting. Please don't rant.
too much drinking, geez. when i do trainings at work i don't sniffle or drink 1000x. And it was giving you 1.7 miles as straight line distance which is totally useless unless you're flying a plane.
Wish you had explained it in a way so that people who are at beginners level could have also understand it. You were presenting as if you are not explaining it but you are just doing it for the sake of just showing it. It is a good video but I think you could have done better in explaining things as a trainer. Also, your results are not accurate too. As a programmer, I thought you will be good too. But, I guess I will look other video by other programmers.
Thanks for the comment. I can understand that this video may be a little advanced for those who do not have prior exposure to VBA, though I would say that others have commented that it works for them even though they don't have VBA experience or necessarily understand all the code. Unfortunately, to do something like this in a way you truly understand every aspect if you are a beginner would take probably an 8 hour course rather than a 20 minute video. So I have to strike a balance, but thanks for the feedback.
@@syntaxbyte Can you please atleast tell where did you get the link to use your API where you get the display codes when you calculated the distance from Disney to Universal?
The link I used between those two places was just from google's documentation: developers.google.com/maps/documentation/directions/quickstart
what a waste of time, he did not provide any code, just posted a link where he puts a code full of errors that you fix by reading the comments from other users, after that it still doesn't work
Worked perfect 🫶🏻
Hi, I am getting a compile error - User-defined type not defined. And the text "parsed as dictionary is getting highlighted. Can you please help?
Did you install VBA JSON?
yes I've imported the JsonConverter.bas@@syntaxbyte