如何使用VBA循环浏览Excel电子表格,将值粘贴到网站表单,然后将结果提取回来?

这应该会有所帮助。

作为一个例子,我使用了网络上存在的数百万随机名称生成器中的一个。此代码打开一个IE实例,导航到“ http://www.behindthename.com/random/ ,将姓氏输入表单,提交表单,并返回生成的HTML正文。

我还没有解析结果页面HTML以返回实际名称,因为这对于您的站点来说是不同的,但是这应该可以帮助您开始。

要访问SHDocVw.InternetExplorer对象,需要对“Microsoft Internet控件”的引用。

Private Function GetFromSubmittedForm(strSurname As String) As String
Dim strReturnBody As String

'Requires reference to "Microsoft Internet Controls"'
Dim IE As SHDocVw.InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")


'Navigate to the URL'
IE.Navigate "http://www.behindthename.com/random/"

'No need to show the window'
IE.Visible = False

'Wait for IE to load the page'
While IE.Busy: DoEvents: Wend
Do While IE.ReadyState <> 4: DoEvents: Loop


'NOTE: I have refered to each element by name, but indexes _
       can also be used: Forms("random") could be Forms(0)'


'Select the form by name:'
With IE.Document.Forms("random")

    'Reference each Input by name'
    .Surname.Value = strSurname

    'Submit the form'
    .Submit

    'Sometimes you may need to submit using _
        the name of the submit button like this:'
    '.SubmitButtonName.Click'
End With


'Wait for IE to load the new page'
While IE.Busy: DoEvents: Wend
Do While IE.ReadyState <> 4: DoEvents: Loop


'Set the body of the new page to a string'
strReturnBody = IE.Document.Body.InnerHTML



'/CODE THAT ANALYSES THE BODY HTML GOES HERE'

GetFromSubmittedForm = strReturnBody

'\CODE THAT ANALYSES THE BODY HTML GOES HERE'



'Close the IE doc'
IE.Document.Close
IE.Quit
End Function

希望这有帮助。