如何循环使用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电子表格中的名称列表，并且我想将一个姓氏粘贴到网页上的文本框中，然后在页面提交之后将返回的电话号码解压缩到单元格中

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

任何人都可以帮忙，还是指向我正确的方向？ >

请问，Ian。

解决方案

这应该有帮助。 b
$ b

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

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

您需要参考Microsoft Internet Controls才能访问SHDocVw。 InternetExplorer对象

 私有函数GetFromSubmittedForm（strSurname As String）As String 
 Dim strReturnBody As String 
 
'需要引用Microsoft Internet Controls
 Dim IE As SHDocVw.InternetExplorer 
设置IE = CreateObject（InternetExplorer.Application）
 
 
 '导航到URL'
 IE.Navigatehttp://www.behindthename.com/random/
 
'无需显示窗口'
 IE.Visible = Fals e 
 
'等待IE加载页面'
虽然IE.Busy：DoEvents：Wend 
尽管IE.ReadyState<> 4：DoEvents：Loop 
 
 
'注意：我已经通过名称指向每个元素，但也可以使用索引_ 
：Forms（random）可以是Forms （0）'
 
 
'按名称选择表单：'
用IE.Document.Forms（random）
 
'输入名称'
 .Surname.Value = strSurname 
 
'提交表单'
 .Submit 
 
'有时您可能需要使用_ 
提交按钮的名称如下：'
'.SubmitButtonName.Click'
结束
 
 
'等待IE加载新的页面'
虽然IE.Busy：DoEvents：Wend 
尽管IE.ReadyState<> 4：DoEvents：Loop 
 
 
'将新页面的正文设置为字符串'
 strReturnBody = IE.Document.Body.InnerHTML 
 
 
 
'/分析身体HTML在这里的代码
 
 GetFromSubmittedForm = strReturnBody 
 
'\CODE分析身体的HTML在这里' 
 
 
 
'关闭IE文档'
 IE.Document.Close 
 IE.Quit 
结束函数
  

希望这有帮助。

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.

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?

Regards, Ian.

解决方案

this should help.

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.

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.

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

Hope this helps.

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