将网站中的数据提取到Excel文件中，其中包含超链接 [英] Fetch data from a Website into an Excel file with the hyperlinks in it
本文介绍了将网站中的数据提取到Excel文件中，其中包含超链接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
当我使用宏从网页将数据提取到Excel文件时，它会将数据保存在Excel文件中。我的要求是它应该使用网页中的超链接保存Excel中的数据。如果我们点击Excel中的任何超链接，它应该转到特定网站并获取数据并将信息保存在excel文件或工作表中。我希望这是有道理的。任何帮助将不胜感激。
我的宏代码如下：
When I fetch data from a webpage to an Excel file using Macro it saves the data in the Excel file as it is supposed to be. My requirement is that it should save the data in the Excel with the hyperlinks in the webpage and If we click any hyperlink in the Excel it should go to the particular website and fetch the data and save the information in the excel file or sheet. I hope it makes sense. Any help will be greatly appreciated.
My Macro code below:
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
推荐答案
看看这里： Hyperlinks.Add Method（Excel） [ ^ ]
这篇关于将网站中的数据提取到Excel文件中，其中包含超链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
