从一个网站获取数据到一个Excel文件与其中的超链接

本文关键字：一个 超链接 文件 数据 网站 获取 Excel | 更新日期: 2023-09-27 18:13:51

当我使用宏从网页获取数据到Excel文件时，它将数据保存在Excel文件中，因为它应该是。我的要求是，它应该保存在Excel中的数据与网页中的超链接，如果我们点击Excel中的任何超链接，它应该去特定的网站，获取数据，并保存在Excel文件或表中的信息。我希望这是有意义的。如有任何帮助，我将不胜感激。

下面的宏代码:

Sub GetTable()
Dim ieApp As InternetExplorer
Dim ieDoc As Object
Dim ieTable As Object
Dim clip As DataObject
'create a new instance of ie
Set ieApp = New InternetExplorer
'you don’t need this, but it’s good for debugging
ieApp.Visible = True
'assume we’re not logged in and just go directly to the login page
ieApp.Navigate "website link"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
Set ieDoc = ieApp.Document
'fill in the login form – View Source from your browser to get the control names
With ieDoc.forms(0)
    .user.Value = "UserNmae
    .Password.Value = "password"
    .submit
End With
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
'now that we’re in, go to the page we want
ieApp.Navigate "final webpage link"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
'get the table based on the table’s id
Set ieDoc = ieApp.Document
Set ieTable = ieDoc.all.Item("AutoNumber1")
'copy the tables html to the clipboard and paste to teh sheet
If Not ieTable Is Nothing Then
    Set clip = New DataObject
    clip.SetText "<html>" & ieTable.outerHTML & "</html>"
    clip.PutInClipboard
    Sheet1.Select
    Sheet1.Range("A1").Select
    Sheet1.PasteSpecial "Unicode Text"
End If
'close 'er up
ieApp.Quit
Set ieApp = Nothing
End Sub

从一个网站获取数据到一个Excel文件与其中的超链接

您可以使用此代码从web浏览器获得所有超链接，我认为您可以轻松地从列表框导出数据到excel 

Dim links As HtmlElementCollection = WebBrowser1.Document.Links
        For Each link As HtmlElement In links
            ListBox1.Items.Add(link.GetAttribute("href"))
        Next
源

相关文章：