Search code examples
excelvbaweb-scrapinggetelementbyid

getElementby ID not returning value VBA

I have searched SO and haven't been able to get an answer but I think I am close. I am new to coding so I appreciate the help.

I use Hubspot manage customer details and excel to produce quotes. I want to scrape customer data from Hubspot and have it populate the quote.

The HTML code

<input type="text" property="PropertyRecord { &quot;calculated&quot;: false, 
;createdUserId&quot;: null, &quot;defaultValue&quot;: undefined, 
;deleted&quot;: null, &quot;description&quot;: &quot;A contact's first 
name&quot;, &quot;displayMode&quot;: &quot;current_value&quot;, 
;displayOrder&quot;: 0, &quot;externalOptions&quot;: false, 
;favorited&quot;: true, &quot;favoritedOrder&quot;: 0, 
;fieldType&quot;: &quot;text&quot;, &quot;filterName&quot;: 
&quot;&quot;, &quot;formField&quot;: true, &quot;groupName&quot;: 
;contactinformation&quot;, &quot;hidden&quot;: false, 
;hubspotDefined&quot;: true, &quot;label&quot;: &quot;First Name&quot;, 
;mutableDefinitionNotDeletable&quot;: true, &quot;name&quot;: 
;firstname&quot;, &quot;numberDisplayHint&quot;: null, 
;objectType&quot;: undefined, &quot;options&quot;: List [], 
;optionsAreMutable&quot;: null, &quot;placeholder&quot;: &quot;&quot;, 
;prospectType&quot;: null, &quot;readOnlyDefinition&quot;: true, 
;readOnlyValue&quot;: false, &quot;referencedObjectType&quot;: null, 
;searchable&quot;: true, &quot;showCurrencySymbol&quot;: null, 
;sortable&quot;: true, &quot;textDisplayHint&quot;: null, 
;type&quot;: &quot;string&quot;, &quot;updatedUserId&quot;: null }" 
value="Jonnevie" id="uid-ctrl-54" data-field="firstname" data-selenium-
test="property-input-firstname" autocomplete="off" class="form-control 
private-form__control private-form__control--inline isInline">

I am using the following code to scrape and getting a Run Rime Error 424 

Dim ie As InternetExplorer
Dim ieDoc As HTMLDocument
Dim firstname As Object
Dim url As Variant
Dim ID As Variant
Dim Content As String
Dim i As Integer

Set ieApp = New InternetExplorer

ieApp.Visible = 1

url = "https://app.hubspot.com/sales/3425759/contact/379701/?interaction=note"
ieApp.navigate url

Do While ieApp.Busy:
DoEvents: Loop
Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

Dim ele As Object
Set ieDoc = ieApp.document

For Each ele In ieDoc.getElementById("uid-ctrl-8")

Debug.Print (ele.text)

Next ele

   ieApp.Quit

What would be a better solution?

Thanks

Solution

  • Try the below approach. You can't use .getElementById() in a for loop as you did above. The ID contains a single element. However, I used .querySelector() to do the trick cause the ID's are not static and you can't use it in a for loop either.

    Set firstname = ieDoc.querySelector("[id^='uid-ctrl-']")
MsgBox firstname.getAttribute("value")