Error 91: Retrieving Data from Website

[LTofsrud]

Hi everyone,

I am having some problems with trying to use a Microsoft Web Browser control
embedded on a form to pull data from a website. I am currently getting the
Error 91: object variable or with block variable not set.

For some odd reason, the odd time that I step through the code I get no
error so I am not sure if it is an issue with timing or not. Here is the code
that I am using. The goal was to have it running every 60 minutes but I have
it set for two minutes for testing. If anyone has any ideas, or has a better
method for pulling data like this in Access, I'd appreciate it.

Thanks!
LT



Option Compare Database
Option Explicit

Private Sub RefreshWebPage()
wb.Navigate
"http://ets.aeso.ca/Market/Reports/ShortTermOutageReportServlet"

Do While wb.Busy ' this loop is for waiting for
the page to load
DoEvents
Loop
End Sub

Private Sub ExtractData()
Dim mDoc As HTMLDocument
Dim mTables As IHTMLElementCollection

On Error GoTo cmdExtractData_Click_Error

RefreshWebPage

Set mDoc = wb.Document
Set mTables = mDoc.getElementsByTagName("table")

Dim mRows As IHTMLElementCollection
Dim mRow As HTMLTableRow
Dim mCell As HTMLTableCell

Set mRows = mTables(2).Rows
Set mRow = mRows(1)

Dim upperLimit As Integer
upperLimit = 11

Dim index As Integer
Dim rowToInsert As String

For index = 2 To upperLimit
For Each mCell In mRow.Cells
Select Case mCell.nodeName
Case "TH" ' table heading
'Debug.Print "Table Heading = ";
mCell.FirstChild.NodeValue

Case "TD" ' table data
rowToInsert = rowToInsert + "', '" +
mCell.FirstChild.NodeValue
End Select
Next mCell

rowToInsert = Trim(Right(rowToInsert, Len(rowToInsert) - 2))

Dim sqlInsert As String
sqlInsert = "INSERT INTO tblShortTermOutages (PeriodCode,
ReportingPeriod, CoalOutage, GasCogenOutage, GasOtherOutage, OtherOutage)
VALUES (" & rowToInsert & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL sqlInsert, True
DoCmd.SetWarnings True

Set mRow = mRows(index)
sqlInsert = ""
rowToInsert = ""
Next index

Set mDoc = Nothing
Set mTables = Nothing
Set mRow = Nothing
Set mRows = Nothing
Set mCell = Nothing
upperLimit = 0
index = 0


txtLastExtract = Now

subShortTermOutages.Requery

On Error GoTo 0
Exit Sub

cmdExtractData_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmdExtractData_Click of VBA Document Form_frmOutageDataManager"
End Sub

Private Sub cmdStartButton_Click()
Me.TimerInterval = 120000
ToggleControlButtons (False)
ToggleControlPanel (True)
End Sub

Private Sub cmdStopButton_Click()
Dim response As Integer

response = MsgBox("Are you sure you want to stop the data extract?",
vbYesNo + vbQuestion, "Stop Extract?")

If response = vbYes Then
Me.TimerInterval = 0
ToggleControlButtons (True)
ToggleControlPanel (False)
End If
End Sub

Private Sub Form_Load()
ToggleControlButtons (True)
ToggleControlPanel (False)

RefreshWebPage
End Sub

Private Sub Form_Timer()
ExtractData
End Sub

Private Function ToggleControlButtons(toggle As Boolean)
cmdStartButton.Visible = toggle
cmdStopButton.Visible = Not toggle
End Function

Private Function ToggleControlPanel(toggle As Boolean)
imgStartPanel.Visible = toggle
imgStopPanel.Visible = Not toggle
End Function