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:
This is the HTML
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">