Assign a value from excel to a html input box through excel vba

Question

I have some ISIN-codes from bonds in an excel sheet (column A). I would like to find the corresponding rating from the site "https://www.moodys.com". As this should be done on a regular basis, I would like to automate this process through Excel VBA. The website does not use any ID's so I can't use getElementById.

I tried using through "getElementsByClassName", but this does not work. Also I am not sure if the "search-widget" is the correct class name.

The Error message is: "Object doesn't support this property or method (Error 438)"

Many thanks in advance! Pieterjan

Sub SearchBot()

Dim objIE As InternetExplorer 'special object variable representing the IE browser

Nbr = ThisWorkbook.Sheets("Sheet1").Cells(1, 1).End(xlDown).Row - 1

With Worksheets("Sheet1").Range(Cells(2, 2), Cells(100000, 6)) .ClearContents End With

For i = 1 To 1 'Nbr

'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.moodys.com"

'wait here a few seconds while the browser is busy
Application.Wait (Now + TimeValue("00:00:04"))
'Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

Do
DoEvents
Loop Until objIE.readyState = 4

objIE.document.getElementsByClassName("search-widget").Value = Sheets("Sheet1").Range("A" & i + 1).Value

...

Alex de Jong Alex de Jong · Accepted Answer · 2019-10-09T12:15:07

The getElementsByClassName method returns an array of all elements with that classname, even if that is only one element. So if you know it is the first and only element with that classname, you can use getElementsByClassName("search-widget")(0).Value

Furthermore, you can find the right classname by inspecting the pages html. In chrome, you can do that by right clicking the element and press "inspect". The only searchbar I saw on the site has "typeahead search-box-input left" as classname by the way.

Assign a value from excel to a html input box through excel vba

1 Answers