VBA [EXCEL 2013]:自动化:在同一浏览器窗口/选项卡中打开链接 [英] VBA [EXCEL 2013]: Automation: Open Links in Same Browser Window / Tab
问题描述
我想用VBA(Excel)解析一长串本地网页( .HTM
文件),然后将一些数据提取到excel中.该程序需要抓取9000多个网页.这是一个例子:
I want to parse with VBA (Excel) a long list of local webpages (.HTM
files) and extract some data into excel. There are more than 9000 webpages which the program needs to scrape. This is an example:
> C:\Users\User_ID\Webpages\BS_1000.HTM.htm
C:\Users\User_ID\Webpages\BS_1001.HTM.htm
C:\Users\User_ID\Webpages\BS_1002.HTM.htm
C:\Users\User_ID\Webpages\BS_1003.HTM.htm
C:\Users\User_ID\Webpages\BS_1006.HTM.htm
C:\Users\User_ID\Webpages\BS_1007.HTM.htm
C:\Users\User_ID\Webpages\BS_1011.HTM.htm
C:\Users\User_ID\Webpages\BS_1012.HTM.htm
C:\Users\User_ID\Webpages\BS_1015.HTM.htm
C:\Users\User_ID\Webpages\BS_1016.HTM.htm
[... and the list goes on ...]
这是VBA:
<!-- language: lang-HTML -->
For startNumber = 1 To TotalProfiles
Dim ie As InternetExplorerMedium
Set ie = New InternetExplorerMedium
ie.Visible = True
Application.StatusBar = "Loading profile " & ProfileNumber & " from a total of " & TotalProfiles & " profiles"
Set currentProfile = Worksheets("List_of_Files").Range("B" & CurrentRowPosition)
ie.navigate currentProfile
Application.StatusBar = "Loading profile: " & ProfileNumber & "; file location: " & currentProfile
Do While ie.READYSTATE <> READYSTATE_COMPLET
DoEvents
Loop
Application.StatusBar = "Storing " & currentProfile & " information into HTMLElement"
Set html = ie.document
Set ie = Nothing
[some code here...]
问题是我当前的代码在新的IE窗口中打开了每个页面(而没有关闭前一个窗口).要抓取9000多个网页,这很快就会成为一个很大的问题.
The problem is that my current code opens each page in a new IE window (without closing the previous). With more than 9000 webpages to scrape, this can very soon be a very big problem.
我将Internet Explorer 11(在Windows 7 Enterprise SP1上)与Microsoft Office 2013一起使用.
I use Internet Explorer 11 (on Windows 7 Enterprise SP1) with Microsoft Office 2013.
我想要的是IE应该在同一个标签页中打开每个网页(完成分析后,只需刷新已在使用中的标签页并加载下一页-或至少在完成分析并打开后关闭窗口即可)新"窗口中的下一个网页).可悲的是,直到现在我才设法找到解决方案.任何帮助将不胜感激.
What I would like is that IE should open each webpage in the same tab (pretty much just refresh the already "in use" tab after finishing parsing and load the next page - or at least close the window after finishing parsing and open the next webpage in a "new" window). Sadly, I didn't manage to find a solution until now. Any help would be appreciated.
推荐答案
每次打开新窗口的原因是,您在此循环的开头用此行告诉它 Set ie = New InternetExplorerMedium
The reason it opens a new window each time is that you tell it to at the beginning of your loop with this line Set ie = New InternetExplorerMedium
有两种解决方法.
- 在循环之前启动IE,然后在循环完成后退出IE:
像这样:
Dim ie As InternetExplorerMedium
Set ie = New InternetExplorerMedium
ie.Visible = True
For startNumber = 1 To TotalProfiles
Application.StatusBar = "Loading profile: " & ProfileNumber & "; file location: " & currentProfile
Do While ie.READYSTATE <> READYSTATE_COMPLET
DoEvents
Loop
Set currentProfile = Worksheets("List_of_Files").Range("B" & CurrentRowPosition)
ie.navigate currentProfile
Application.StatusBar = "Storing " & currentProfile & " information into HTMLElement"
Set html = ie.document
[some code here...]
Next
Set html = Nothing
ie.Quit
Set ie = Nothing
- 每次结束循环之前都退出IE实例(可能不如第一种方法有效)
像这样:
For startNumber = 1 To TotalProfiles
Dim ie As InternetExplorerMedium
Set ie = New InternetExplorerMedium
ie.Visible = True
Application.StatusBar = "Loading profile " & ProfileNumber & " from a total of " & TotalProfiles & " profiles"
Set currentProfile = Worksheets("List_of_Files").Range("B" & CurrentRowPosition)
ie.navigate currentProfile
Application.StatusBar = "Loading profile: " & ProfileNumber & "; file location: " & currentProfile
Do While ie.READYSTATE <> READYSTATE_COMPLET
DoEvents
Loop
Application.StatusBar = "Storing " & currentProfile & " information into HTMLElement"
Set html = ie.document
[some code here...]
Set html = Nothing
ie.Quit
Set ie = Nothing
Next
这篇关于VBA [EXCEL 2013]:自动化:在同一浏览器窗口/选项卡中打开链接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!