Looping through element collection and input data on webpage

[Bizy]

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:

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

<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">