I've got a weird project I'm trying to pull off. Essentially, I have a tool that will create an extensive spreadsheet of the entire inventory of a specific section of a warehouse. It lists off their location, inventory state, and their item ID ("ASIN" which is basically it's barcode and virtual reference in the internal system. Problem is, it does not list off the "velocity" (a metric of how many we sell in a week) of those specific items, and I would like to get this metric printed out adjacent to every item ID so I can sort out what isn't selling and send it to a long-term storage section of the warehouse. I've found another tool which grabs a table of information about an individual item ID from our internal wiki("FCresearch") which happens to contain this specific metric. I'm looking to grab only the velocity of an item out of this table (essentially the number that is in this location:
/html/body/div[2]/div/div[1]/div/div[1]/div/div[2]/div/div/div[2]/table/tbody/tr[19]/td
on the web page) and then adapt this macro so it acts on an ASIN in the table created by the former tool, prints the velocity of it to the adjacent cell, then moves a row down and repeats for all ~4000 entries until it hits empty space.
Here is the full relevant function:
Sub getFCresearch()
Dim A As Object, H As Object, D As Object, C As Object, asin$, B$, F$
Dim x&, t&
Set C = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
Set D = CreateObject("HTMLFile")
Set A = CreateObject("New:{00000566-0000-0010-8000-00AA006D2EA4}")
Set H = CreateObject("WinHTTP.WinHTTPRequest.5.1")
H.SetAutoLogonPolicy 0
''passes badge
H.Open "GET", "https://hrwfs.amazon.com/?Operation=empInfoByUid&ContentType=JSON&employeeUid=" & Environ("USERNAME")
H.send
DoEvents
B = Split(Split(H.ResponseText, "employeeBarcode"":""")(1), Chr(34))(0)
H.Open "POST", "http://fcmenu-iad-regionalized.corp.amazon.com/do/login"
H.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
H.setRequestHeader "Content-Length", Len("badgeBarcodeId=" & B)
H.send "badgeBarcodeId=" & B
DoEvents
H.Open "GET", "http://fcmenu-iad-regionalized.corp.amazon.com/" & F
H.send
DoEvents
''Needs to derive "asin" variable from adjacent cell
asin = Sheets("Sheet1").[A1]
''This gathers the specific item's page on the wiki "FCresearch"
H.Open "GET", "http://fcresearch-na.aka.amazon.com/DEN3/results/inventory?s=" & asin, False
H.send
'''This gets the whole table,where I only need one specific element called "velocity" at: /html/body/div[2]/div/div[1]/div/div[1]/div/div[2]/div/div/div[2]/table/tbody/tr[19]/td
D.body.InnerHTML = H.ResponseText
C.SetText D.GetElementById("table-inventory").OuterHTML
C.PutInClipboard
''This pastes the table to a different sheet, but needs to paste to a cell adjacent to the "asin" variable of each row
''Before moving down to the next row and repeating the process
Sheet2.[C:Z].Cells.ClearContents
Sheet2.[C1].PasteSpecial
Sheet2.[C:N].WrapText = False
Sheet2.Columns("C:N").AutoFit
End Sub
Any help you all can provide would be amazing. Apologies that it's such an extensive thing, I'm fairly new to this and I've only been able to adjust minor things about the code, and I haven't been able to find documentation anywhere that helps any deeper than the .GetElementById function that doesn't work on a html element without an ID.
Image of table HTML, + plaintext
<table data-row-id="1579657885" class="a-keyvalue"><tbody><tr><th>ASIN</th><td><a href="/DEN3/results?s=1579657885">1579657885</a></td></tr><tr><th>Title</th><td><a target="_blank" href="http://www.amazon.com/gp/product/1579657885">1,000 Places to See Before You Die (Deluxe Edition): The World as You've Never Seen It Before</a></td></tr><tr><th>Binding</th><td>Hardcover</td></tr><tr><th>Publisher</th><td></td></tr><tr><th>Vendor Code</th><td>ATSAN</td></tr><tr><th>Weight</th><td>6.45 pounds</td></tr><tr><th>Dimensions</th><td>1.50 x 13.00 x 9.80 IN</td></tr><tr><th>List Price</th><td>USD 50.00</td></tr><tr><th>Expiration Date</th><td class=""></td></tr><tr><th>Asin Demand</th><td><a target="_blank" href="https://ufo.amazon.com/srw14na/asins/place_in_line/1579657885?warehouse=DEN3">Demand for 1579657885</a></td></tr><tr><th>Sortable</th><td>true</td></tr><tr><th>Conveyable</th><td>true</td></tr><tr><th>Very High Value</th><td>false</td></tr><tr><th>Master Case</th><td>false</td></tr><tr><th>FCSku Scope</th><td>FNSKU</td></tr><tr><th>Sales Forecast</th><td>4.0</td></tr><tr><th>Sales History (approx)</th><td>5.0</td></tr><tr><th>Sales Override</th><td>0.0</td></tr><tr><th>ASIN Velocity (approx)</th><td>5.0</td></tr><tr><th>Provenance Value</th><td>UNTRACKED</td></tr><tr><th>Provenance IOG</th><td>Info Not Found</td></tr></tbody></table>Ok so, here are two ways to get the info you want. If you understand the logic, I believe any combination of these methods should be enough to adjust the code to your needs.
For the sake of simplicity I will assume that the HTML has already been loaded into an HTMLDocument object named D. The value of interest will be printed in your immediate window for demonstration purposes.
Firstly you need a reference to Microsoft HTML Object Library (VBE>Tools>References>...).
I'll be using the following variables:
Dim table As HTMLTable
Dim tableOfInterest As HTMLTable
Dim row As HTMLTableRow
Dim rowOfInterest As HTMLTableRow
Dim cell As HTMLTableCell 'not using it but you could in a For-Each
Dim cellOfInterest As HTMLTableCell
Assuming the table's index, the row's index and the cell's index are always the same and you know them:
Set tableOfInterest = D.getElementsByTagName("table")(0) 'Assuming the table of interest is the first table to appear in the HTML document. Keep in mind indexing starts at zero!
Set rowOfInterest = tableOfInterest.getElementsByTagName("tr")(18) 'Assuming the row of interest is the 19th row in the table.
Set cellOfInterest = rowOfInterest.getElementsByTagName("td")(0) 'Assuming the cell of interest is the 1st cell in the row.
Debug.Print cellOfInterest.innerText
Assuming you don't explicitly know the index of the table and the row but you know other info like attributes or inner text
For Each table In D.getElementsByTagName("table")
If table.Attributes("data-row-id").Value = "1579657885" Then 'assuming the value of this attribute is always the same
Set tableOfInterest = table
End If
Next table
For Each row In tableOfInterest.getElementsByTagName("tr")
If row.innerText Like "*ASIN Velocity (approx)*" Then 'assuming that's the text you're looking for
Set rowOfInterest = row
End If
Next row
Debug.Print rowOfInterest.Cells(1).innerText 'in this case the "th" element is also considered a cell so the cell you're interested in is the 2nd one.
Another method to use when the ID is absent would be the .getElementsByClassName(). It works with the same logic .getElementsByTagName() does.