从网站屏幕获取数据到Excel与表单 - VBA [英] Get data from website screen to Excel with form - VBA

问题描述

在Stackoverflow的帮助下，我达到了以下代码;它基本上打开IE，导航到url，填写表单并提交。

  Sub getdata（）
应用程序。 ScreenUpdating = False 
 
设置IE = CreateObject（InternetExplorer.Application）
 IE.Visible = True 
 IE.Navigatehttp://www.bseindia.com/markets /equity/EQReports/BulknBlockDeals.aspx?expandable=7
 
 Application.StatusBar =提交
'等待IE加载... 
虽然IE.Busy 
 DoEvents 
 Wend 
'*************************************** ********************************* 
 delay 5 
 IE.document.getElementbyid（ ctl00_ContentPlaceHolder1_chkAllMarket）。点击
 delay 5 
 IE.document.getElementbyid（ctl00_ContentPlaceHolder1_txtDate）。Value =01/01/2014
 delay 5 
 IE.document.getElementbyid （ctl00_ContentPlaceHolder1_txtToDate）。Value =12/01/2014
 delay 5 
 IE.document.getElementbyid（ctl00_ContentPlaceHolder1_btnS ubmit）点击
 delay 5 
'''IE.document.getElementbyid（ctl00_ContentPlaceHolder1_btnDownload）。点击
'（注释为点击给出选项要保存，打开csv文件）
 
'*************************************** ********************************* 
 Application.StatusBar =Form Submitted
' IE.Quit'将一次工作
'设置IE = Nothing'将退出一次工作
 
 Application.ScreenUpdating = True 
 End Sub 
 
 Private Sub延迟（秒长）
 Dim endTime As Date 
 endTime = DateAdd（s，seconds，Now（））
 Do While Now（）< endTime 
 DoEvents 
循环
 End Sub 
  

问题： / p>

提交表单后，数据将在屏幕上填充，同时还有一个带有相同数据的Excel图标（下载）。csv。

如何在我的活动工作表中获取这些数据（任何方式都很好）。

解决方案

尝试这个

  Sub getdata（）
 Application.ScreenUpdating = False 
 
设置IE = CreateObject（InternetExplorer.Application）
 IE.Visible = True 
 IE.Navigatehttp://www.bseindia.com/markets/equity/EQReports/BulknBlockDeals.aspx?expandable=7 
 
 Application.StatusBar =提交
'等待IE加载... 
虽然IE.Busy 
 DoEvents 
 Wend 
' ************************************************** ******************** 
 delay 5 
 IE.document.getelementbyid（ctl00_ContentPlaceHold点击
 delay 5 
 IE.document.getelementbyid（ctl00_ContentPlaceHolder1_txtDate）。Value =01/01/2014
 delay 5 
 IE.document.getelementbyid （ctl00_ContentPlaceHolder1_txtToDate）Value =12/01/2014
 delay 5 
 IE.document.getelementbyid（ctl00_ContentPlaceHolder1_btnSubmit）。点击
 delay 5 
 
'************************************************ ********************** 
 Application.StatusBar =Form Submitted
 
 Dim tbl As Object，tr As Object ，trCol As Object，td As Object，tdCol As Object 
 Dim row As Long 
 Dim col As Long 
 
 row = 1 
 col = 1 
 
设置tbl = IE.document.getelementbyid（ctl00_ContentPlaceHolder1_divData1）getElementsbytagname（Table）（0）
设置trCol = tbl.getElementsbytagname（TR）
 
对于每个tr在trCol 
设置tdCol = tr.getElementsbytagname（TD）
对于每个td在tdCol 
单元格（row，col）= td.innertext 
 col = col + 1 
下一个
 col = 1 
 row = row + 1 
下一个
 
 
 IE.Quit'将退出一次工作
设置IE = Nothing将退出行一次工作
 
 Application.ScreenUpdating = True 
 End Sub 
 
私有子延迟（秒长）
 Dim endTime As Date 
 endTime = DateAdd（s，seconds，Now（））
 Do While Now（） < endTime 
 DoEvents 
循环
 End Sub 
  

With help from Stackoverflow, I reached to the following code; it basically opens IE, navigate to the url, fills the form and submit.

Sub getdata()
    Application.ScreenUpdating = False

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.Navigate "http://www.bseindia.com/markets/equity/EQReports/BulknBlockDeals.aspx?expandable=7"

    Application.StatusBar = "Submitting"
    ' Wait while IE loading...
    While IE.Busy
        DoEvents
    Wend
    ' **********************************************************************
    delay 5
    IE.document.getElementbyid("ctl00_ContentPlaceHolder1_chkAllMarket").Click
    delay 5
    IE.document.getElementbyid("ctl00_ContentPlaceHolder1_txtDate").Value = "01/01/2014"
    delay 5
    IE.document.getElementbyid("ctl00_ContentPlaceHolder1_txtToDate").Value = "12/01/2014"
    delay 5
    IE.document.getElementbyid("ctl00_ContentPlaceHolder1_btnSubmit").Click
    delay 5
    '''IE.document.getElementbyid("ctl00_ContentPlaceHolder1_btnDownload").Click
    '''(Commented as the click gives the option asking to save, open the csv file)

    '**********************************************************************
    Application.StatusBar = "Form Submitted"
    'IE.Quit            'will uncomment line once working
    'Set IE = Nothing   'will uncomment line once working

    Application.ScreenUpdating = True
End Sub

Private Sub delay(seconds As Long)
    Dim endTime As Date
    endTime = DateAdd("s", seconds, Now())
    Do While Now() < endTime
        DoEvents
    Loop
End Sub

The Problem:

Once the form is submitted, the data gets populated on screen as well as there is an Excel icon (Download) with the same data in csv.

How can I get this data (anyone way is fine) in my active worksheet.

解决方案

Try this

Sub getdata()
    Application.ScreenUpdating = False

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.Navigate "http://www.bseindia.com/markets/equity/EQReports/BulknBlockDeals.aspx?expandable=7"

    Application.StatusBar = "Submitting"
    ' Wait while IE loading...
    While IE.Busy
        DoEvents
    Wend
    ' **********************************************************************
    delay 5
    IE.document.getelementbyid("ctl00_ContentPlaceHolder1_chkAllMarket").Click
    delay 5
    IE.document.getelementbyid("ctl00_ContentPlaceHolder1_txtDate").Value = "01/01/2014"
    delay 5
    IE.document.getelementbyid("ctl00_ContentPlaceHolder1_txtToDate").Value = "12/01/2014"
    delay 5
    IE.document.getelementbyid("ctl00_ContentPlaceHolder1_btnSubmit").Click
    delay 5

    '**********************************************************************
    Application.StatusBar = "Form Submitted"

    Dim tbl As Object, tr As Object, trCol As Object, td As Object, tdCol As Object
    Dim row As Long
    Dim col As Long

    row = 1
    col = 1

    Set tbl = IE.document.getelementbyid("ctl00_ContentPlaceHolder1_divData1").getElementsbytagname("Table")(0)
    Set trCol = tbl.getElementsbytagname("TR")

    For Each tr In trCol
        Set tdCol = tr.getElementsbytagname("TD")
        For Each td In tdCol
            Cells(row, col) = td.innertext
            col = col + 1
        Next
        col = 1
        row = row + 1
    Next


    IE.Quit            'will uncomment line once working
    Set IE = Nothing   'will uncomment line once working

    Application.ScreenUpdating = True
End Sub

Private Sub delay(seconds As Long)
    Dim endTime As Date
    endTime = DateAdd("s", seconds, Now())
    Do While Now() < endTime
        DoEvents
    Loop
End Sub

这篇关于从网站屏幕获取数据到Excel与表单 - VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！