将数据从XL Sheet传递到网站;当ie2打开时失去控制 [英] Pass Data from XL Sheet to Web Site; Lose Control when ie2 Opens
问题描述
我将值从Excel工作表传递到网站（防火墙后面） ）<跨度>群组。
一切都很顺利（粗体）：
ie.navigate a.href：退出
此时，新的网络浏览器打开，我失去焦点，控制或其他任何东西。
我想要做的是从Excel工作表中复制并粘贴到打开的新浏览器的TextArea中; TextArea被称为&llquo; invalidProfitCenter’。
如果我复制/粘贴手动，然后单击按钮，一切正常。
我想要做的是用代码控制这个过程。 类似这样的事情：
如果oHTML_Element.Value赞"&*"* Validate - > *"然后oHTML_Element.Click：
在我甚至之前但到了那里，似乎我失去了对第二个浏览器窗口的控制。
如果我运行到代码的末尾，数组从excel表复制并粘贴到TextArea，就像我想要的那样，但是我在浏览器窗口的左下角
中有一个小错误，当我手动点击‘验证 - >’按钮，没有任何反应。
我想有些东西会变得"失去"’当第二个，即brower打开时。
我不确定如何更好地描述它？
有没有人经历过这个？
如果你知道该怎么做，请告诉你。 我非常感谢你的一些帮助。
Sub Derivatives（）
Dim oHTML_Element As IHTMLElement
Dim sURL As String
'Dim NEW_URL As String
< p style ="margin：0in 0in 0pt"> 'Dim NEW_URL2 As String
Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Dim ie As Variant
Dim ie2 As Variant
Dim itm As Variant
Dim td As HTMLInputElement
Dim a As Variant
Dim arr As Variant
Dim textarea As Variant
Const DELIMITER = vbCr
;
Const NEW_URL =" https://moar.it.com/moap/reportType= advanced"
Set ie = CreateObject（" InternetExplorer.Application"）
ie.Visible = True
ie.navigate ie.navigate" https：//moar.it.com/moap/reportType=standard"
而ie.Busy：DoEvents：Wend
而ie.Busy：DoEvents：Wend
设置oHTML_Element = ie.document.getElementsByName（" selectedReportClass"）（0）
对于每个oHTML_Element在ie.document.getElementsByTagName（" input"）
如果oHTML_Element.Type =" submit"那么oHTML_Element.Click ：退出
下一页
而ie.Busy：DoEvents：Wend
设置itm = ie.document.getElementsByName（" report.field [0] .value"）（0）
如果不是没有那么itm.Value =" 31-8-2010"
< span style ="font-family：Calibri; font-size：small">
设置itm = ie.document.getElementsByName（" report.field [4] .Value"）（0）
如果Not itm Nothing那么itm.Value =" DBLN"
设置itm = ie.document.getElementsByName（" report.field [5] .value"）（0）
如果Not itm Nothing那么itm.Value =" DERIVATIVES"
设置itm = ie.document.getElementsByName（" report.field [1] .valueSelect"）（0）
如果Not itm Nothing那么itm.Value =" All"
'Working ...
For each td in ie.document.getElementsByTagName（" td"）
如果td.className =" moar-td-form- obj"然后
如果td.innerText喜欢"*（高级利润中心选择）*"那么
设置= td.getElementsByTagName（" a"）（0）
;
如果不是，那么
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
ie.navigate a.href：退出
< span style ="font-size：small">
结束如果
结束如果
结束如果
下一步
;
<跨度>&NBSP;
设置ie2 = CreateObject（" InternetExplorer.Application"）
ie2.Visible = True
ie2.navigate NEW_URL
而ie2.readyState<> 4：DoEvents：Wend
<跨度>&NBSP;&NBSP;
设置textarea = ie2.document.getElementById（" pasteProfitCenter"）
如果Not textarea什么都没有那么
使用ActiveSheet'表格（"PC列表"）
arr = WorksheetFunction.Transpose（.Range（。[A3]，。Cell（Rows.Count，" A"）。End（xlUp）））
&NBSP;&NBSP;&NBSP;
结束
<跨度>&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
如果不是IsArray（arr）那么arr =数组（arr）
< span style ="font-size：small">
textarea.Value =加入（arr，DELIMITER）
结束如果
。 。 。
End sub
我认为你有时间问题。 在第二个网页完全加载之前，您正在继续使用该代码。 readystead属性可能有效且网页仍然繁忙。 您可能正在尝试将数据粘贴到
网页中，并且对象未完全加载。
我证明这一点的简单方法是在代码中添加一个断点在您写入网页之前。 在继续之前等待30秒。
来自
而ie2.readyState<> 4：DoEvents：Wend
to
做IE2.busy = True或_
IE2.readystate<> 4
DoEvents
循环
I’m passing values from an Excel sheet into a web site (behind a firewall). Everything works great up to this line (in bold):
ie.navigate a.href: Exit For
Right at that point, a new web browser opens and I lose focus, or control, or whatever. What I want to do is copy from the Excel sheet and paste into the TextArea of the new browser that opens; the TextArea is called ‘invalidProfitCenter’.
If I copy/paste manually, and click the button, everything works fine. What I want to do is control this process with code. Something like this:
If oHTML_Element.Value Like "*Validate-->*" Then oHTML_Element.Click:
Before I even get there though, it seems like I lose control of that second browser window.
If I run tight through to the end of the code, the array is copied from the excel sheet and pasted into the TextArea, just as I want, but I have a small error in the lower left of that browser window and when I manually click the ‘Validate-->’ button, nothing happens. I guess something gets ‘lost’ when the second ie brower opens. I’m not sure how to describe it any better than that?
Has anyone experienced this before? If you know what to do, please do tell. I’d greatly appreciate some help with this.
Sub Derivatives()
Dim oHTML_Element As IHTMLElement
Dim sURL As String
'Dim NEW_URL As String
'Dim NEW_URL2 As String
Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Dim ie As Variant
Dim ie2 As Variant
Dim itm As Variant
Dim td As HTMLInputElement
Dim a As Variant
Dim arr As Variant
Dim textarea As Variant
Const DELIMITER = vbCr
Const NEW_URL = "https://moar.it.com/moap/reportType=advanced"
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate ie.navigate "https://moar.it.com/moap/reportType=standard"
While ie.Busy: DoEvents: Wend
While ie.Busy: DoEvents: Wend
Set oHTML_Element = ie.document.getElementsByName("selectedReportClass")(0)
If Not oHTML_Element Is Nothing Then oHTML_Element.Value = "com.db.moar.report.FOBOJournalReport"
For Each oHTML_Element In ie.document.getElementsByTagName("input")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next
While ie.Busy: DoEvents: Wend
Set itm = ie.document.getElementsByName("report.field[0].value")(0)
If Not itm Is Nothing Then itm.Value = "31-8-2010"
Set itm = ie.document.getElementsByName("report.field[4].Value")(0)
If Not itm Is Nothing Then itm.Value = "DBLN"
Set itm = ie.document.getElementsByName("report.field[5].value")(0)
If Not itm Is Nothing Then itm.Value = "DERIVATIVES"
Set itm = ie.document.getElementsByName("report.field[1].valueSelect")(0)
If Not itm Is Nothing Then itm.Value = "All"
'Working . . .
For Each td In ie.document.getElementsByTagName("td")
If td.className = "moar-td-form-obj" Then
If td.innerText Like "*(advanced profit centre selection)*" Then
Set a = td.getElementsByTagName("a")(0)
If Not a Is Nothing Then
ie.navigate a.href: Exit For
End If
End If
End If
Next
Set ie2 = CreateObject("InternetExplorer.Application")
ie2.Visible = True
ie2.navigate NEW_URL
While ie2.readyState <> 4: DoEvents: Wend
Set textarea = ie2.document.getElementById("pasteProfitCenter")
If Not textarea Is Nothing Then
Sheets("Sheet1").Select
With ActiveSheet 'Sheets("PC Listing")
arr = WorksheetFunction.Transpose(.Range(.[A3], .Cells(Rows.Count, "A").End(xlUp)))
End With
If Not IsArray(arr) Then arr = Array(arr)
textarea.Value = Join(arr, DELIMITER)
End If
. . .
End sub
I think you have a timing problem. You are proceeding with the code before the 2nd webpage is fully loaded. It is possible that the readystead property is valid and the webpage is still busy. You are probably trying to paste the data into the webpage and the object isn't fully loaded.
I simple way of proving this is to put a break point in you code before you write to the webpage. the wait 30 seconds before proceeding.
from
While ie2.readyState <> 4: DoEvents: Wend
to
Do While IE2.busy = True Or _
IE2.readystate <> 4
DoEvents
Loop
这篇关于将数据从XL Sheet传递到网站;当ie2打开时失去控制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
