I have around 100 websites in an excel for which i need to check the time taken for website to load. Currently I am manually doing it in Internet Explorer by using developer tool(F12)> Network> Taken(Column) one by one. Is there any way to do this check automatically??
我在excel中有大约100个网站,我需要检查网站加载的时间。目前,我通过逐个使用开发人员工具(F12)>网络>拍摄(列)在Internet Explorer中手动执行此操作。有没有办法自动检查?
1 个解决方案
#1
1
Not sure your level of VBA, and this is far from perfect, but as a starting point this is a code I normally use to scrape websites, but I have modified slightly to time how long it takes each website to open. This assumes you have all your websites in column A, it will print the time taken to open a website in column B. Bare in mind this is timing the time the macro takes to run per website, so not entirely accurate to how long the actual site takes to load.
不确定你的VBA水平,这远非完美,但作为一个起点,这是我通常用来刮网站的代码,但我略微修改了每个网站打开需要多长时间。这假设您在A栏中拥有所有网站,它将打印在B列中打开网站所花费的时间。请记住这是宏在每个网站上运行所需的时间,因此不完全准确到实际的时间长度网站需要加载。
Enum READYSTATE
READYSTATE_UNINITIALIZED = 0
READYSTATE_LOADING = 1
READYSTATE_LOADED = 2
READYSTATE_INTERACTIVE = 3
READYSTATE_COMPLETE = 4
End Enum
Sub ImportWebsiteDate()
'to refer to the running copy of Internet Explorer
Dim ie As InternetExplorer, WDApp As Object, staTime As Double, elapsedTime As Double
'to refer to the HTML document returned
Dim html As HTMLDocument, websiteNo As Integer, curSite As String
websiteNo = Range("A500").End(xlUp).Row
'open Internet Explorer in memory, and go to website
Set ie = New InternetExplorer
For i = 1 To websiteNo
curSite = Cells(i, 1).Value
ie.Visible = False
staTime = Timer
ie.navigate curSite
'Wait until IE is done loading page
Do While ie.READYSTATE <> READYSTATE_COMPLETE
Application.StatusBar = "Trying to go to " & curSite
DoEvents
Loop
elapsedTime = Round(Timer - staTime, 2)
Cells(i, 2).Value = elapsedTime
Next i
Set ie = Nothing
Application.StatusBar = False
Application.ScreenUpdating = False
End Sub
This won't just work if you copy and paste, read this site for info on referencing the required applications.
这不仅适用于复制和粘贴,请阅读此站点以获取有关引用所需应用程序的信息。
#1
1
Not sure your level of VBA, and this is far from perfect, but as a starting point this is a code I normally use to scrape websites, but I have modified slightly to time how long it takes each website to open. This assumes you have all your websites in column A, it will print the time taken to open a website in column B. Bare in mind this is timing the time the macro takes to run per website, so not entirely accurate to how long the actual site takes to load.
不确定你的VBA水平,这远非完美,但作为一个起点,这是我通常用来刮网站的代码,但我略微修改了每个网站打开需要多长时间。这假设您在A栏中拥有所有网站,它将打印在B列中打开网站所花费的时间。请记住这是宏在每个网站上运行所需的时间,因此不完全准确到实际的时间长度网站需要加载。
Enum READYSTATE
READYSTATE_UNINITIALIZED = 0
READYSTATE_LOADING = 1
READYSTATE_LOADED = 2
READYSTATE_INTERACTIVE = 3
READYSTATE_COMPLETE = 4
End Enum
Sub ImportWebsiteDate()
'to refer to the running copy of Internet Explorer
Dim ie As InternetExplorer, WDApp As Object, staTime As Double, elapsedTime As Double
'to refer to the HTML document returned
Dim html As HTMLDocument, websiteNo As Integer, curSite As String
websiteNo = Range("A500").End(xlUp).Row
'open Internet Explorer in memory, and go to website
Set ie = New InternetExplorer
For i = 1 To websiteNo
curSite = Cells(i, 1).Value
ie.Visible = False
staTime = Timer
ie.navigate curSite
'Wait until IE is done loading page
Do While ie.READYSTATE <> READYSTATE_COMPLETE
Application.StatusBar = "Trying to go to " & curSite
DoEvents
Loop
elapsedTime = Round(Timer - staTime, 2)
Cells(i, 2).Value = elapsedTime
Next i
Set ie = Nothing
Application.StatusBar = False
Application.ScreenUpdating = False
End Sub
This won't just work if you copy and paste, read this site for info on referencing the required applications.
这不仅适用于复制和粘贴,请阅读此站点以获取有关引用所需应用程序的信息。