Thursday, January 3, 2008

Calculate speed of web site connection

This macro is a small example how to measure the time a webpage is loaded:

Sub CalculateSpeed()
Dim sch As Object
Dim xmlUrl As String
Dim fName As String
Dim ws1 As Worksheet
Dim counter As Long, k As Long
Set ws1 = ActiveSheet ' Result sheet ("sheet1")
counter = InputBox("How many times loading?")
k = 2
fName = "http://www.YOURWEBSITE.com" ' internet adres
Cells.ClearContents
ws1.[A1:C1] = Array("StartTime", "EndTime", "TotalTime")
ws1.[A1:C1].Font.Bold = True 'Exit Sub
For i = 1 To counter
Set sch = CreateObject("InternetExplorer.Application")
sch.Navigate fName

'Optional to make visible explorer
sch.Visible = True
strtime = Now()

'Wait until page is loaded
Do While sch.Busy
DoEvents
Loop
endTime = Now()
totTim = Left(Right(endTime, 5), 2) - Left(Right(strtime, 5), 2)
ws1.Cells(k, 1) = Format(strtime, "hh:mm.ss")
ws1.Cells(k, 2) = Format(endTime, "hh:mm.ss")
ws1.Cells(k, 3) = totTim
k = k + 1
sch.Quit
Next i
Set sch = Nothing
End Sub

To use this code:
1. Open Excel
2. Open VBA editor (ALT+F11)
3. Insert new module
4. Paste code.
5. Run Macro

1 comment:

  1. Jeroen, probeer tzt ook nog eens een test te maken voor de grey-ware van mensen.
    al was het maar voor hun zelf-kennis.
    :-)
    Veel succes verder,
    Ferrie.

    ReplyDelete