Web scraping question

This is giving me a headache. Below is my code. This forum does not let me post URLs, which is ever so silly. Where it says "URL =" in the code below, enter any (URL in quotation marks) from a bibliography page listed on espacenet, e.g. for patent Nr. KR20190082024.

The responsetext seems to return the web page header, but of course I want to grab the body. Using the InternetExplorer.Application object is no option; this is slow and prone to error.

Please help.

Please Login or Register  to view this content.

When I do this in my own code I get the body. Actually I just wrote some code to do this yesterday. PM me the URL and I will try it, and also add it to your post.

This forum does not let me post URLs, which is ever so silly.

After you have posted more times, it is allowed. New members cannot post URLs because 90% of the time it means it's spam.

Your call is retrieving the entire page. There are 52,488 characters in the page source, and you can only put 32,767 in a cell. There are also limits on line feeds, and how many characters can actually be displayed. You are not saving the header, you are saving the actual page--just not all of it.

What do you ultimately need to do with the page source? If you really need it in cells, you can loop through and distribute it into multiple cells. I might be able to give you other ideas if you can give me the big picture of what you are trying to do.

The coding for the page you are retrieving is a bit complicated. I am not a web coding expert, but it looks like the page loads then there is JavaScript to retrieve the content on the client side after the page is loaded. You can see what I mean if you load the page in a browser then View Source. I am not sure how to retrieve that content by VBA.

Hi Pak,

you can install Selenium Basic application from GITHub and you can write Selenium code in Excel. this will help you alot

Originally Posted by Pak Mariman

Maybe the initial "GET" request is indeed to the wrong URL (?). Any thoughts?

Here is the difference between the two methods you used, and why the second one works.

The first method performs the HTTP transaction. That is, VBA sends a page request directly to the target URL, and synchronously waits for a response. The server sends the page back, but it contains JavaScript, which VBA does not know how to execute. You get the page exactly as served. To the server, you look the same as a browser.

The second method is a built-in but clunky way of handling web pages. Instead of sending a request directly to the server, you are asking Internet Explorer to do it for you, then when IE is all done, you can read ask IE to give you the page as it is displayed (which should be all HTML, but I haven't looked). You are letting IE execute the JavaScript for you. This is a bit slower because the VBA has to wait out whatever it is IE is doing. (Early vs. late binding is not the issue; it's a completely different method.)

Since all you want is the abstract, I can help you isolate that if you are interested.

Originally Posted by Pak Mariman

I would be interested in some pointers if and how it is possible to do with the first method. I agree with you that the internet explorer-based method is clunky. I am a bit puzzled why Microsoft has not improved on that. I found that accessing e.g. Google Patents using the internet explorer method does not work at all.

It's just not possible with the first method unless someone has written a VBA-callable library to execute JavaScript.

I have been bumping this around to other places and one suggestion was to use Google Sheets instead of Excel. This formula would retrieve the abstract:

=importxml("https://worldwide.espacenet.com/data/publicationDetails/biblio?DB=EPODOC&II=0&ND=3&adjacent=true&locale=en_EP&FT=D&date=20190709&CC=KR&NR=20190082024A&KC=A&rnd=1592067094437","/html/body/div[1]/div[6]/div/div[2]/div[2]/p[1]")

VBA can execute JScript via some external ways like execScript & eval methods or the ScriptControl ActiveX …

Is Google Sheets faster than Excel FILTERXML / WEBSERVICE formulas, PowerQuery
or even just piloting IE via VBA (here ~ 2.5 seconds to grab the 'abstract' text) ?