excel - 如何自动检查多个网站从 Excel 加载所需的时间？

Question

我的 Excel 中有大约 100 个网站，我需要检查网站加载所需的时间。目前我正在 Internet Explorer 中使用开发人员工具(F12)> Network> Taken(Column)一一手动执行此操作。有什么办法可以自动进行此检查吗？

Answer 1

不确定您的 VBA 水平，这远非完美，但作为起点，这是我通常用来抓取网站的代码，但我对每个网站打开所需的时间进行了稍微修改。假设您的所有网站都在 A 列中，它将在 B 列中打印打开网站所需的时间。请记住，这是对每个网站运行宏所需的时间进行计时，因此并不完全准确地反射(reflect)实际网站加载所需的时间。

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 site有关引用所需应用程序的信息。