0

Below is the code for simple IE automation which simply inputs order number like 1413105088 and postal code which is always 78759 and clicks on submit button and then from the result page it gets the tracking number like 017136295201034 and puts them in column C.

It works as expected but as IE is not so reliable and slow I was wondering if there is a faster way to do this process and if not can I make it atleast reliable so that it doesn't fails after click on the submit button, meaning the 

   Do While IE.Busy Or IE.ReadyState <> 4
        DoEvents
    Loop

after 

        .document.getElementsByClassName("button_text")(3).Click

fails as it doesn't really checks if the ie page has finished loading.

I am asking this as I have to do this for 100s of such request. Thanks in advance.

Full code: 

Sub test()

Dim urL As String, orderNum As String
Dim trackingNum, prodDetail, cet
Dim i As Long, fI As Long
Dim IE

urL = "https://fisher-price.mattel.com/webapp/wcs/stores/servlet/OrderStatusGuestView?catalogId=10101&langId=-1&storeId=10151&krypto=prThs8zyeWG0bkF9ajSr%2FCnzmv1TKodtTEw0EdXtC7NjEmfD3cb6Z75umdkcXCiEPFxvkd0TfHkOswm3ZcMp8sbrU2doZFa6TxVbI%2BW1Lzk%3D"

fI = MAIN.Range("B" & Rows.Count).End(xlUp).Row

Set IE = CreateObject("InternetExplorer.Application")

With IE
    .Visible = True

For i = 2 To fI

    orderNum = Trim(MAIN.Range("B" & i).Value)        'Sample ordernum = 1413105088

    If orderNum <> "" Then
        .navigate urL

        Do While IE.Busy Or IE.ReadyState <> 4
            DoEvents
        Loop

        .document.getelementbyid("orderNumber").Value = orderNum
        .document.getelementbyid("postalCode").Value = 78759
        .document.getElementsByClassName("button_text")(3).Click

        Application.Wait Now + TimeValue("00:00:02")
        Do While IE.Busy Or IE.ReadyState <> 4
            DoEvents
        Loop

        prodDetail = .document.getElementsByClassName("productDetails")(0).innerText
        If InStr(prodDetail, "Tracking :") > 0 Then
            cet = Split(prodDetail, "Tracking :")
            trackingNum = Trim(cet(1))
            MAIN.Range("C" & i).Value = trackingNum
        Else
            MAIN.Range("C" & i).Value = "N/A"
        End If

    End If

Next i

End With

IE.Quit
Set IE = Nothing


End Sub
Community
  • 1
  • 1
Rohan
  • 319
  • 1
  • 5
  • 18
  • I'd include `IE.Document.ReadyState <> "complete"` into you're loop to make sure it's loaded - As you really are simulating IE interaction (and not just scraping), this is the only way I know of – Jeremy Nov 23 '17 at 12:43
  • @Jeremy : I dont know much about the working of the browser, but my understanding is that the code looks for the browser title or tab instead of the page. Hence as soon as the tab or the browser title is loaded it looks for the item. Please correct me if I am wrong. – nishit dey Nov 23 '17 at 12:55

1 Answers1

1

Even i faced this problem where the Do While... Loop did not load properly so i used the below code

x = 0
Do until x = 1
  if IsObject(.document.getelementbyid("orderNumber")) Then
    .document.getelementbyid("orderNumber").Value = orderNum
    .document.getelementbyid("postalCode").Value = 78759
    .document.getElementsByClassName("button_text")(3).Click
    x = 1
  Else
    Application.Wait Now + TimeValue("00:00:02")
  End if
Loop

Working: Since x=0 it will go insde the Loop and since the IsObject(.document.getelementbyid("orderNumber")) was not found so it will wait for two second and loop will continue till it find the ordernumber or else it will make the value as x=1 and exit the loop.

Caution: if your code does not work then this code will run till eternity. For which you can set the limit the loop.

nishit dey
  • 458
  • 1
  • 7
  • 21
  • I am trying to check if `If IsObject(.document.getElementsByClassName("productDetails"))` and I don't know why it results true and I get the error of with or with block not set – Rohan Nov 23 '17 at 13:11
  • It is working fine for me. It seems you have not used the code correctly it correct. Thanks – nishit dey Nov 23 '17 at 13:31