Closing IE with VBA

mlk11

New Member
Joined
Feb 22, 2017
Messages
9
Hello there,

I have a script that opens IE, inputs some data into a form, then copies and pastes the data, closes IE. The script loops, doing the whole process again.

What I'm finding is it doesn't properly close IE, it does close IE, but iexplore.exe is showing in the Task Manager. When the code runs around 10 times, I have 10 instances of iexplore.exe in the Task Manager! I'm hoping to loop the script hundreds of times!

Is there a way to properly 'kill' IE? IE.Quit is only doing half a job really.

Code:
'Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub GetTravelData()
 Dim frompostcode As String
 Dim topostcode As String
 Dim traveldt As Date
 Dim traveltime As Date
 Dim traveltimestr As String
 Dim traveldtstr As String
 
 frompostcode = ThisWorkbook.Sheets("Postcodes").Cells(2, 5).Value
 topostcode = ThisWorkbook.Sheets("Postcodes").Cells(2, 6).Value
 traveldt = ThisWorkbook.Sheets("Postcodes").Cells(4, 6).Value
 traveltime = ThisWorkbook.Sheets("Postcodes").Cells(5, 6).Value
 traveltimehr = Format$(Hour(traveltime), "00")
 traveltimemin = Format$(Minute(traveltime), "00")
 traveltimestr = traveltimehr + ":" + traveltimemin
 
 daystr = Format(traveldt, "ddd")
 mthstr = MonthName(Month(traveldt), True)
 ddstr = Format$(Day(traveldt), "00")
 yyyystr = Format$(Year(traveldt))
 
 traveldtstr = daystr + " " + mthstr + " " + ddstr + " " + yyyystr

 Call GetTravelDataInfo(frompostcode, topostcode, traveldtstr, traveltimestr)
End Sub

Sub GetTravelDataInfo(frmpostcode As String, topostcode As String, traveldtstr As String, traveltime As String)

Dim IE As InternetExplorer
Set IE = New InternetExplorer
Dim TravelineHomePage As HTMLDocument

IE.Navigate2 "www.traveline.info"
IE.Visible = True
Do While IE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
Dim inputfield As Object
Set inputfield = IE.document.getElementById("origin")
inputfield.Value = frmpostcode


Set inputfield = IE.document.getElementById("destination")
inputfield.Value = topostcode

Set inputfield = IE.document.getElementById("datePicker")
inputfield.Value = traveldtstr

Set dropOptions = IE.document.getElementsByClassName("timeIntervals")(0)
 
For Each o In dropOptions.Options
    If o.Value = traveltime Then
        o.Selected = True
        Exit For
    End If
Next

Set objcollection = IE.document.getElementsByTagName("button")
i = 0
While i < objcollection.Length
    If objcollection(i).Type = "submit" Then
     Set objElement = objcollection(i)
    End If
    i = i + 1
Wend
objElement.Click
startrow = 2

While True

Do While IE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
'Sleep 10000
Application.Wait (Now + TimeValue("00:00:06"))
Set journeycollection = IE.document.getElementsByClassName("journey-summary")
'MsgBox journeycollection.Length

For Each o In journeycollection
  If (ThisWorkbook.Sheets("Postcodes").Cells(10, 6).Value > ThisWorkbook.Sheets("Postcodes").Cells(12, 6).Value) Then
     endfortheday = True
     GoTo exitfor
  End If

  If (o.hasAttribute("tabindex")) Then
  Set departtext = o.getElementsByTagName("TD")
  ThisWorkbook.Sheets("Postcodes").Cells(startrow, 10).Value = departtext(1).innerText
  ThisWorkbook.Sheets("Postcodes").Cells(startrow, 11).Value = departtext(3).innerText
  ThisWorkbook.Sheets("Postcodes").Cells(startrow, 12).Value = departtext(4).innerText
  ThisWorkbook.Sheets("Postcodes").Cells(startrow, 13).Value = departtext(6).innerText
  startrow = startrow + 1
  Else
     endfortheday = True
     GoTo exitfor
  End If
Next

exitfor:
If endfortheday Then
  GoTo exitwhile
Else
Set laterbutton = IE.document.getElementById("later")
laterbutton.Click
End If
Wend
exitwhile:
IE.Quit
End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Brilliant! Thanks ever so much!

I've solved one problem, but now found another one.

I can run the code on my work laptop at home, but it throws an error when I use it at work :(

"Run-time error "-2147417848 (80010108) Automation error: The object invoked has disconnected from its clients"

n5o3l5.png


ajle9f.png


I'm utterly confused by this error, I'm using the same laptop. Obviously at home I'm using my personal internet connection and at work a WAN connection. What else could be causing this discrepancy? Additional network drives? :confused:
 
Last edited:
Upvote 0
I think your code make DDOS client. You must speed reduction down. Maybe:
Code:
[COLOR=#101094][FONT=inherit]Do[/FONT][/COLOR][COLOR=#101094][FONT=inherit]While[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ie[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]readystate [/FONT][/COLOR][COLOR=#303336][FONT=inherit]<>[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]4 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Or[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ie[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Busy
[/FONT][/COLOR][COLOR=#303336][FONT=inherit]    Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Wait Now [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#303336][FONT=inherit] TimeValue[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"00:00:01"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] 
Doevents
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Loop[/FONT][/COLOR]
 
Upvote 0
I think your code make DDOS client. You must speed reduction down. Maybe:
Code:
[COLOR=#101094][FONT=inherit]Do[/FONT][/COLOR][COLOR=#101094][FONT=inherit]While[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ie[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]readystate [/FONT][/COLOR][COLOR=#303336][FONT=inherit]<>[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]4 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Or[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ie[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Busy
[/FONT][/COLOR][COLOR=#303336][FONT=inherit]    Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Wait Now [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#303336][FONT=inherit] TimeValue[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"00:00:01"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] 
Doevents
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Loop[/FONT][/COLOR]

Thanks for your response, no such luck unfortunately. (I even tried adjusting the seconds)

14e80hy.png


Hmph.. how infuriating.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top