获取的XmlHtt prequest readyState的从Javascript的VBA [英] Getting the ReadyState of an XmlHttpRequest from Javascript in VBA
问题描述
我有我们保持的东西，我们发现围绕我的工作的Excel文件。
我们必须登录到一个网站，以检查某些事情。通过VBA，我已经得到了一个IE窗口中打开，我把它登录到网站，并填写表格（并提交！）。
不过，我的问题就出现了，因为形式是使用JavaScript和Ajax提交。所以，我也因此无法得到那个出来的响应数据（这是我开始这整个努力的）。
因此，我的问题是：的存在的任意的办法，我可以循环在VBA直到Javascript的XML对象获取数据，还是我完全疯了的尝试
编辑：由于要求，我把code我现在。我不能发布双方的说[删除]由于我们的隐私协议的项目。我知道一个事实，一旦响应数据加载会被添加到一个div有resultsTable的ID但是，我尝试循环，直到DOM元素被发现没有工作。
编辑＃2 我终于得到了一些工作。发生了什么事错在我原来的code是我不知道怎么ohtml.getElementById（）函数的工作以及它返回，如果什么也没找到。直到出现下等待数据集。然后，它如何处理数据（这我不能发表，不幸的是）某些行动。谢谢大家！
code
私人小组IE_Autiomation（）
昏暗我只要，R为范围
昏暗即作为InternetExplorer的，ohtml作为HTMLDocument的
昏暗ouser作为HTMLInputElement，opass作为HTMLInputElement，otext作为HTMLTextAreaElement，otable作为HTMLTABLE
创建InternetExplorer对象
集R = Application.Union（ActiveSheet.ListObjects（1）.Range，ActiveSheet.ListObjects（2）.Range，ActiveSheet.ListObjects（3）.Range，ActiveSheet.ListObjects（4）.Range）
Debug.Print r.Address
集合R = Application.Intersect（R，ActiveSheet.Range（B：B，E：E，H：H））'你相信我，好吗？
设置IE =新的InternetExplorer
发送表单数据的URL作为POST二进制请求
IE.Navigate[删除]
做，而IE.Busy
Application.Wait使用DateAdd（S，1，现在）
循环
设置ohtml = IE.document
设置ouser = ohtml.getElementById（用户）
设置opass = ohtml.getElementById（密码）
ouser.Value =[删除]
PW =输入框（密码）
opass.Value = PW
IE.Visible = TRUE
ouser.form.submit
做，而IE.Busy
Application.Wait使用DateAdd（S，1，现在）
循环
设置otext = ohtml.getElementsByName（VIN）。项目（0）
对于每封在研发
如果e.Offset（0，-1）.value的=，而不是e.Value =然后，
otext.Value = otext.Value和放大器; e.Value和放大器; ，
结束如果
下一个
Debug.Print otext.Value
otext.Value =左（otext.Value，莱恩（otext.Value） - 2）
Debug.Print otext.Value
Debug.Print到目前为止，wafles。
对每个j在ohtml.getElementsByTagName（输入）
如果j.Value =提交然后
设置ouser = j的
退出对于
结束如果
下一个
ouser.Click
这里是哪里，我需要等待响应加载到HTML
等等，而IE浏览器加载...
' 清理
设置otable = ohtml.getElementById（resultsTable）
做，而otable是Nothing
Application.Wait使用DateAdd（S，1，现在）
设置otable = ohtml.getElementById（resultsTable）
循环
IE.Quit
设置IE =什么
设置objElement =什么
设置ohtml =什么
设置objCollection =什么
Application.StatusBar =
结束小组
使用现成的等待循环。
您将需要WinAPI的睡眠功能：
公开声明子睡眠库KERNEL32（BYVAL dwMilliseconds长）对于定时器功能的使用
那么，你的请求被发送后，你需要做的是这样（例如一个IE浏览器对象，如果你与你的code，我可以修改你的需要更新您的Q）。
设置IE =的CreateObject（InternetExplorer.Application）
IE.Navigatehttp://google.com
做，而不是IE.readyState = 4
睡眠250
循环
这样做是睡了第二次的1/4，循环，直到该文件已加载。
I have an Excel file that we keep for things we find around my job.
We have to log into a website in order to check certain things. With VBA, I've gotten an IE window to open, I get it to log in to the website and fill out a form (and submit it!).
However, my problem arises because the form is submitted using Javascript and AJAX. So, I can therefore not get the response data that comes out (which is what I started this whole endeavor for).
My question is thus: is there any way I can loop in VBA until the Javascript XML object gets the data, or am I totally insane for trying?
Edit: due to a request, I am putting the code I have currently. I cannot post both the items that say [removed] due to our privacy agreement. I know for a fact that once the response data is loaded it is appended to a div that has an ID of "resultsTable" However, my attempts to loop until that DOM element was found did not work.
EDIT #2 I finally got something working. What was going wrong in my original code was I didn't know how the ohtml.getElementById() function worked and what it returned if it found nothing. The following waits until the data set appears. It then does certain actions with the data (which I can't post, unfortunately). Thanks everyone!
Code
Private Sub IE_Autiomation()
Dim i As Long, r As Range
Dim IE As InternetExplorer, ohtml As HTMLDocument
Dim ouser As HTMLInputElement, opass As HTMLInputElement, otext As HTMLTextAreaElement, otable As HTMLTable
' Create InternetExplorer Object
Set r = Application.Union(ActiveSheet.ListObjects(1).Range, ActiveSheet.ListObjects(2).Range, ActiveSheet.ListObjects(3).Range, ActiveSheet.ListObjects(4).Range)
Debug.Print r.Address
Set r = Application.Intersect(r, ActiveSheet.Range("B:B,E:E,H:H")) 'Just Trust Me, Ok?
Set IE = New InternetExplorer
' Send the form data To URL As POST binary request
IE.Navigate "[Removed]"
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
Set ohtml = IE.document
Set ouser = ohtml.getElementById("USER")
Set opass = ohtml.getElementById("PASSWORD")
ouser.Value = "[removed]"
pw = InputBox("Password?")
opass.Value = pw
IE.Visible = True
ouser.form.submit
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
Set otext = ohtml.getElementsByName("vin").Item(0)
For Each e In r
If e.Offset(0, -1).Value = "" And Not e.Value = "" Then
otext.Value = otext.Value & e.Value & ", "
End If
Next
Debug.Print otext.Value
otext.Value = Left(otext.Value, Len(otext.Value) - 2)
Debug.Print otext.Value
Debug.Print "So far, wafles."
For Each j In ohtml.getElementsByTagName("input")
If j.Value = "Submit" Then
Set ouser = j
Exit For
End If
Next
ouser.Click
'Here is where I need to wait for the response to load into the HTML
' Wait while IE loading...
' Clean up
Set otable = ohtml.getElementById("resultsTable")
Do While otable Is Nothing
Application.Wait DateAdd("s", 1, Now)
Set otable = ohtml.getElementById("resultsTable")
Loop
'IE.Quit
Set IE = Nothing
Set objElement = Nothing
Set ohtml = Nothing
Set objCollection = Nothing
Application.StatusBar = ""
End Sub
Use a ready waiting loop.
You will need the WinAPI sleep function:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For use in timer function
Then, after your request is sent you need to do something like (example for an IE object, if you update your Q with your code I can modify for your needs).
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://google.com"
Do While Not IE.readyState = 4
Sleep 250
Loop
What this does is sleep for 1/4 of a second, looping until the document has loaded.
这篇关于获取的XmlHtt prequest readyState的从Javascript的VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
