vba 用VBA填写网站上的输入框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39799363/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fill in Input Box on Website with VBA
提问by Bill T
I've been banging my head against he wall trying to figure out why this VBA code will not work :(
我一直在用头撞墙，试图弄清楚为什么这个 VBA 代码不起作用:(
I am simply trying to insert the value entered into the excel input box into a website's input box. I am a novice when it comes to HTML so I'm sure that has something to do with it.
我只是想将输入到 excel 输入框中的值插入到网站的输入框中。我是 HTML 的新手，所以我确定这与它有关。
Here is the HTML element from the website Zomato.com:
这是来自 Zomato.com 网站的 HTML 元素：
<input class="dark" id="location_input" role="combobox" aria-expanded="true" aria-labelledby="label_search_location" aria-owns="explore-location-suggest" aria-autocomplete="list" placeholder="Please type a location...">
Here is my VBA code:
这是我的 VBA 代码：
Sub Merchant_Extraction()
Dim IE As Object
Dim form As Variant
Dim button As Variant
Set IE = CreateObject("internetexplorer.application")
merchantzip = InputBox("Enter Zip Code")
With IE
.Visible = True
.navigate ("http://www.zomato.com")
While IE.readystate <> 4
DoEvents
Wend
IE.Document.GetElementByID(“location_input_sp”).Item.innertext = merchantzip
Set form = IE.Document.getelementsbytagname("form")
Set button = form(0).onsubmit
form(0).get
End With
Set IE = Nothing
End Sub
I am unclear why it's not working - any help would be incredible!
我不清楚为什么它不起作用 - 任何帮助都会令人难以置信！
回答by QHarr
API XMLHTTP GET request
API XMLHTTP GET 请求
The API was mentioned. The documention is here.
提到了 API。文档在这里。
The basic free account allows access to restaurant information and search APIs (up to 1000 calls/day).
基本免费帐户允许访问餐厅信息和搜索 API（每天最多 1000 次调用）。
An example first 0-20 results call, with a city id specified (68 for Manchester,UK), is as follows; a JSON response is received. The response it parsed into a JSON object with JSONConverter.bas
示例前 0-20 个结果调用，指定城市 ID（英国曼彻斯特为 68），如下所示；收到 JSON 响应。它使用JSONConverter.bas解析为 JSON 对象的响应
Option Explicit
Public Sub GetInfo()
Dim URL As String, strJSON As String, json As Object
URL = "https://developers.zomato.com/api/v2.1/search?entity_id=68&entity_type=city&start=0&count=20"
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", URL, False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "user-key", "yourAPIkey"
.send
strJSON = .responseText
End With
Set json = JsonConverter.ParseJson(strJSON)
'other stuff with JSON object
End Sub
Example JSON response:
JSON 响应示例：
Zomato - Common APIs:
Zomato - 常用 API：
Finding your city ID:
查找您的城市 ID：
The quickest way for me was to go to concatenate the city onto a base URL string e.g. https://www.zomato.com/manchester, then click search and right-click inspect HTML on first result. Then Ctrl+Fto bring up search box, search for CITY_ID, and scan through find results for the HTML until city id found e.g.
对我来说，最快的方法是将城市连接到一个基本 URL 字符串上，例如https://www.zomato.com/manchester，然后单击搜索并右键单击检查第一个结果的 HTML。然后Ctrl+ 调F出搜索框，搜索CITY_ID，并扫描 HTML 的查找结果，直到找到 city id 例如
回答by B Hart
As far as entering values into webpages the correct syntax would be:
至于在网页中输入值，正确的语法是：
IE.Document.all.Item("location_input").Value = ""
I've combined your routine with some code that I use so you can see an example. I have not been able to test however. In my environment, the IE object disconnects after the .navigate portion so I added in a loop to find and re-assign the object...
我已将您的例程与我使用的一些代码结合起来，以便您可以查看示例。但是，我无法进行测试。在我的环境中，IE 对象在 .navigate 部分之后断开连接，所以我添加了一个循环来查找和重新分配对象......
Option Explicit
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub Merchant_Extraction()
Dim IE As Object, objShellWindows As Object
Dim MerchantZip As String, strWebPath As String
Dim Form As Variant, Button As Variant
Dim X As Long
strWebPath = "http://www.zomato.com"
MerchantZip = InputBox("Enter Zip Code")
If MerchantZip = vbNullString Then Exit Sub
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = True
.Navigate strWebPath
End With
Do
Sleep 250
DoEvents
Loop While IE.Busy Or IE.ReadyState <> 4
If TypeName(IE) <> "IWebBrowser2" Or IE.Name <> "Internet Explorer" Then
Set objShellWindows = CreateObject("Shell.Application").Windows
For X = 0 To objShellWindows.Count - 1
Set IE = objShellWindows.Item(X)
If Not IE Is Nothing Then
If IE.Name = "Internet Explorer" Then
If InStr(1, IE.LocationURL, strWebPath, 1) > 0 Then
Do While IE.Busy Or IE.ReadyState <> 4
Sleep 250
DoEvents
Loop
Exit For
End If
End If
End If
Set IE = Nothing
Next
Set objShellWindows = Nothing
End If
If Not IE Is Nothing Then
IE.Document.all.Item("location_input").Value = MerchantZip
Sleep 250
For Each Button In IE.Document.getelementsbytagname("form")
If StrComp(Button.Type, "Button", 1) = 0 Then
Button.Click
End If
Next
Set IE = Nothing
End If
End Sub
