Extract table from HTML code (Chrome)

[excel_2009]

Hi Excel gurus, is it possible to extract/copy a table within a html source code from within a chrome browser? I am trying to figure out a way of copying the contents of a table:

<table class="data-table">

TABLE CONTENTS HERE

</tbody></table>

the idea is to fetch the data within this tag only and paste the data into a cell (excel formats the table but it strips out some information I need).

Is this possible? if so, how could it be achieved?

Thank you!

 

[Dan_W]

It is possible, and there are quite few threads on this forum of people doing much the same thing - for example: Webscraping several tables with getElementsByClassName

With that html code, you're probably best using the getElementsbyClassName method (i.e., getElementsbyClassName("data-table")(0))

Hope that helps.

 

[excel_2009]

It is possible, and there are quite few threads on this forum of people doing much the same thing - for example: Webscraping several tables with getElementsByClassName

With that html code, you're probably best using the getElementsbyClassName method (i.e., getElementsbyClassName("data-table")(0))

Hope that helps.

Thank you Dan!

I've had a quick look but the coding seems to be referencing internet explorer?

Dim IE As New InternetExplorer, html As HTMLDocument
Dim elem As Object, data As String

Nonetheless I'll give it a go, let's see!

 

[Dan_W]

I had understood your original post to mean you already had the HTML code - otherwise why did you stipulate that it had to be Chrome? Does it need to be code from a browser at all?
Once you have the HTML code, the key method is as I set out above, and that's where the other threads will become relevant.

But anyway... so you don't have the HTML code, right?

 

[Dan_W]

Assuming that you don't have the HTML code, and so that will need to be step one, are you able to tell me what the URL is of the site you want to scrape? I can check to see if you need to use a browser at all, because if not, it will be a lot quicker and easier.

 

[excel_2009]

I had understood your original post to mean you already had the HTML code - otherwise why did you stipulate that it had to be Chrome? Does it need to be code from a browser at all?
Once you have the HTML code, the key method is as I set out above, and that's where the other threads will become relevant.

But anyway... so you don't have the HTML code, right?

My apologies, I was under the impression that this stipulation would require Internet Explorer:

Dim IE As New InternetExplorer, html As HTMLDocument

The URL is an internal URL that will not work without a user access unfortunately, however the table that i'd wish to extract from the view source page is as follows:

<table class="data-table">

TABLE CONTENTS HERE

</tbody></table>

The assumption is that, once i've logged onto the page on chrome, I can load up the macro to attempt to retrieve the table information into a cell (as excel attempts to create the table automatically if it's pasted normally) but when I attempt to run the following code:

Sub GetQuickfs()

Dim IE As New InternetExplorer, html As HTMLDocument
Dim elem As Object, data As String
webaddress = "https://XYZ"

With IE
.Visible = False
.navigate webaddress
Do While .readyState <> READYSTATE_COMPLETE: Loop
Set html = .document
End With

For Each elem In html.getElementsByClassName("data-table")

data = elem.innerText
Debug.Print data
Next elem
IE.Quit
End Sub

I see an error:

So i'm not too sure what the issue(s) could be?

 

[Dan_W]

My apologies, I was under the impression that this stipulation would require Internet Explorer:

Dim IE As New InternetExplorer, html As HTMLDocument

You're right - that stipulation DOES require Internet Explorer, but my point is that your original post and indeed the title of this thread expressly mentions chrome, which is why I assumed you had already managed to obtain the HTML code.

Anyway. Internet Explorer is/was a useful tool because VBA could control Internet Explorer directly. Unfortunatey, Chrome (having been made by Microsoft's competitor) is nowhere near as easy to control (if at all, in some cases). There are ways of doing it, but if you're doing this on a work laptop, you probably won't be able to install anything. If I knew the URL, I could test whether you even needed to use a browser at all, because ordinarily, you can just use VBA to direct download the code....but sometimes you do need to use a browser.

The URL is an internal URL that will not work without a user access unfortunately, however the table that i'd wish to extract from the view source page is as follows:

Yes, I understood it the first time you said it - the problem can SOMETIMES be that the HTML code you get will depend entirely on which browser you use, if you use one at all.

And now I've just seen your error message. Are you using a Mac?