尝试从Excel中与WebPage进行交互 [英] Trying to interact with a WebPage from within an Excel

问题描述

• 
  
  
  

  
  

  
  

  嗨全部，
  
  
  
  我正在尝试开发一个宏：
  
  
  1.打开一个网页（https://ethernetpricingtool.openreach.co.uk/QuickQuotes）。按（生成报价）。等待下一页。
  
  
  
  2.按（单引号）。等待下一页。
  
  
  
  3.从Excel中复制信息（邮政编码）并粘贴到文本框中。
  
  
  
  4.按（继续）。等待下一页。
  
  
  
  5.从下拉列表中选择并按（继续）
  
  
  
  6.等待输出数据。
  
  
  
  7.从网页中提取输出数据。
  
  
  
  8.将此数据粘贴到Excel工作簿中。
  
  
  
  有人可以提供帮助吗？
  
  
  
  谢谢你
  
  
  Nour

  解决方案

  你好，

  
  

  要在VBA中自动化IE，请使用  InternetExplorer对象。

  
  

  以下是示例：  

  将IE作为对象Dim 
  设置IE = CreateObject（" InternetExplorer.Application"）
   IE.Visible = False 
   IE.Navigate" https：//ethernetpricingtool.openreach.co。英国/ QuickQuotes" 

  点击按钮或在下拉列表中选择项目列表，您可以按F12查看页面的HTML并找到元素并获取其标签。 

  
  

  例如，您找到按钮"Generate Quote"

  < input name =" page：frm：j_id5：j_id37" class =" btn buttonhomepage" style =" background：rgb（113,90,163）！important;"类型= [提交" value =" Generate Quote"> 

  要点击按钮，您可以使用 

  设置objCollection = IE.document.getElementsByTagName（" input"）
  i = 0 
  当i< objCollection.Length 
  如果objCollection（i）.Type =" submit"并且_ 
   objCollection（i）.Value =" Generate Quote"然后
  设置objElement = objCollection（i）
  结束如果
  i = i + 1 
   Wend 
  如果Not objElement则没有那么
   objElement.Click 
   Else 
   MsgBox（"找不到按钮"）
  退出Sub 
  结束如果

  如果有Id，你可以使用 

   IE.document.getElementById（""）

  读取值从Excel，请使用  Range.Value Property（Excel）

  
  

  因此，要在网站上的文本字段中输入值，您可以使用 

   IE.document.getElementById（""）。Value = Range（" A1"）。Value 

  要将数据从一个工作簿复制到另一个工作簿，我建议您访问类似的主题：

  
  

  https://stackoverflow.com/questions/19351832/copy-from-one-workbook-and-paste-into-another

  
  

  如果您无法编写整个宏，我建议您将需求分成几个。 

  
  

  问候，

  
  

  Celeste

  
  

  • Hi All,
    
    I am trying to develop a macro that will:
    
    1. open a Web page (https://ethernetpricingtool.openreach.co.uk/QuickQuotes). Press the (Generate Quote). wait for the next page.
    
    2. Press the (Single Quote). wait for the next page.
    
    3. copy information (Post code) from excel and Paste into the text box.
    
    4. Press the (continue). wait for the next page.
    
    5. choose from the drop down lists and Press the (continue)
    
    6. Wait for the output data.
    
    7. Extract the output data off of the Web Page.
    
    8. Paste this data into an Excel workbook.
    
    Can anybody help out?
    
    Thanks
    
    Nour

    解决方案

    Hello,

    To automate IE in VBA, please use InternetExplorer object.

    Here is the example:  

        Dim IE As Object
        Set IE = CreateObject("InternetExplorer.Application")
        IE.Visible = False
        IE.Navigate "https://ethernetpricingtool.openreach.co.uk/QuickQuotes"

    To click the button or select items in drop down list, you could press F12 to see the HTML of page and find the elements and get their tags. 

    For example, you find the button "Generate Quote"

    <input name="page:frm:j_id5:j_id37" class="btn buttonhomepage" style="background: rgb(113, 90, 163) !important;" type="submit" value="Generate Quote">

    To click the button, you could use 

        Set objCollection = IE.document.getElementsByTagName("input")
        i = 0
        While i < objCollection.Length
                If objCollection(i).Type = "submit" And _
                   objCollection(i).Value = "Generate Quote" Then
                    Set objElement = objCollection(i)
                End If
            i = i + 1
        Wend
        If Not objElement Is Nothing Then
        objElement.Click
        Else
        MsgBox ("Could not find the button")
        Exit Sub
        End If

    If there is Id, you could use 

     IE.document.getElementById("")

    To read value from Excel, please use Range.Value Property (Excel)

    So to enter value to a text field on the website, you could use 

     IE.document.getElementById("").Value=Range("A1").Value

    To copy data from one workbook to another, i suggest you visit the similar thread:

    https://stackoverflow.com/questions/19351832/copy-from-one-workbook-and-paste-into-another

    I suggest you split your requirement into several ones if you have trouble to write the whole macro. 

    Regards,

    Celeste

    
    

    这篇关于尝试从Excel中与WebPage进行交互的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！