Error 438 Doesn't support property or method, code was working, now it's not

sdrloveshim

New Member
Joined
May 10, 2016
Messages
4
So I had this code working, everything except the 2nd ie tab that's loaded up won't close. So I fiddled around with it some more and I broke it I guess. I don't think I changed anything that would have to do with the line of code that's throwing an error but I may just be missing it. Full code is lower down if you need it. Here is what I believe is relevant code:
Code:
Dim ie As Object
Dim RowCount As Integer
Dim i As Integer
Dim shellWins As ShellWindows
Dim htmlColl2 As MSHTML.IHTMLElementCollection
Dim htmlInput2 As MSHTML.HTMLInputElement

'The code edited out navigates to pilot.com, enters a tracking number, clicks the submit button, brings up a new page with minimal tracking information and clicks a link to load up a new tab with detailed tracking information.

ie.Quit
'it seems I have to close out of the first tab to be able to focus excel on the new one

Set shellWins = New ShellWindows
If shellWins.Count > 0 Then
    Set ie2 = shellWins.Item(1)
    'this finds the new tab
End If

Do Until Not ie2.Busy And ie2.readyState = 4: DoEvents: Loop
       'every so often this will throw an error as well but I don't remember what the error was.

Set htmlColl2 = ie2.document.getElementsByTagName("td")
          'The above line causes Runtime error 438 - object doesn't support this property or method. 
For Each htmlInput2 In htmlColl2
    If htmlInput2.className = "dxgv" Then
        If ActiveCell.Offset(RowCount).Value = "" Then
            ActiveCell.Offset(RowCount).Value = htmlInput2.innerText
            'this puts the status of the shipment in a cell
        Else
            ActiveCell.OffSet(RowCount, 1).Value = htmlInput2.innerText
            'this puts the date of that satus in the next cell
            Exit For
        End If
    End If
Next htmlInput2

ie2.Quit
    'This does not close out of the new tab like I'd expect it too and I haven't solved that yet either.*


Set shellWins = Nothing
Set ie2 = Nothing
The code below is the full length of my current code:
Code:
Sub WaitHalfSec()
    Dim t As Single
    t = Timer + 1 / 2
        Do Until t < Timer: DoEvents: Loop
End Sub

Sub PilotTracking()
Dim ProURL As String
Dim ie As Object
Dim RowCount As Integer
Dim i As Integer
Dim html_Document As HTMLDocument
Dim htmlColl As MSHTML.IHTMLElementCollection
Dim htmlInput As MSHTML.HTMLInputElement
Dim shellWins As ShellWindows
Dim htmlColl2 As MSHTML.IHTMLElementCollection
Dim htmlInput2 As MSHTML.HTMLInputElement

Set ie = CreateObject("InternetExplorer.application")
RowCount = 0
ProURL = "http://www.pilotdelivers.com/"

Do While Not ActiveCell.Offset(RowCount, -5).Value = ""

With ie
    .Visible = False
    .navigate ProURL
    Do Until Not ie.Busy And ie.readyState = 4: DoEvents: Loop
End With

Set Doc = ie.document 'works don't delete

Doc.getElementById("tbShipNum").innerHTML = ActiveCell.Offset(RowCount, -5).Value 'works don't delete

Doc.getElementById("btnTrack").Click 'works don't delete

Do Until Not ie.Busy And ie.readyState = 4: DoEvents: Loop

i = 0
Do While i < 4
    WaitHalfSec
    i = i + 1
Loop

Do Until Not ie.Busy And ie.readyState = 4: DoEvents: Loop

Set htmlColl = ie.document.getElementsByTagName("a")
For Each htmlInput In htmlColl
    If htmlInput.ID = "clickElement" Then
        htmlInput.Click
    Exit For
    End If
Next htmlInput

ie.Quit

Set shellWins = New ShellWindows
If shellWins.Count > 0 Then
    Set ie2 = shellWins.Item(1)
End If

i = 0
Do While i < 6
    WaitHalfSec
    i = i + 1
Loop

Do Until Not ie2.Busy And ie2.readyState = 4: DoEvents: Loop
       'every so often this will throw an error as well but I don't remember what the error was.

Set htmlColl2 = ie2.document.getElementsByTagName("td")
          'The above line causes Runtime error 438 - object doesn't support this property or method. 
For Each htmlInput2 In htmlColl2
    If htmlInput2.className = "dxgv" Then
        If ActiveCell.Offset(RowCount).Value = "" Then
            ActiveCell.Offset(RowCount).Value = htmlInput2.innerText
        Else
            ActiveCell.OffSet(RowCount, 1).Value = htmlInput2.innerText
            Exit For
        End If
    End If
Next htmlInput2

ie2.Quit
    'This does not close out of the new tab like I'd expect it too and I haven't solved that yet either.*

RowCount = RowCount + 1

Loop

Set shellWins = Nothing
Set ie = Nothing
Set ie2 = Nothing

End Sub

Sub WaitHalfSec()
    Dim t As Single
    t = Timer + 1 / 2
        Do Until t < Timer: DoEvents: Loop
End Sub
 
Last edited by a moderator:

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,995
ShellWindows includes Explorer and IE windows, so you need to loop through ShellWindows looking for the IE window/tab with the required LocationURL or LocationName or another distinguishing property.
 

sdrloveshim

New Member
Joined
May 10, 2016
Messages
4
I was able to get it working. For anyone looking for help my working code is shown below. The Do Until Not ie2.busy line throws an error every so often if a page loads very slow. I'm working on adding in an on error statement for this.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Sub PilotTracking()
Dim ProURL As String
Dim ie As Object
Dim ie2 As Object
Dim RowCount As Integer
Dim i As Integer
Dim html_Document As HTMLDocument
Dim htmlColl As MSHTML.IHTMLElementCollection
Dim htmlInput As MSHTML.HTMLInputElement
Dim shellWins As ShellWindows
Dim htmlColl2 As MSHTML.IHTMLElementCollection
Dim htmlInput2 As MSHTML.HTMLInputElement

RowCount = 0
ProURL = "http://www.pilotdelivers.com/"

Do While Not ActiveCell.Offset(RowCount, -5).Value = ""

Set ie = CreateObject("InternetExplorer.application")

With ie
.Visible = False
'threw automation error on the second loop, before moving set ie = nothing group
'after moving set ie = nothing withing the loop this threw error 91, object or with
'block variable not set. Moved set ie = create object within the loop
'stopped throwing errors
.navigate ProURL
Do Until Not ie.Busy And ie.readyState = 4: DoEvents: Loop
End With

Set Doc = ie.document 'works don't delete

Doc.getElementById("tbShipNum").innerHTML = ActiveCell.Offset(RowCount, -5).Value 'works don't delete

Doc.getElementById("btnTrack").Click 'works don't delete

Do Until Not ie.Busy And ie.readyState = 4: DoEvents: Loop

i = 0
Do While i < 4
WaitHalfSec
i = i + 1
Loop

Do Until Not ie.Busy And ie.readyState = 4: DoEvents: Loop

Set htmlColl = ie.document.getElementsByTagName("a")
For Each htmlInput In htmlColl
If htmlInput.ID = "clickElement" Then
htmlInput.Click
Exit For
End If
Next htmlInput

ie.Quit

Set shellWins = New ShellWindows
If shellWins.Count > 0 Then
Set ie2 = shellWins.Item(1)
End If

i = 0
Do While i < 8
WaitHalfSec
i = i + 1
Loop

Do Until Not ie2.Busy And ie2.readyState = 4: DoEvents: Loop
'Threw error 91 obj var or with block var not set
'Didn't change anything, ran a couple times fine then errored again
'changed above do while from i6 to i8

Set htmlColl2 = ie2.document.getElementsByTagName("td")
For Each htmlInput2 In htmlColl2
If htmlInput2.className = "dxgv" Then
If ActiveCell.Offset(RowCount).Value = "" Then
ActiveCell.Offset(RowCount).Value = htmlInput2.innerText
Else
ActiveCell.Offset(RowCount, 1).Value = htmlInput2.innerText
Exit For
End If
End If
Next htmlInput2

ie2.Quit
Set shellWins = Nothing
Set ie = Nothing
Set ie2 = Nothing

RowCount = RowCount + 1

Loop

Set shellWins = Nothing
Set ie = Nothing
Set ie2 = Nothing

End Sub

Sub WaitHalfSec()
Dim t As Single
t = Timer + 1 / 2
Do Until t < Timer: DoEvents: Loop
End Sub
</code><code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">
</code>
 

Forum statistics

Threads
1,081,556
Messages
5,359,547
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top