vba 如何创建脚本以从 Excel 复制单元格并粘贴到位于浏览器上的文本框中

How to create script to copy a cell from Excel and paste into a text box located on a browzer

提问by Mark Nold

At work we have to track our calls via an online application made via vb.net. To enter the needed info into the text boxes and drop down boxes you have to deal with more than one page. It is cumbersome to have to type or even copy and paste the needed info.

在工作中，我们必须通过 vb.net 制作的在线应用程序来跟踪我们的通话。要在文本框和下拉框中输入所需的信息，您必须处理不止一页。必须输入甚至复制和粘贴所需的信息是很麻烦的。

I want to be able to type the info onto one page in excel and then have a script be able to copy and paste into the text boxes located on the browser screen automatically, without me having to do the copying and pasting.

我希望能够在 Excel 中的一页上键入信息，然后让脚本能够自动复制并粘贴到位于浏览器屏幕上的文本框中，而无需我进行复制和粘贴。

Is this possible? Can anyone point me in the right direction?

这可能吗？任何人都可以指出我正确的方向吗？

I noticed when you try to create an email account they always have that one box where you have to type the squiggly letters into. I assume this is because there are apps that can automatically paste or write to a text box located on a browzer screen.

我注意到当你尝试创建一个电子邮件帐户时，他们总是有一个框，你必须在其中输入波浪形的字母。我认为这是因为有些应用程序可以自动粘贴或写入位于浏览器屏幕上的文本框。

Any help to point me in the right direction would be great.

任何帮助我指明正确方向的帮助都会很棒。

回答by Shalom Craimer

As far as I can see, what you want is some 3rd party script or a VBA script in the Excel worksheet to do the copy/pasting for you.

据我所知，您想要的是 Excel 工作表中的一些 3rd 方脚本或 VBA 脚本来为您进行复制/粘贴。

What I can tell you will notwork, is to try and have the browser do the work. There's a lot of restrictions placed on what a browser can and cannot do, and they mostly involve preventing it from accessing data on your computer. This includes copying from Excel spreadsheets.

我可以告诉你不会工作，是尝试让浏览器完成工作。浏览器可以做什么和不能做什么有很多限制，它们主要涉及阻止它访问您计算机上的数据。这包括从 Excel 电子表格复制。

What I think you may be able to do, is to build a very very rigid Windows Shell script. Using the SendKeysfunction, you can execute a set of key-presses exactly as if you had done them yourself.

我认为您可以做的是构建一个非常严格的 Windows Shell 脚本。使用该SendKeys功能，您可以执行一组按键，就像您自己完成的那样。

You could have the script do something like this:

你可以让脚本做这样的事情：

1. Activate the Excel's window
2. Move to the 1st cell you wish to copy. (By using the arrow keys.)
3. Copy it. (By using CTRL-INSERT)
4. Activate the VB.NET window
5. Move to the place you insert the data from the 1st cell. (Using TAB over and over.)
6. Paste it. (By using SHIFT-INSERT)

1. 激活 Excel 的窗口
2. 移动到您要复制的第一个单元格。（通过使用箭头键。）
3. 复制它。（通过使用 CTRL-INSERT）
4. 激活 VB.NET 窗口
5. 移动到您从第一个单元格插入数据的位置。（反复使用 TAB。）
6. 粘贴它。（通过使用 SHIFT-INSERT）

I must warn you, that such a script will fail if anythingis out of place - the current cursor position is wrong, the text of each of the windows' has changed, etc.

我必须警告你，如果有任何不合适的地方，这样的脚本将会失败- 当前光标位置错误，每个窗口的文本都已更改，等等。

Here's an example:

下面是一个例子：

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.AppActivate "the-exact-text-in-the-titlebar-of-Excel"
WScript.Sleep 50
WshShell.SendKeys "{RIGHT}"
WshShell.SendKeys "{RIGHT}"
WshShell.SendKeys "{DOWN}"
WshShell.SendKeys "^{INSERT}"
WshShell.AppActivate "the-exact-text-in-the-titlebar-of-vb.net-browser-window"
WScript.Sleep 50
WshShell.SendKeys "{TAB}"
WshShell.SendKeys "{TAB}"
WshShell.SendKeys "{TAB}"
WshShell.SendKeys "+{INSERT}"

The example assumes:

该示例假设：

1. The cell to copy from Excel is in the 2rd column to the right of the current cursor position in Excel, and one row down.
2. The place to paste in the vb form is the 3th control from the current cursor position in the browser.

1. 要从 Excel 复制的单元格位于 Excel 中当前光标位置右侧的第二列，向下一行。
2. vb 表单中粘贴的位置是浏览器中从当前光标位置开始的第 3 个控件。

回答by Mark Nold

I can't think of any particular way other than @scraimer's SendKeys to do exactly what you asked for. Take his warnings to heart, SendKeys is very tricky to do reliably.

除了@scraimer 的 SendKeys 之外，我想不出任何特定的方式来完成您的要求。牢记他的警告，SendKeys 很难可靠地执行。

Another possibility to get the same end result is to change your .aspx to accept GET variables and pre-populate its fields on PageLoad() you might have 3 variables that populate the name, company and message;

获得相同最终结果的另一种可能性是更改您的 .aspx 以接受 GET 变量并在 PageLoad() 上预填充其字段，您可能有 3 个填充名称、公司和消息的变量；

http://www.mysite.com/myform.aspx?var1=JoeBolgs&var2=Company+XYZ&var3=Help+me+please

Then in VBA create a form or sheet that a user can fill out var1 -> var3

然后在 VBA 中创建一个用户可以填写的表单或工作表 var1 -> var3

// obviously make your var names nicer than mine :)
myUrl = "http://www.mysite.com/myform.aspx?"
myUrl = myUrl & "var1="  & strVar1
myUrl = myUrl & "&var2=" & strVar2
myUrl = myUrl & "&var3=" & strVar3

OpenBrowserURL(myUrl)

and have a OpenBrowserURL()routine

并有OpenBrowserURL()规律

Sub OpenBrowserURL(strURL As String)
  ActiveWorkbook.FollowHyperlink Address:=strURL, NewWindow:=True
End Sub

I use something similar for help pages called via a menu and it works a treat. A user clicks a menu and VBA opens a url with some data in the user's favourite browser.

我对通过菜单调用的帮助页面使用了类似的东西，它很有效。用户单击一个菜单，VBA 在用户最喜欢的浏览器中打开一个包含一些数据的 url。

The only other way i've interacted with a website from VBA programatically is by making calls to wininet.dll directly for GET and POST data. Though don't think it would help in your question.

我以编程方式与 VBA 网站交互的唯一另一种方式是直接调用 wininet.dll 以获取 GET 和 POST 数据。虽然不认为这对你的问题有帮助。

One last thought, if you want to stop people gaming your site somehow, your should encrypt your vars. It's not to hard to encrypt then UUEncode the data and send that as a request variable. Then just URLdecode it and decrypt it on the server using the same algorythm you used to encrypt it.

最后一个想法，如果您想以某种方式阻止人们对您的网站进行游戏，您应该加密您的变量。加密然后 UUEncode 数据并将其作为请求变量发送并不难。然后只需对它进行 URLdecode 并使用与加密它相同的算法在服务器上对其进行解密。

Good luck.

祝你好运。

回答by Mark Nold

join two excel sheets + a excel cell change + change another sheet in vb.net with source

加入两个excel表+一个excel单元格更改+在vb.net中使用源更改另一个表