Read html

[Npsays]

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..

 

[Legacy 98055]

Yes. A simple example:

<table width="100%" border="1" bgcolor="White" style="filterrogid: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]

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