如何使用 VBA 遍历 Excel 电子表格，将值粘贴到网站表单然后提取结果? [英] How Do I Loop Through An Excel Spreadsheet With VBA, Paste A Value To A Website Form Then Extract The Result Back?

问题描述

我正在尝试使用 VBA 遍历 Excel 电子表格中的姓名列表，我想将姓氏粘贴到网页上的文本框中，然后在提交页面后将返回的电话号码提取到单元格中.

I am trying to loop through a list of names in an Excel spreadsheet using VBA and I would like to paste a surname into a text box on a webpage and then extract the returned phone number into a cell after the page has been submitted.

我一次只能做一个名字，所以我想自动化这个过程，因为有数百个.我可以做 Excel 循环，但我不知道如何与网页交互.

I can only do this one name at a time so I would like to automate this process as there are several hundreds of them. I can do the Excel looping but I don't know how to interact with the webpage.

有人可以帮忙吗，或者请给我指明正确的方向?

Can anybody help, or point me in the right direction please?

问候，伊恩.

推荐答案

这应该会有所帮助.

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

As an example, I've used one of the millions of random name generators that exist on the 'Net. This code opens an IE instance, navigates to "http://www.behindthename.com/random/", inputs the surname to the form, submits the form, and returns the resulting HTML body.

我还没有解析结果页面 HTML 以返回实际名称，因为这对于您的网站来说会有所不同，但这应该可以帮助您入门.

I haven't parsed the result page HTML to return the actual name, as this will be different for your site, but this should help you get started.

您需要引用Microsoft Internet Controls"才能访问 SHDocVw.InternetExplorer 对象.

You'll need a reference to "Microsoft Internet Controls" to access the SHDocVw.InternetExplorer object.

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

希望这会有所帮助.

这篇关于如何使用 VBA 遍历 Excel 电子表格，将值粘贴到网站表单然后提取结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！