vba 创建宏从excel中的单元格复制到浏览器中的文本框，然后单击添加
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9632249/
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
Create a macro to copy from a cell in excel to a text box in a browser, then click add
提问by user1242345
I have already created a Macro using a 3rd party application that does what i want 80% of the time. The problem is that i can't leave it running because it sometimes bugs out, or the browser takes a little longer to respond.
我已经使用 3rd 方应用程序创建了一个宏，该应用程序可以在 80% 的时间内完成我想要的操作。问题是我不能让它继续运行，因为它有时会出错，或者浏览器需要更长的时间来响应。
I would like to know if i could create what i wanted in the macro manager that comes with excel (VBA).
我想知道是否可以在 excel (VBA) 附带的宏管理器中创建我想要的内容。
I want to:
我想要：
- copy from A1, go to browser, paste in text box 1 (about half way down the page)
- Go back to excel, copy from B1, go to browser, paste in text box 2
- Click button in browser that says "Add"
- Wait for pop up box (javascript alert) to disapear
- Rinse and repeat X times, this time from A2 and B2.
- 从 A1 复制，转到浏览器，粘贴到文本框 1（大约页面下方的一半）
- 返回 excel，从 B1 复制，转到浏览器，粘贴到文本框 2
- 单击浏览器中显示“添加”的按钮
- 等待弹出框（javascript 警报）消失
- 冲洗并重复 X 次，这次是从 A2 和 B2。
Can you help?
你能帮我吗？
回答by Siddharth Rout
user1242345, there are two ways to go about it.
user1242345，有两种方法可以解决。
Way 1
方式一
You can launch the URL in WebBrowser1 from VBA and then write to the textbox directly using .GetElementByID
您可以从 VBA 启动 WebBrowser1 中的 URL，然后使用直接写入文本框 .GetElementByID
For example
例如
WebBrowser1.Document.getElementById("TextBoxName").Value = "Whatever"
WebBrowser1.Document.getElementById("TextBoxName").Value = "Whatever"
Way 2
方式二
Use XMLHTTP. This is way much faster than Way 1
使用 XMLHTTP。这比方式1快得多
If you can share the link then I can give you an exact answer?
如果你可以分享链接，那么我可以给你一个确切的答案？
FOLLOWUP
跟进
Thanks for your response.. i can't give you the link as it's a password protected page. However here is the html for the form i want to paste into if this helps. pastebin.com/cWrwfKBf – user1242345 17 mins ago
Both, i would like to copy from cell A1 and paste into Feed_name, then go back to excel and copy from B1, and paste into feed_url. Thanks for your help i really appreciate it. – user1242345 5 mins ago
感谢您的回复.. 我不能给你链接，因为它是一个受密码保护的页面。但是，如果这有帮助，这里是我想粘贴到的表单的 html。pastebin.com/cWrwfKBf – user1242345 17 分钟前
两者，我想从单元格 A1 复制并粘贴到 Feed_name，然后返回到 excel 并从 B1 复制，然后粘贴到 feed_url。感谢您的帮助，我真的很感激。– user1242345 5 分钟前
I copied the source code in a text file and saved it as Test.Htm on my desktop. Please see the example below on how to write to the first textbox. i am sure you can replicate it for the next ;)
我将源代码复制到一个文本文件中，并将其作为 Test.Htm 保存在我的桌面上。请参阅下面有关如何写入第一个文本框的示例。我相信你可以在下一个复制它；)
To run this, create a userform in Excel and place the WebBrowser1 control and a CommandButton Control in the form. See Snapshot.
要运行它，请在 Excel 中创建一个用户窗体并将 WebBrowser1 控件和一个 CommandButton 控件放在窗体中。请参阅快照。
SNAPSHOT 1
快照 1
Paste this code in the code area of the userform.
将此代码粘贴到用户表单的代码区域中。
Private Sub CommandButton1_Click()
Dim url As String
url = "C:\Documents and Settings\Siddharth Rout\Desktop\Test.Htm"
WebBrowser1.Navigate url
WaitForWBReady
WebBrowser1.Document.getElementById("feed-create-feed_name").Value = "Whatever"
End Sub
Private Sub Wait(ByVal nSec As Long)
nSec = nSec + Timer
While Timer < nSec
DoEvents
Wend
End Sub
Private Sub WaitForWBReady()
Wait 1
While WebBrowser1.ReadyState <> 4
Wait 3
Wend
End Sub
When you click on the button the text gets auto populated as show below.
当您单击按钮时，文本会自动填充，如下所示。
SNAPSHOT 2
快照 2
MORE FOLLOWUP
更多跟进
Unfortunately this is my first time using VB, so i'm failing at creating this loop.. :( – user1242345 2 mins
不幸的是，这是我第一次使用 VB，所以我无法创建这个循环.. :( – user1242345 2 分钟
I usually don't post a solution in such a scenario but advise the Askerto learn VBA but since I have already posted a major chunk so I will finish it for you. But any more questions from you this point onwards has to be followed by the code that you have written yourself:)
在这种情况下，我通常不会发布解决方案，而是建议提问者学习 VBA，但由于我已经发布了一个主要内容，因此我会为您完成。但是从这一点开始，您提出的任何问题都必须遵循您自己编写的代码:)
UNTESTED
未经测试
Try this
尝试这个
Private Declare PtrSafe Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim url As String
Dim lastRow As Long
url = "C:\Documents and Settings\Siddharth Rout\Desktop\Test.Htm"
'~~> This is the sheet where the values has to be picked up from
Set ws = Sheets("Sheet1")
With ws
'~~> Get the Last Row in Sheet1
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
'~~> Loop through the range
For i = 1 To lastRow
'~~> Navigate to the URL
WebBrowser1.Navigate url
WaitForWBReady
'~~> Input Values
WebBrowser1.Document.getElementById("feed-create-feed_name").Value = .Range("A" & i).Value
WebBrowser1.Document.getElementById("feed-create-feed_url").Value = .Range("B" & i).Value
'~~> Click Button
WebBrowser1.Document.getElementsByTagname("Input")(3).Click
WaitForWBReady
Next
End With
End Sub
Private Sub Wait(ByVal nSec As Long)
nSec = nSec + Timer
While Timer < nSec
DoEvents
Sleep 100
Wend
End Sub
Private Sub WaitForWBReady()
Wait 1
While WebBrowser1.ReadyState <> 4
Wait 3
Wend
End Sub
HTH
HTH
Sid
锡德
