CODEWITHSUNDEEP
vbahttp
Alter
Alter

Reputation: 3464

Fill excel table from web page

I want to fill out an excel table from data in a web page. How can I do this?

My plan so far, use the WinHttpRequest in Excel-VBA. I'm just getting timeout errors :( 

Sub webtest()
    Dim request As WinHttpRequest
    Set request = New WinHttpRequest

    request.SetTimeouts 2000, 2000, 2000, 2000
    request.Open "GET", "http://www.w3schools.com/html/html_tables.asp", False
    request.Send

    MsgBox request.ResponseBody
End Sub

Perhaps this wasn't the best language to use, but I can't give up over some little time out errors. If anyone can spot my mistake let me know, I'll be glowering over it until it goes away

Upvotes: 0

Views: 144

Answers (1)

triggeradeadcat
triggeradeadcat

Reputation: 81

Alt + D, D, W will allow you to do this without code.

The URL has to be 100% correct. Unlike a browser there is no code to fix urls.

The purpose of my program is to get error details.

How I get a correct URL is to type my url in a browser, navigate, and the correct URL is often in the address bar. The other way is to use Properties of a link etc to get the URL.

Also Microsoft.XMLHTTP maps to Microsoft.XMLHTTP.1.0. HKEY_CLASSES_ROOT\Msxml2.XMLHTTP maps to Msxml2.XMLHTTP.3.0. Try a later one

Try this way using xmlhttp. Edit the url's etc. If it seems to work comment out the if / end if to dump info even if seeming to work. It's vbscript but vbscript works in vb6. 

 On Error Resume Next
 Set File = WScript.CreateObject("Microsoft.XMLHTTP")
 File.Open "GET", "http://www.microsoft.com/en-au/default.aspx", False
 'This is IE 8 headers
 File.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 1.1.4322; .NET CLR 3.5.30729; .NET CLR 3.0.30618; .NET4.0C; .NET4.0E; BCD2000; BCD2000)"
 File.Send
 If err.number <> 0 then 
    line =""
    Line  = Line &  vbcrlf & "" 
    Line  = Line &  vbcrlf & "Error getting file" 
    Line  = Line &  vbcrlf & "==================" 
    Line  = Line &  vbcrlf & "" 
    Line  = Line &  vbcrlf & "Error " & err.number & "(0x" & hex(err.number) & ") " & err.description 
    Line  = Line &  vbcrlf & "Source " & err.source 
    Line  = Line &  vbcrlf & "" 
    Line  = Line &  vbcrlf & "HTTP Error " & File.Status & " " & File.StatusText
    Line  = Line &  vbcrlf &  File.getAllResponseHeaders
    wscript.echo Line
    Err.clear
    wscript.quit
 End If

On Error Goto 0

 Set BS = CreateObject("ADODB.Stream")
 BS.type = 1
 BS.open
 BS.Write File.ResponseBody
 BS.SaveToFile "c:\users\test.txt", 2

Also see if these other objects work.

C:\Users>reg query hkcr /f xmlhttp

HKEY_CLASSES_ROOT\Microsoft.XMLHTTP
HKEY_CLASSES_ROOT\Microsoft.XMLHTTP.1.0
HKEY_CLASSES_ROOT\Msxml2.ServerXMLHTTP
HKEY_CLASSES_ROOT\Msxml2.ServerXMLHTTP.3.0
HKEY_CLASSES_ROOT\Msxml2.ServerXMLHTTP.4.0
HKEY_CLASSES_ROOT\Msxml2.ServerXMLHTTP.5.0
HKEY_CLASSES_ROOT\Msxml2.ServerXMLHTTP.6.0
HKEY_CLASSES_ROOT\Msxml2.XMLHTTP
HKEY_CLASSES_ROOT\Msxml2.XMLHTTP.3.0
HKEY_CLASSES_ROOT\Msxml2.XMLHTTP.4.0
HKEY_CLASSES_ROOT\Msxml2.XMLHTTP.5.0
HKEY_CLASSES_ROOT\Msxml2.XMLHTTP.6.0
End of search: 12 match(es) found.

Also be aware there is a limit on how many times you can call any particular XMLHTTP object before a lockout occurs. If that happens, and it does when debugging code, just change to a different xmlhttp object

EDIT

This is what help says.

What's New in MSXML 6.0 MSXML 6.0 shipped with SQL Server 2005. It also shipped as a Web release. The following is a high-level summary of features that were introduced with MSXML 6.0.

Security

A number of enhancements have been made to MSXML 6.0 to eliminate security threats. Additionally, some features that are insecure, such as DTDs and inline schemas, have been turned off by default. For more information, see MSXML Security Overview.

XDR Schema Support Removed

XML Data Reduced (XDR) schema support has been removed. For more information, see Removal of XDR Schema Support.

Improvements in XSD Standards Compliance

MSXML 6.0 has improved XSD schema standards compliance. For more information, see Improvements in XSD Standards Compliance.

Improvements in Schema Cache Behavior

MSXML 6.0 has changed the behavior of the XSD schema cache. The new version is less ambiguous when there are name collisions. For more information, see Improvements in Schema Cache Behavior.

XML Digital Signature Removed

MSXML 6.0 removed support for XML digital signatures. For more information, see XML Digital Signatures.

Edit 2

Ports are specified by putting : at the end of URLs. eg; http://www.microsoft.com:80

Edit 3

Use Macro Recorder in Excel to record your data query which will write most of your code for you. But they are flexible and probably don't need to be programmed (excel is for non programmers to do programming stuff) - look at the option dialog while setting up the query.

Upvotes: 2

Related Questions