IE Object won't close, app eventually crashes 1

[1DMF]

Hi,

I have the following code

' load emails
Set emails = CurrentDb.OpenRecordset("SELECT DISTINCTROW HTMLBody FROM Email WHERE Subject LIKE 'New Proposition*'")

'loop records
Do While Not emails.EOF
    
    'load HTML
    Set IE = CreateObject("InternetExplorer.Application")
    
    IE.Visible = True
    IE.navigate "about:blank"
    IE.Document.write (emails.Fields("HTMLBody"))

    ' get input elements
    Set HTMLDoc = IE.Document
    Set InputElements = HTMLDoc.getElementsByTagName("INPUT")
        
    ' set vars
    sSQL = ""
    iContact = 0
    
    'loop inputs
    For i = 0 To (InputElements.length - 1)
    
        ' check company name
        If InputElements(i).Name = "CompanyName" Then
                iContact = Nz(DLookup("ContactID", "Contacts", "(ContactTypeID = 'Member' OR ContactTypeId = 'X-Member') AND CompanyName = '" & InputElements(i).Value & "'"), 0)
                sSQL = "('" & InputElements(i).Value & "'"
        End If
        
        If iContact = 0 And InputElements(i).Name = "FullName" Then
            Dim iName As Variant
            Dim sName As String
            iName = Split(InputElements(i).Value, " ", , vbTextCompare)
            For x = 0 To UBound(iName) - 1
                If (iContact <> 0) Then
                    iContact = Nz(DLookup("ContactID", "Contacts", "(ContactTypeID = 'Member' OR ContactTypeId = 'X-Member') AND LastName = '" & iName(x) & "'"), 0)
                End If
            Next x
            sSQL = sSQL & ",'" & InputElements(i).Value & "'"
        End If
        
        If InputElements(i).Name = "Hear" Then
            sHear = InputElements(i).Value
        End If
        
        If InputElements(i).Name = "Other" And Nz(InputElements(i).Value, "") <> "" Then
            sHear = sHear & " : " & InputElements(i).Value
        End If
                                        
    Next i

    ' check if found and add to table

    If iContact <> 0 Then
        sSQL = sSQL & ",'" & sHear & "'," & iContact & ")"
        CurrentDb.Execute ("INSERT INTO Matched (CompanyName,Adviser,Hear,ContactID) VALUES " & sSQL)
    End If
    
    

    Set InputElements = Nothing
    Set HTMLDoc = Nothing
    Set IE = Nothing
    emails.MoveNext
    
Loop

However as the code loops it keeps opening a new browser again and again until the app crashes with an automation error.

why doesn't the 'Set = Nothing' for all open objects clear the object and close the IE window?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

setting a variable equal and object does not copy the object but only creates a pointer to the object.

Public Sub demo()
Dim frm As Access.Form
DoCmd.OpenForm "frmOne"
'This only makes a pointer to the form object
Set frm = Forms("frmOne")
Set frm = Nothing
Debug.Print frm Is Nothing
'frm is nothing the pointer is gone but the Form is still
'open
End Sub

 

[MajP]

http://oreilly.com/pub/a/oreilly/windows/ron/objects.html

Here is some discussion on that point, that may be help or confuse.

 

[1DMF]

Gotcha, so how do i close the IE object?

If I try to use the same object for each HTMLBody I get a permission denied error?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

confuse!

I'm not passing a rountine anything byval or byref ?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

Public Sub demo()
'load HTML
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate "about:blank"
'if you want to close the application
MsgBox "Ok to quit", vbInformation
ie.Quit
End Sub

 

[1DMF]

Thanks,

Talk about mind freeze!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts