Get Element Name using VBA

K1600

Board Regular
Joined
Oct 20, 2017
Messages
185
I am trying to write a code to open internet explorer, go to the MOT checking website and run a check on a registration number. I then want it to put the date the MOT expires into my sheet. I would ideally like it to pick the registration from the sheet in say cell A2 and place the result alongside it in B2 but I haven't managed to work that bit out yet so the registration is currently listed in the code.

The issue I am having is the element name in the developer tools changes each time internet explorer opens. Is there a way that I can get the element name as part of the process once IE opens?

The code on the DOM Explorer is:
CSS:
input name="320320320b260e2c6c2df0a7daf5c24b203c6d3f1609806998" class="form-control" id="320320320b260e2c6c2df0a7daf5c24b203c6d3f1609806998" type="text" value=""

The code I currently have is below. I am hoping that I can select the MOT expiry in a similar way to how I would enter the registration:

Code:
Option Explicit

Sub ie_automated()

Dim IE As InternetExplorer
Dim sURL As String

sURL = "www.check-mot.service.gov.uk"
Set IE = New InternetExplorer

With IE
    .AddressBar = 0
    .StatusBar = 0
    .Toolbar = 0
    
    .Visible = True
    .Navigate sURL
    
    Do Until Not .Busy And .ReadyState = 4
    DoEvents
Loop

    .Document.all("???").Value = "YB62XDY"
    Application.Wait Now + TimeSerial(0, 0, 2)
    
End With

Do Until Not IE.Busy
DoEvents
Loop
IE.Document.all("submit").Click

End Sub

Thanks in advance.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
You could get it by the class name:


VBA Code:
Set list = html.getElementsByClassName("form-control")

Set ele = ie.Document.querySelector(".form-control")



Can you post the entire HTML? I am not in the UK, cannot load the site and have no VPN at the moment.
 
Upvote 0
You could get it by the class name:


VBA Code:
Set list = html.getElementsByClassName("form-control")

Set ele = ie.Document.querySelector(".form-control")



Can you post the entire HTML? I am not in the UK, cannot load the site and have no VPN at the moment.
Hi Worf, thanks for the response.

I think I have managed to get what you asked for below. I tried adding the above bits of code you suggested but I get a compile error of "Variable not defined" on the Set List = html.getelementbyclassname("form-control") but I think I have probably not done something else that is needed.

Thanks for your help.

HTML:
<html class="" lang="en"><!--<![endif]--><head>
    <meta charset="utf-8">
    <title>What is the vehicle's registration number - MOT History</title>
    <link rel="shortcut icon" href="/assets/images/favicon.ico?0.22.1" type="image/x-icon">
    <link rel="mask-icon" href="/assets/images/gds/gov.uk_logotype_crown.svg?0.22.1" color="#0b0c0c">
    <link rel="apple-touch-icon" sizes="180x180" href="/assets/images/apple-touch-icon-180x180.png?0.22.1">
    <link rel="apple-touch-icon" sizes="167x167" href="/assets/images/apple-touch-icon-167x167.png?0.22.1">
    <link rel="apple-touch-icon" sizes="152x152" href="/assets/images/apple-touch-icon-152x152.png?0.22.1">
    <link rel="apple-touch-icon" href="/assets/images/apple-touch-icon.png?0.22.1">

    <meta name="theme-color" content="#0b0c0c">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta property="og:image" content="/assets/images/gds/opengraph-image.png?0.22.1">

    <link rel="stylesheet" href="/assets/stylesheets/styles.css">

    <!--[if lt IE 9]>
    <script type="text/javascript" src="/javascripts/ie-shims.bundle.js"></script>
    <![endif]-->

    <script src="/js/cookie_manager_config.js"></script>

    
</head>
<body class="moth js-enabled">
    <div class="govuk-visually-hidden theme__cookie-success-message-grey" id="accept-all-cookies-success">
    <div class="govuk-width-container">
        <div class="govuk-!-padding-top-5 govuk-!-padding-bottom-7">
            <span class="govuk-body">You’ve accepted all cookies. You can <a href="/cookie-preferences">change your cookie settings</a> at any time.</span>

            <span id="cookie-accept-all-success-banner-hide" class="govuk-body u-float-right"><a href="#">Hide</a></span>
        </div>
    </div>
</div>

<script type="text/javascript">
    document.getElementById('cookie-accept-all-success-banner-hide').onclick = function(){
        document.getElementById('accept-all-cookies-success').classList.add('govuk-visually-hidden');
    };
</script>
        <div class="theme__cookie-success-message-grey" id="cm_cookie_notification">
        <div class="govuk-width-container">
            <div class="govuk-grid-row ">
                <div class=" govuk-grid-column-two-thirds govuk-!-margin-top-5">
                    <div>
                        <span class="govuk-heading-m">Tell us whether you accept cookies</span>
                        <p class="govuk-body">We use <a class="govuk-link" href="/cookies">cookies to collect information</a> about how you use the MOT history service. We use this information to make the service work as well as possible.</p>
                    </div>
                    <div class="js-show govuk-grid-column-full govuk-grid-column-one-half-from-desktop">
                        <button class="govuk-button" type="submit">Accept all cookies</button>
                    </div>
                    <div class="govuk-grid-column-full govuk-grid-column-one-half-from-desktop">
                        <a role="button" class="govuk-button" href="/cookie-preferences">Set cookie preferences</a>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script>document.body.className = ((document.body.className) ? document.body.className + ' js-enabled' : 'js-enabled');</script>
        <!-- skiplink -->
    <div id="skiplink-container">
      <div><a href="#content" class="skiplink">Skip to main content</a></div>
    </div>

    <!-- header -->
    <header id="global-header" class="with-proposition">
        <div class="header-wrapper">
            <div class="header-global">
                <div class="header-logo">
                    <a href="https://www.gov.uk" title="Go to the GOV.UK homepage" id="logo" class="content">
                        <img src="/assets/images/gov.uk_logotype_crown_invert_trans.png?0.22.1" width="36" height="32" alt="GOV UK Crown">
                        GOV.UK
                    </a>
                </div>
            </div>

            <div class="header-proposition print-hide">
                <div class="content">
                    <nav id="proposition-menu">
                        <a href="/" id="proposition-name">Check MOT history</a>
                    </nav>
                </div>
            </div>
        </div>
    </header>
    <!-- new header banner: start -->
        <!-- header bar -->
    <div id="global-header-bar"></div>

    <!-- main content -->
    <main id="content">

        <div class="phase-banner">
            <p>
                <span>This is a new service. <a href="http://www.smartsurvey.co.uk/s/GIZZX?" target="_blank" class="appendClientID"> Complete a quick survey</a> to help us improve.</span>            </p>
        </div>

        <p class="print-show"> Check MOT history <a href="https://www.gov.uk/check-mot-history"><span class="print-hide">GOV UK - Check MOT history</span></a> </p>

        
<div>
    <a class="link-back" href="https://www.gov.uk/check-mot-history">Back</a>
</div>

<div class="grid-row">
    <div class="column-two-thirds">

        
        <h1 class="heading-large">What is the vehicle's registration number?</h1>
        <form method="POST" name="moth-search" id="EVL">
        <fieldset>
            <legend class="form-title heading-medium visuallyhidden">Enter the vehicle registration</legend>
                <input type="hidden" name="_csrf_token" value="618427D1-ED89-9F08-899D-32D9EC94149C">
                                                                                            <div class="form-group input-blaster blocked">
                        
                        <label class="form-label form-label-bold" for="630630660ebd073f42b30e03447026172dcce5881610483210">
                            <span>Do not enter anything in this field</span>
                            <span class="form-hint">For example, CU57ABC</span>
                                                    </label>

                        <input type="text" name="630630660ebd073f42b30e03447026172dcce5881610483210" id="630630660ebd073f42b30e03447026172dcce5881610483210" class="form-control" aria-hidden="true" tabindex="-1" value="">
                        </div>
                                                                                            <div class="form-group honey-pot robo-cop">
                        
                        <label class="form-label form-label-bold" for="330330339f12fe70c919034c8f089a69fe63b90e1610483210">
                            <span>Do not enter anything in this field</span>
                            <span class="form-hint">For example, CU57ABC</span>
                                                    </label>

                        <input type="text" name="330330339f12fe70c919034c8f089a69fe63b90e1610483210" id="330330339f12fe70c919034c8f089a69fe63b90e1610483210" class="form-control" aria-hidden="true" tabindex="-1" value="">
                        </div>
                                                                                            <div class="form-group apiary-22453 robo-cop">
                        
                        <label class="form-label form-label-bold" for="vehicle-manufacturer">
                            <span>Leave this field empty</span>
                            <span class="form-hint">For example, CU57ABC</span>
                                                    </label>

                        <input type="text" name="vehicle-manufacturer" id="vehicle-manufacturer" class="form-control" aria-hidden="true" tabindex="-1" value="">
                        </div>
                                                                                            <div class="form-group keep-hidden bee-hive">
                        
                        <label class="form-label form-label-bold" for="42042042998e8f30381e94df9ccf272ac8f12ee01610483210">
                            <span><span class="sr-only">Enter your</span> Registration number (number plate) <span class="sr-only">into this field only</span></span>
                            <span class="form-hint">For example, CU57ABC</span>
                                                    </label>

                        <input type="text" name="42042042998e8f30381e94df9ccf272ac8f12ee01610483210" id="42042042998e8f30381e94df9ccf272ac8f12ee01610483210" class="form-control" value="">
                        </div>
                                                                                            <div class="form-group it-290 isOnshow">
                        
                        <label class="form-label form-label-bold" for="registration">
                            <span>This field should be left empty</span>
                            <span class="form-hint">For example, CU57ABC</span>
                                                    </label>

                        <input type="text" name="registration" id="registration" class="form-control" aria-hidden="true" tabindex="-1" value="">
                        </div>
                                                                                            <div class="form-group bee-hive isOnshow">
                        
                        <label class="form-label form-label-bold" for="reg-number">
                            <span>This field should not be filled</span>
                            <span class="form-hint">For example, CU57ABC</span>
                                                    </label>

                        <input type="text" name="reg-number" id="reg-number" class="form-control" aria-hidden="true" tabindex="-1" value="">
                        </div>
                                                                                            <div class="form-group bee-hive inlined">
                        
                        <label class="form-label form-label-bold" for="registration-number">
                            <span>Leave this field empty</span>
                            <span class="form-hint">For example, CU57ABC</span>
                                                    </label>

                        <input type="text" name="registration-number" id="registration-number" class="form-control" aria-hidden="true" tabindex="-1" value="">
                        </div>
                            </fieldset>

        <div class="form-group">
            <input type="submit" name="submit" class="button" value="Continue">
        </div>

        </form>
        <details role="group">
            <summary role="button" aria-controls="details-content-0" aria-expanded="false">
                <span class="summary" id="looking-for-trailer-section">Looking for HGV trailer history?</span>
            </summary>
            <div class="panel panel-indent" id="details-content-0" aria-hidden="true">
                <p>Enter the trailer identification number to search for HGV trailer history.</p>
                <p>For example, C236718 or A787121.</p>
            </div>
        </details>
    </div>
</div>

<div class="input-block "></div>

    </main>
    <!-- / #content -->

    <!-- footer -->
    <footer class="group js-footer" id="footer">
        <div class="footer-wrapper">
            <div class="footer-meta">
                <div class="footer-meta-inner">
                    <h2 class="visuallyhidden">Support links</h2>
                    <ul>
                        <li>
                            <a href="/cookies">Cookies</a>
                        </li>
                        <li>
                            <a href="/terms-and-conditions">Terms and conditions</a>
                        </li>
                        <li>
                            <a href="http://www.gov.uk/dvsa/privacy" target="_blank">Privacy notice</a>
                        </li>
                        <li>
                            <a href="/accessibility-statement">Accessibility statement</a>
                        </li>
                        <li>
                            <a href="https://dvsa.github.io/mot-history-api-documentation/" target="_blank">MOT history API</a>
                        </li>
                        <li>Built by the <a href="https://www.gov.uk/government/organisations/driver-and-vehicle-standards-agency">Driver &amp; Vehicle Standards Agency</a></li>
                    </ul>
                    <div class="open-government-licence">
                        <p class="logo"><a href="https://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/" rel="license">Open Government Licence</a></p>
                        <p>All content is available under the <a href="https://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/" rel="license">Open Government Licence v3.0</a>, except where otherwise stated</p>
                    </div>
                </div>
                <div class="copyright">
                    <a href="https://www.nationalarchives.gov.uk/information-management/re-using-public-sector-information/copyright-and-re-use/crown-copyright/">© Crown copyright</a>
                </div>
            </div>
        </div>
    </footer>

    <div id="global-app-error" class="app-error hidden"></div>

    <script type="text/javascript" src="/assets/javascripts/vendor.bundle.js"></script>
    <script type="text/javascript" src="/assets/javascripts/dvsa.bundle.js"></script>

    
  <script async="" type="text/javascript" src="/_Incapsula_Resource?SWJIYLWA=719d34d31c8e3a6e6fffd425f7e032f3&amp;ns=1&amp;cb=290062715"></script>


</body></html>
 
Upvote 0
See if this code helps:

input boxes.PNG


VBA Code:
Private Sub CommandButton3_Click()
Dim bc As Object, ie As Object, doc As HTMLDocument, i%
Set ie = New InternetExplorerMedium
ie.Visible = True
ie.navigate "c:\test\govuk.htm"       ' local page
Do While ie.Busy
    Application.Wait DateAdd("s", 1, Now)
Loop
Set bc = ie.Document.getElementsByClassName("form-control")
For i = 0 To bc.Length - 1
    If UCase(bc(i).tagName) = "INPUT" Then bc(i).Value = "Box" + CStr(i)
Next
End Sub
 
Upvote 0
See if this code helps:

View attachment 29710

VBA Code:
Private Sub CommandButton3_Click()
Dim bc As Object, ie As Object, doc As HTMLDocument, i%
Set ie = New InternetExplorerMedium
ie.Visible = True
ie.navigate "c:\test\govuk.htm"       ' local page
Do While ie.Busy
    Application.Wait DateAdd("s", 1, Now)
Loop
Set bc = ie.Document.getElementsByClassName("form-control")
For i = 0 To bc.Length - 1
    If UCase(bc(i).tagName) = "INPUT" Then bc(i).Value = "Box" + CStr(i)
Next
End Sub
Hi, I am a bit confused and don't understand the last. It may very well be me but I am struggling to grasp what you have sent. I may have resolved this in a slightly different way if I can sort out getting the information via a API but I just can't work out how to extract it. I posted here earlier to see if anyone could assist.
 
Upvote 0
K1600

Do you have an API key for the DVLA?
 
Upvote 0
If you have the API key then don't automate IE, use XMLHttp.
 
Upvote 0
If you have the API key then don't automate IE, use XMLHttp.
Pardon my ignorance but how do I do that?

I'm relatively new to VBA / coding and have never had any dealings with API before.

Thanks for your time.
 
Upvote 0
It will look like the code below, but that is not production code for you as it is another API.

I tried the VPN and still could not access the site.

VBA Code:
Sub authenticating()
Dim req As MSXML2.ServerXMLHTTP60, key_id, _
secretk$, secreth$, URL$, json As Object, item, s$
' create object
Set req = New MSXML2.ServerXMLHTTP60
key_id = CStr([b18]) ' id from cell B18
secretk = CStr([b19]) ' secret key from cell B19
' get Microsoft information
URL = "https://paper-api.alpaca.markets/v1/assets/MSFT"
req.Open "GET", URL, False
req.setRequestHeader "APCA-API-KEY-ID", key_id
req.setRequestHeader "APCA-API-SECRET-KEY", secretk
req.send
' start building string
s = "Raw data:" & vbLf & req.responseText
s = s & vbLf & "**************" & vbLf & _
"Available data:" & vbLf
' use the library
Set json = ParseJson(req.responseText)
For Each item In json ' loop the collection
s = s & item & vbLf
Next
MsgBox s, 64
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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
Back
Top