Reading HTML file in VBA Excel

Question

i want to read the HTML code in VBA (something like URL in Java). I need to save it in a string. I parse it afterwards.

alpan67

Do you mean URL decoding into a String? e.g. java.net.URLDecoder.decode(url, "UTF-8"); — bonCodigo
– bonCodigo, Commented Dec 31, 2012 at 15:20
I need to read the HTML code, it is a football table and i decode it later. How can i save the HTML of the website ? In Java i can create a URL and then openconnection etc. etc. — Alpan67
– Alpan67, Commented Dec 31, 2012 at 15:23

Daniel · Accepted Answer · 2012-12-31 17:21:31Z

10

Here's a function for you. It will return the String of the HTML returned by a given URL.

Function GetHTML(URL As String) As String
    Dim HTML As String
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        .Send
        GetHTML = .ResponseText
    End With
End Function

Just make sure your provided URL is well formed. I.E. it includes the http:// or https:// if appropriate.

For Example: GetHtml("www.google.com") is incorrect.
You would want GetHtml("https://www.google.com/")

edited Dec 31, 2012 at 17:21

answered Dec 31, 2012 at 15:42

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Davide Over a year ago

Thanks - how do you avoid the response text being truncated?

Apostolos Over a year ago

I used your code and I get "Access denied" error at .Send. Any idea why? (Note: I used very common URLs (google.com, youtube.com, etc. that normally have no problem to be opened programmatically.)

Apostolos Over a year ago

Thanks. I found a solution using CreateObject("MSXML2.ServerXMLHTTP.6.0")

Community · Accepted Answer · 2017-05-23 10:24:46Z

0

To do the URL DECODING you may use that post as a reference.
Here goes a article that uses MS Word to save html of web page as text.
Code in Excel VBA - from VBAExpress: I wouldn't fancy copying his code. You may give it a try and comment.

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered Dec 31, 2012 at 15:35

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Collectives™ on Stack Overflow

Reading HTML file in VBA Excel

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related