Looping through element collection and input data on webpage

Bizy

New Member
Joined
Nov 17, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hey,


I’m new to VBA and I’m trying to figure out how to automate manual price updates for work. I’ve manage to automate IE to input prices for menu items in an input tag. I can’t share the url as it’s for work but I’ll show the html below.

I’m using the getelementsbytagname and then specifying the name of the input tag. This works perfectly fine but as I have hundreds of prices to enter I’m wondering if there is a way to loop over the input elements. I have a list of all the input tag names (store id’s) in a sheet that are separated out in columns by price tiers.

Ideally, what I want to avoid is writing this out for every store as there are hundreds of stores and I would end up with a very long code. I was thinking something like this would work:


If HTMLInput.getAttribute("name") = "store_prices[369]" Then


HTMLName.Value = 1


Exit For


End If


But instead of “store_price[369]” is it’s possible to write a code that loops through all the store id’s in a spreadsheet column?


I’d really appreciate any help, advice or suggestions. If I haven’t been clear on something, let me know and I’ll try to explain more.




This is the beginning of my code:

VBA Code:
Sub TestPriceUpdate()
 
    Dim ie As New SHDocVw.InternetExplorer
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim HTMLInput As MSHTML.HTMLInputElement
    Dim HTMLInputs As MSHTML.IHTMLElementCollection

    ie.Visible = True
    ie.navigate URL
 
    Do While ie.readyState <> READYSTATE_COMPLETE
    Loop
   
    'Click login (password & username saved)
   
    ie.document.getElementById("submitBtn").Click
   
    Set HTMLDoc = ie.document
      
    Do Until HTMLDoc.readyState = "complete"
    Loop
   
    Set HTMLInputs = HTMLDoc.getElementsByTagName("Input")
   
    For Each HTMLInput In HTMLInputs
   
        Debug.Print HTMLInput.Name, HTMLInput.Value
      
    Next HTMLInput
       
   Set HTMLInputs = HTMLDoc.getElementsByName("store_prices[369]")(0)
   HTMLInputs.Value = 1

   Set HTMLInputs = HTMLDoc.getElementsByName("store_prices[1947]")(0)
   HTMLInputs.Value = 1

   Set HTMLInputs = HTMLDoc.getElementsByName("store_prices[505]")(0)
   HTMLInputs.Value = 1

This is the HTML

HTML:
<label>Unavailable <input type="checkbox" name="store_unavailable[1961]" value="1" class="store_unavailable"></label>
<input type="hidden" value="0" name="store_unavailable[1961]">
<input type="text" name="store_prices[1961]" value="0.00">
<label class="master" for="store_price">AAA</label>
<div class="row checkboxes" style="display: block; overflow: hidden;">
                <label class="master" for="store_price">AAA</label>
        <input type="text" name="store_prices[1961]" value="0.00">
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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