macro errors out

[Pancho3]

When running the enclosed macro, I receive a Object variable or With block variable not set, error Line <code> trlen = tr.ChildNodes.Length <code>

any help or suggestions would be appreciated.

Please Login or Register  to view this content.

[6StringJazzer]

I haven't done coding to parse HTML documents but from a VBA standpoint the most likely problem is that this line of code

Please Login or Register  to view this content.

fails, and tr is set to Nothing. However, I do not know what can cause getElementsByTagName to fail to return anything. I can tell you that the HTML from the page you're retrieving only has two "tbody" tags and you seem to be trying to get the 91st one.

[Sean Thomas]

What are you trying to do?
you are currently trying to recover the data between tbody & /tbody tags, but using (91) means you are looking for the 92nd set of tags. however the source code for the web site only shows 2 sets of these tags.
I presume you are trying to get the data from a table.
If you can send a copy of what you are trying to achieve, ie image of the data from the website & a layout in a workbook, then I might be able to help.
Web scraping can be rather tricky depending on how the web page has been set out by the developer. If they tag everything with id's its easy otherwise you have to search for headings within tables etc.
anyway, send what you can & I will take a look

[Pancho3]

Thank you Sean, I appreciate your time and efforts. I have attached the documents as requested. If I have missed something, please let me know.


again. Thank you

[6StringJazzer]

I am following this thread with interest but I can't tell from the Excel file you attached what your strategy is for populating it with data from the web page. Also the workbook you attached does not have any macros in it. Do you want to capture the entire table, or just specific rows?

It appears that Excel web query does not support multiple arguments in the URL, but I can still get a superset of the data you need. It is much easier to do it as a web query instead of writing VBA to parse it. See sheet "Web Query" in the attached for an example. I used the URL in your code rather than the URL in your sample workbook.

If you want to capture specific rows, it will still be easier to capture the entire table in a web query, then use VLOOKUP to build the table of just the desired pitchers. See your sheet "Pitcher-Report-Advanced" as an example.

[Sean Thomas]

Hi,
hopefully this is what you are after.

[Sean Thomas]

Hi 6StringJazzer,
I agree that using web query is easy to pull a table in with. I used to use this before understanding XML.
I find this quicker & also allows you to manipulate data easier.
You can use some simple code to pull out individual rows, columns or just one cell of data from a table. Therefore if you wanted to capture data for say one player in a table its easy to do with a few lines of code.
Took a while to research the web for info & understand it, but its faster than using web queries or using a web browser control to get the data from html.

[6StringJazzer]

Parsing the XML is certainly much more powerful, although depending on the how the code is written creates a greater dependency on the format of the web page. Nevertheless, I am still not clear on what Pancho3's ultimate objective is, which has some bearing on what is the most effective way to achieve it.

[Pancho3]

thank you for your response. I use web queries on a regular basis. I basically was trying to learn a different method of acquiring the data.

[Pancho3]

Sean - Thank you. exactly what I was looking for. I will definitely be reviewing the code as learning tool.

again, Thank you

[Sean Thomas]

Hi Pancho,
happy to help.
If this has resolved your question could you please mark the thread as solved & click the* button to add reputation & thanks for helping.