VBA Web scraper

ThomasA83

Board Regular
Joined
Mar 10, 2009
Messages
95
Hi,

I have been reading through many VBA web scraper posts, however I still can't figure out to get my code to work, so hope you can help me.

The solution I am after is as follow:

1. Go to MSC: Global Container Shipping Company
2. Insert Value from column A2 in the search box (a. in image illustration)
3. Hit search to get the search result
4. If not search result, then continue to step 6.
5. Return 2 values from the search result (b. & c. in the image illustration),
6. Continue procedure from step 2, but with next row (A3), until end of active value in column A

I have managed to get it to work until step 3, but can't get it to work with step 4-6..

Attached is the current Excel file, including the VBA code and the image illustration: WeTransfer Download-link

VBA Code:
Sub Search()
 
    'dimension (declare or set aside memory for) our variables
    Dim objIE As InternetExplorer 'special object variable representing the IE browser
    Dim result As String 'string variable that will hold our result link
 
    'initiating a new instance of Internet Explorer and asigning it to objIE
    Set objIE = New InternetExplorer
 
    'make IE browser visible (False would allow IE to run in the background)
    objIE.Visible = True
 
    'navigate IE to this web page (a pretty neat search engine really)
    objIE.navigate "https://www.msc.com/track-a-shipment"
 
    'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
 
    'in the search box put cell "A2" value
    objIE.document.getElementById("ctl00_ctl00_plcMain_plcMain_TrackSearch_txtBolSearch_TextField").Value = _
      Sheets("Sheet1").Range("A2").Value
 
    'click the 'search' button
    objIE.document.getElementById("ctl00_ctl00_plcMain_plcMain_TrackSearch_hlkSearch").Click
 
    'wait again for the browser
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
 
    'define the Bill of Lading value from the website
    a = objIE.document.getElementsByClassName("bolToggle").Item(0).innerText
    
    Sheets("Sheet1").Range("B2") = a
   
    objIE.Quit
 
'exit our Search subroutine
End Sub

Thanks.
 

ThomasA83

Board Regular
Joined
Mar 10, 2009
Messages
95
Sorry - I had meant to send this part as welll:
I added a few extra lines at the beginning to deal with the country selection and newsletter/cookies pop-ups, so you may or may not want to change these. Otherwise the other adjustments are to either extract the desired info, or to deal with the possibility that there is no available tracking information. I found that IE tends to move a bit too fast for this site, so I would suggest that you use the PAUSE script that I included above if encounter any errors. It's usually the case that IE hasn't found a given HTML element because that element has yet to load.

Let me know how it goes. Fingers crossed.
Hi Dan,

It didn't work, and it debugged at
VBA Code:
Set BOL = objContainer.getElementsByClassName("bolToggle")(0)
.
As you commented on the PAUSE script, I tried changing the PAUSE script to 1# instead of 0.5 on the PAUSE event just before the debug error. And now it works like a charm.

Thank you so much
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
Oh - you really should lead withe conclusion first! LOL Or maybe I should read the whole message before I go back into the code to try and work out why it went wrong. (Chuckle) Anyway, after coming up with all manner of debugging proposals, I finished reading your message - I'm glad it's working. As I suspected, the HTML elements weren't loading up in time before VBA starting to run through it.

If there is nothing else, and it's working like you wanted, can you please mark the post with the latest code solution as being the correct answer so that anyone else who has a similar(-ish?) issue will know that this is the code that eventually worked for you. Apparently, there is a 'Mark as solution' tick icon to the right of the post. Thank you, and let me know if you encounter any more problems.
 

Forum statistics

Threads
1,140,926
Messages
5,703,211
Members
421,282
Latest member
hogie

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
Top