Accessing a specific website

crushdrinker06 · Apr 4th 2016

I have been trying to use excel to access a website, fill in information and submit. The code on the website is listed below.

Code

<form ID="myform" NAME="myform" action="../php/item_setup.php" method="get" >
<table align="center">
<tr><td>Item Number:</td><td><input type="text" name="item" size="9" maxlen="8"></td></tr>
<tr><td>Send Output To:</td><td><select name="output"><option value="pdf" selected>Adobe PDF</option>
<option value="xls">Excel File</option>
</select>
</td></tr>
</table
  <br>
  <div align="center"><input type="submit" value="submit"> </div>
</form>

Display More

I would like to input a number into the "item", select "xls" and then submit. I have looked up ways to interact with the internet, but they do not seem to work. Please be aware that I have no experience with interacting with the internet through excel and would appreciate some code rather than simply a link.

John_w · Apr 4th 2016

Re: Accessing a specific website

Try this code. You must set references to Microsoft Internet Controls and Microsoft HTML Object Library via Tools -> References in the VBA editor, otherwise the code won't compile or run.

Code

Option Explicit




Public Sub IE_Submit()
    
    Dim URL As String
    Dim IE As InternetExplorer
    Dim HTMLdoc As HTMLDocument
    Dim outputSelect As HTMLSelectElement
    
    URL = "http://www.thesite.com/page.php"  'CHANGE TO CORRECT URL
    
    Set IE = New InternetExplorer
    With IE
        .navigate URL
        .Visible = True
        While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Wend
        Set HTMLdoc = .document
    End With
    
    HTMLdoc.getElementsByName("item")(0).Value = "1234"
    
    Set outputSelect = HTMLdoc.getElementsByName("output")(0)
    outputSelect.Value = "xls"
        
    HTMLdoc.getElementById("myForm").submit
    'Or
    'HTMLdoc.forms(0).submit
    
    With IE
        While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Wend
        Set HTMLdoc = .document
    End With
    
End Sub

Display More

crushdrinker06 · Apr 5th 2016

Re: Accessing a specific website

John_w,

Thank you for the help, but I am having an issue when I try to run the macro. I am receiving a runt-time error '-2147467259 (80004005)' Automation error Unspecified error and the debuger is highlighting While .Busy Or .readyState <> READYSTATE_COMPLETE. Do you know what would be causing this?

John_w · Apr 5th 2016

Re: Accessing a specific website

Did you change the URL string?

Try this, using a known working site, ozgrid.com. Also note the explicit use of the SHDocVw library for the InternetExplorer object, though this shouldn't make any difference (no additional references are needed). You could try the same change on my previous code.

Code

Public Sub IE_Ozgrid()
    
    Dim URL As String
    Dim IE As SHDocVw.InternetExplorer
    Dim HTMLdoc As HTMLDocument
    
    URL = "http://www.ozgrid.com"
    
    Set IE = New SHDocVw.InternetExplorer
    With IE
        .navigate URL
        .Visible = True
        While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Wend
        Set HTMLdoc = .document
    End With
    MsgBox HTMLdoc.body.outerText
    Stop


End Sub

Display More

crushdrinker06 · Apr 5th 2016

Re: Accessing a specific website

John_w,

Both sets of code works for the ozgrid website, but not for the company website I am trying to use. I get the same error message for both sets of code when I try the website I am trying to use. Both sets of code do open up the website, but the second set of code errors out at the "While .Busy Or .readyState <> READYSTATE_COMPLETE" line

John_w · Apr 6th 2016

Re: Accessing a specific website

Try changing the Busy readyState wait to separate loops:

Code

While .Busy
            DoEvents
        Wend
        While .readyState <> READYSTATE_COMPLETE
            DoEvents
        Wend

That may work. If not it may tell you which of Busy or readyState is causing the problem.

crushdrinker06 · Apr 6th 2016

Re: Accessing a specific website

It is stopping at "While .readyState <> READYSTATE_COMPLETE"

John_w · Apr 6th 2016

Re: Accessing a specific website

Therefore remove or comment out that loop.

Accessing a specific website

Participate now!

Share