Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Import Web Data to Excel using VBA Makro - route planer

0

Hello everybody, i would like to ask another question wich belongs to the same headline as 'Import Web Data to Excel using VBA Macros -- Object Variable not set error'. So i'm not sure if must create a new question. Sorry if i'm doing it wrong.

First of all excuse me my language is not the best. I'm from Germany.

I'm getting the same error which is the 'Object Variable not set...' (Error 91)

I have watched the Youtube video https://www.youtube.com/watch?v=IOzHacoP-u4&t=575s and tried it by myself. 

My goal is to get the information of the kilometers (just the number) from a route planer like (for example) google Maps.  

I've just changed the website to the GoogleMaps page and i copied the classname which i think is the one i need from that page. I also looked for the numer of that specific element but it doesn't worked like i wanted it to.

Maybe you have any advice for my problem?

Thank you very much in advance and one more time excuse me if i'm doing any wrong with this forum chat.

excelinternet-webmacros-vba
MS Office Version: Office 365 ProPlus
posted by: Thms on Wed Sep 4, 2019 at 3:46 pm
Answer
Discuss

Discussion

You should stay with a thread until a working solution has been found in it. Don't expand a question after it has been answered.
As I recall, your question hasn't changed from when you first raised it. You may not have understood the answer then or it may not have been adequate or even incorrect. Either way, you should have stuck with the original thread.
Now, this is the original :-)
Variatus (rep: 4889) Sep 5, '19 at 4:56 am
Add to Discussion

Answers

0

This is the line of code that throws the error.

KM = html.getElementsByClassName("section-directions-trip-distance section-directions-trip-secondary-text").Item(0).innerText

Examine this code (this is done from the middle to both ends).
It first creates and object, an "element" of the html object  in this case.

getElementsByClassName("section-directions-trip-distance section-directions-trip-secondary-text")

Obviously, this object will not get created if its specs - "section-directions-trip-distance section-directions-trip-secondary-text" - isn't found in a class of html.

Then the code continues to examine the Element object:-
Specifically, it takes the InnerText of Item(0). Of course, this effort must fail if the Element object couldn't be created, leading to the error you complain about.

In order to trap this error you should split the single line of code into its components.

  1. First create an object (an effort which will fail) 
    Set MyElement = html.getElementsByClassName("section-directions-trip-distance section-directions-trip-secondary-text") 
  2. Then 
    KM = MyObject.Item(0).innerText
     

Incidentally, as the property name innerText indicates, it is text - a string. Declaring it as a Variant will work but it's a waste of computing resources. You wouldn't list your bicycle as a "vehicle" or "sporting device". Naming it as a "bicycle" is much clearer. It's the same for declaring the value of an innerText as String.

posted by: Variatus (rep: 4889) on Wed Sep 4, 2019 at 10:19 pm
Discuss

Discussion

Thank you for your Answer! 
I knew it was that line which got the error out, but i didn't knew how to fix it.
I also know that declaring the variables right saves ressources, but in this case i just wasn't sure.

Splitting the line into its components like you said, is still getting an error message (like you predicetd in your point 1.)
Now it says 'Object needed' (424) (if i'm translating right)

How you know that the step 1. will fail? (except you just tried it) I don't get it.

Is it so hard to just get the kilometers of a route via google maps and Excels VBA? I'm so confused.
Thms Sep 7, '19 at 5:20 pm
No. I didn’t try it. You did. The element is the object being set, and the error you get I dictates that isn’t happening. 
The element is specified by the text “section-directions-trip-distance” etc. That’s the ClassName. The error seems to say that a class by that name doesn’t exist. Compare it to Set Ws = Sheets(“My Sheet”). If there is no sheet by that name the Ws object will not be set.
Examine the class name. Where did you get it? How do you know it’s the correct name? Observe that the text is repeated. Who would create a name with such repetition? Could it be that you are concatenating two names into one? Or perhaps include part of the class value into the name? Could be just a space added or missing. 
Have you tested that the html object is set? Test with Debug.Print HTML Is Nothing. Of course, if the object html wasn’t set, there would be no point in looking for a class in it. The error message would be the same.
Variatus (rep: 4889) Sep 8, '19 at 11:15 pm
I recommend this course for your study.
Scraping data from a website
Variatus (rep: 4889) Sep 9, '19 at 1:25 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login