调整从网页中提取表的函数，使其只提取单个元素+重复
调整从网页中提取表的函数，使其只提取单个元素+重复
提问于 2020-03-06 08:12:12
我有个奇怪的项目要完成。本质上，我有一个工具，它将创建仓库特定部分的整个库存的广泛电子表格。它列出了它们的位置、库存状态和它们的项目ID ("ASIN“，这基本上是内部系统中的条形码和虚拟引用)。问题是，它没有列出这些特定商品的“速度”(我们一周卖出多少)，我希望在每个项目ID旁边打印出这个度量，这样我就可以整理出哪些不是出售的，并将其发送到仓库的长期存储区。我发现了另一个工具，它从我们的内部wiki("FCresearch")中获取关于单个项ID的信息表，该表恰好包含了这个特定的度量。我只想从这个表中获取一个项目的速度(本质上是这个位置上的数字)：
/html/body/div[2]/div/div[1]/div/div[1]/div/div[2]/div/div/div[2]/table/tbody/tr[19]/td (在网页上)，然后调整这个宏，使其在由前一个工具创建的表中的ASIN上工作，将它的速度打印到相邻的单元格，然后向下移动一行，并对所有的~4000个条目重复，直到它到达空空间。
以下是全部相关职能：
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你们所能提供的任何帮助都将是惊人的。很抱歉，这是一件非常广泛的事情，我对此相当陌生，我只能调整代码中的一些次要内容，而且我在任何地方都找不到文档，这些文档比没有ID的html元素不能工作的.GetElementById函数更有帮助。
<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>
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-03-06 11:28:09
好的，这里有两种方法来获取你想要的信息。如果您理解逻辑，我相信这些方法的任何组合都应该足够调整代码以满足您的需要。
为了简单起见，我将假设HTML已经加载到名为D的D对象中。感兴趣的价值将打印在您的即时窗口，以供演示之用。
首先，您需要引用Microsoft HTML Object Library (VBE>Tools>References>.)。
我将使用以下变量：
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 假设表的索引、行的索引和单元格的索引总是相同的，并且您知道它们：
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假设您不显式地知道表和行的索引，但是您知道其他信息，比如属性或内部文本
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.在没有ID时使用的另一种方法是.getElementsByClassName()。它使用与.getElementsByTagName()相同的逻辑。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/60559952
复制相关文章
相似问题
腾讯云开发者
