从网页导入表格到excel [英] Import tables from a webpage to excel

问题描述

我刚刚拥有一个高级中级的excel和中级的VBA的背景与excel。我想要做的是从这样的网页导入表格： http://www.admision.unmsm.edu.pe/res20130914/A/011/0.html

该网页显示39个链接，每个链接包含一个表。
所以我想知道一个自动的方式将所有这些表导入到excel。

解决方案

来自所有链接的数据。

  Sub Extract_data（）
 
 Dim url As String，links_count As Integer 
 Dim i As Integer，j As Integer，行As Integer 
 Dim XMLHTTP As Object，html As Object 
 Dim tr_coll As Object，tr As Object 
 Dim td_coll As Object，td作为对象
 
 links_count = 39 
对于i = 0到links_count 
 
 url =http://www.admision.unmsm.edu.pe/res20130914/ A / 011 /&我& .html
 
设置XMLHTTP = CreateObject（MSXML2.XMLHTTP）
 XMLHTTP.OpenGET，url，False 
 XMLHTTP.send 
 
设置html = CreateObject（htmlfile）
 html.body.innerHTML = XMLHTTP.ResponseText 
 
设置tbl = html.getelementsbytagname（Table）
 
设置tr_coll = tbl（0）.getelementsbytagname（TR）
 
对于每个tr在tr_coll 
j = 1 
设置td_col = tr.getelementsbytagname（TD）
 
对于每个td在td_col 
单元格（行+ 1，j）.Value = td.innerText 
j = j + 1 
下一个
 row = row + 1 
下一个
下一个
 
 MsgBoxDone
 End Sub 
  

I just have an upper-intermediate level of excel and intermediate background of VBA with excel. What I want to do is to import tables from a webpage like this which appears in the link: http://www.admision.unmsm.edu.pe/res20130914/A/011/0.html

That webpage shows 39 links and each link contains a table. So I would like to know an automatic way to import all these tables to excel.

解决方案

This code get the data from all the links.

Sub Extract_data()

    Dim url As String, links_count As Integer
    Dim i As Integer, j As Integer, row As Integer
    Dim XMLHTTP As Object, html As Object
    Dim tr_coll As Object, tr As Object
    Dim td_coll As Object, td As Object

    links_count = 39
    For i = 0 To links_count

        url = "http://www.admision.unmsm.edu.pe/res20130914/A/011/" & i & ".html"

        Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
        XMLHTTP.Open "GET", url, False
        XMLHTTP.send

        Set html = CreateObject("htmlfile")
        html.body.innerHTML = XMLHTTP.ResponseText

        Set tbl = html.getelementsbytagname("Table")

        Set tr_coll = tbl(0).getelementsbytagname("TR")

        For Each tr In tr_coll
            j = 1
            Set td_col = tr.getelementsbytagname("TD")

            For Each td In td_col
                Cells(row + 1, j).Value = td.innerText
                j = j + 1
            Next
            row = row + 1
        Next
    Next

    MsgBox "Done"
End Sub

这篇关于从网页导入表格到excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！