Read html

Npsays

Board Regular
Joined
Dec 17, 2004
Messages
70
Hi
Is it possible for VBA to read data from HTML files ? If so then can i get an example.. I need to copy some data into excel from a HTML source.
Thanks..
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
L

Legacy 98055

Guest
Yes. A simple example:

<table width="100%" border="1" bgcolor="White" style="filter:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> Example()
       <font color="#0000A0">Dim</font> HtmlSrc <font color="#0000A0">As</font> <font color="#0000A0">String</font>
       HtmlSrc = GetSourceHtml("http://www.google.com/")
       Debug.Print HtmlSrc
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Function</font> GetSourceHtml(URL <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> <font color="#0000A0">String</font>
       <font color="#0000A0">Dim</font> WinHttp <font color="#0000A0">As</font> <font color="#0000A0">Object</font>

       <font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">Resume</font> <font color="#0000A0">Next</font>
       <font color="#0000A0">Set</font> WinHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
       <font color="#0000A0">If</font> WinHttp <font color="#0000A0">Is</font> <font color="#0000A0">Nothing</font> <font color="#0000A0">Then</font>
           <font color="#0000A0">Set</font> WinHttp = CreateObject("WinHttp.WinHttpRequest.5")
       <font color="#0000A0">End</font> <font color="#0000A0">If</font>
       <font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">GoTo</font> 0

       WinHttp.Open "GET", URL
       WinHttp.Send
       GetSourceHtml = WinHttp.ResponseText
  <font color="#0000A0">End</font> <font color="#0000A0">Function</font>

</FONT></td></tr></table><button onclick='document.all("105200642640750").value=document.all("105200642640750").value.replace(/<br \/>\s\s/g,"");document.all("105200642640750").value=document.all("105200642640750").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("105200642640750").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="105200642640750" wrap="virtual">
Sub Example()
Dim HtmlSrc As String
HtmlSrc = GetSourceHtml("http://www.google.com/")
Debug.Print HtmlSrc
End Sub

Function GetSourceHtml(URL As String) As String
Dim WinHttp As Object

On Error Resume Next
Set WinHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
If WinHttp Is Nothing Then
Set WinHttp = CreateObject("WinHttp.WinHttpRequest.5")
End If
On Error GoTo 0

WinHttp.Open "GET", URL
WinHttp.Send
GetSourceHtml = WinHttp.ResponseText
End Function
</textarea>

Why not use Excel's built in web query?
 

Npsays

Board Regular
Joined
Dec 17, 2004
Messages
70
Thanks .. and sorry for the delay in responding ! .. I tried this code but wasn't able to figure out what exactly it is doing. I was expecting it to copy the data into excel but it didn't. Also, what is 'excel's web query' ? and how can i use it to copy some data through an html source..
regards..saysnp
 

Forum statistics

Threads
1,141,094
Messages
5,704,304
Members
421,337
Latest member
DeuxMilleSangue

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top