使用VBA在Excel中导入网页数据

Question

我想将MutualFundsPortfolioValues导入Excel。我不知道如何从一个网站导入数据，我需要做的是在所选公司的2个不同日期内将网络数据导入到Excel中。

当我将日期输入到B3和B4单元格并单击Commandbutton1时， Excel中可能会从我的网页的所有数据导入到我的Excel表格“结果”

例如：

date 1: 04/03/2013 <<<< " it will be in sheets "input" cell B3 
date 2 : 11/04/2013 <<<<< " it will be in sheet "input " cell B4 
choosen companies <<<<<< its Range "B7: B17" 

我添加了一个样本Excel工作表和网页的PRINTSCREEN .. 任何想法？

我的网页，网址：

http://www.spk.gov.tr/apps/MutualFundsPortfolioValues/FundsInfosFP.aspx?ctype=E&submenuheader=0

示例Excel和数据的样品图片： http://uploading.com/folders/get/b491mfb6/excel-web-query

Answer 1

这里是导入用IE自动化数据的代码。

输入参数
开始日期（在Sheet按照下面的截图输入）= B3
结束日期= B4
Şirketler= B5（它允许哪些应该出现下面B5等倍数值）

页输入ViewSource的Fileds

代码是如何工作的：

• 的代码创建Internet Explorer中的对象并导航到 site
• 等待，直到页面完全加载和准备。 （IE。readyState的）
• 创建对象的HTML类
• 提交按钮
• 迭代直通表dgFunds的每一行输入值从工作表Sheet（txtDateBegin，txtDateEnd，lstCompany）
• 点击该输入字段和转储到Excel中Sheet2中

代码：

Dim IE As Object 
Sub Website() 


    Dim Doc As Object, lastRow As Long, tblTR As Object 
    Set IE = CreateObject("internetexplorer.application") 
    IE.Visible = True 

navigate: 
    IE.navigate "http://www.spk.gov.tr/apps/MutualFundsPortfolioValues/FundsInfosFP.aspx?ctype=E&submenuheader=0" 

    Do While IE.readystate <> 4: DoEvents: Loop 

    Set Doc = CreateObject("htmlfile") 
    Set Doc = IE.document 

    If Doc Is Nothing Then GoTo navigate 

    Set txtDtBegin = Doc.getelementbyid("txtDateBegin") 
    txtDtBegin.Value = Format(Sheet1.Range("B3").Value, "dd.MM.yyyy") 

    Set txtDtEnd = Doc.getelementbyid("txtDateEnd") 
    txtDtEnd.Value = Format(Sheet1.Range("B4").Value, "dd.MM.yyyy") 


    lastRow = Sheet1.Range("B65000").End(xlUp).row 
    If lastRow < 5 Then Exit Sub 

    For i = 5 To lastRow 

     Set company = Doc.getelementbyid("lstCompany") 
     For x = 0 To company.Options.Length - 1 
      If company.Options(x).Text = Sheet1.Range("B" & i) Then 
       company.selectedIndex = x 

       Set btnCompanyAdd = Doc.getelementbyid("btnCompanyAdd") 
       btnCompanyAdd.Click 
       Set btnCompanyAdd = Nothing 

       wait 
       Exit For 
      End If 
     Next 
    Next 


    wait 

    Set btnSubmit = Doc.getelementbyid("btnSubmit") 
    btnSubmit.Click 

    wait 

    Set tbldgFunds = Doc.getelementbyid("dgFunds") 
    Set tblTR = tbldgFunds.getelementsbytagname("tr") 



    Dim row As Long, col As Long 
    row = 1 
    col = 1 

    On Error Resume Next 

    For Each r In tblTR 

     If row = 1 Then 
      For Each cell In r.getelementsbytagname("th") 
       Sheet2.Cells(row, col) = cell.innerText 
       col = col + 1 
      Next 
      row = row + 1 
      col = 1 
     Else 
      For Each cell In r.getelementsbytagname("td") 
       Sheet2.Cells(row, col) = cell.innerText 
       col = col + 1 
      Next 
      row = row + 1 
      col = 1 
     End If 
    Next 

    IE.Quit 
    Set IE = Nothing 

    MsgBox "Done" 

End Sub 

Sub wait() 
    Application.wait Now + TimeSerial(0, 0, 10) 
    Do While IE.readystate <> 4: DoEvents: Loop 
End Sub 

输出继电器表在表2

HTH

Answer 2

你可以read about it by clicking here然后使用宏录制器来录制这样做，这会给你一些代码来一起工作，如果你需要更多的帮助，只是问

这里是堆栈溢出了另一个问题：Importing data from web page with diffrent Dates in excel using VBA code

基本上你要一个QueryTable对象添加到Excel中的活动工作表的QueryTables集合。

下面是查询表的MSDN参考：MSDN Library: Excel 2007 Querytables Add method