VBA access remote website, automate clicking submi

2019-01-28 23:31发布

问题:

I'm working with vba in excel 2010 and internet explorer 8 and Vista. The code below works to go to a remote website and post a form. On the resulting page, the code should click the "get estimates" button. Instead I get this error "object variable or with block variable not set". The highlighted problem line in the code is "estimate = ieApp.document.getElementById("btnRequestEstimates")".

I think part of the problem might be that the button that isn't working is a submit button that isn't part of a form. I am also wondering if the variables need to be reset before the 2nd button click. The error message implies this is a qualification problem, but I think it's a pretty standard way of qualifying an element in this situation. Those are some things I've been googling to no avail, I'm not really sure what the problem is.

Sub btn_version()

Dim ieApp As Object
Dim ieDoc As Object
Dim ieForm As Object
Dim ieObj As Object
Dim URL As String
Dim estimate As Object

URL = "http://www.craft-e-corner.com/p-2688-new-testament-cricut-cartridge.aspx"
Set ieApp = CreateObject("InternetExplorer.Application")
ieApp.Visible = True
ieApp.navigate URL
While ieApp.Busy Or ieApp.readyState <> 4: DoEvents: Wend

Set ieDoc = ieApp.document
Set ieForm = ieDoc.forms(1)
For Each ieObj In ieForm.Elements
If ieObj.ClassName = "AddToCartButton" Then
ieObj.Click
End If
Next ieObj

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
While ieApp.Busy Or ieApp.readyState <> 4: DoEvents: Wend
estimate = ieApp.document.getElementById("btnRequestEstimates")
estimate.submit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End Sub

回答1:

The code below combines your xmlhttp code from automate submitting a post form that is on a website with vba and xmlhttp (to give you better control on the POST, ie skipping your Set ieDoc = ieApp.document section in our question) with clicking the "btnRequestEstimates button on the final URl from this page

Sub Scrape2()

Dim objIE As Object
Dim xmlhttp As Object
Dim ieButton As Object
Dim strResponse As String
Dim strUrl As String

strUrl = "http://www.craft-e-corner.com/addtocart.aspx?returnurl=showproduct.aspx%3fProductID%3d2688%26SEName%3dnew-testament-cricut-cartridge"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.navigate "about:blank"
Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP")

'~~> Indicates that page that will receive the request and the type of request being submitted
xmlhttp.Open "POST", "http://www.craft-e-corner.com/addtocart.aspx?returnurl=showproduct.aspx%3fProductID%3d2688%26SEName%3dnew-testament-cricut-cartridge", False
'~~> Indicate that the body of the request contains form data
xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
'~~> Send the data as name/value pairs
xmlhttp.Send "Quantity=1&VariantID=2705&ProductID=2688"
strResponse = xmlhttp.responseText
objIE.navigate strUrl
objIE.Visible = True

Do While objIE.readystate <> 4
    DoEvents
Loop

objIE.document.Write strResponse

Set xmlhttp = Nothing
Set ieButton = objIE.document.getelementbyid("btnRequestEstimates")
ieButton.Click

End Sub