vba 如何使用 SendKeys 从 DOS 应用程序复制粘贴到 Excel？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15282825/
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
How to copy-paste from DOS application to Excel using SendKeys?
提问by Peter P.
I'm trying to simulate this action:
我试图模拟这个动作：
- activate another application,
- send keystroke ctrl+c,
- go back to Excel,
- send key stroke ctrl+v
- 激活另一个应用程序，
- 发送击键 ctrl+c,
- 回到Excel，
- 发送击键 ctrl+v
and have that value in a cell.
并在单元格中具有该值。
It's a DOS window style application, so keystroke is the only way to manage it.
它是一个 DOS 窗口风格的应用程序，所以击键是管理它的唯一方法。
I managed to activate and to input keystrokes such as ENTER into that DOS style application, but when I try ctrl+Cit is not seen to do anything.
我设法激活并将诸如 ENTER 之类的击键输入到该 DOS 样式应用程序中，但是当我尝试ctrl+ 时，C它没有做任何事情。
I tried simulating it in Excel VBA with:
我尝试在 Excel VBA 中模拟它：
Range("E7").Select
SendKeys "^c"
Range("G7").Select
SendKeys "^v"
The E7 value is not copied, but G7 (paste destination) is highlighted as if it was selected for copying.
E7 值不会被复制，但 G7（粘贴目标）被高亮显示，就好像它被选中进行复制一样。
Note: I am not trying to copy things from Excel to Excel, but to execute keystrokes using Excel.
注意：我不是要尝试将内容从 Excel 复制到 Excel，而是要使用 Excel 执行按键操作。
回答by Tom Saenen
I ran into the same issue today.
我今天遇到了同样的问题。
I solved it by waiting a bit after sending CTRL-C to the application. It seems nothing is copied if you immediately execute another script line.
我在将 CTRL-C 发送到应用程序后稍等一下就解决了这个问题。如果您立即执行另一个脚本行，似乎不会复制任何内容。
SendKeys "^c"
WScript.Sleep 100 ' wait a bit after CTRL+C (otherwise nothing is copied)
In fact, the same issue seems to happen when switching to another app and sending CTRL+V. Again, I solved it by waiting a bit:
事实上，切换到另一个应用程序并发送 CTRL+V 时似乎会发生同样的问题。再次，我通过等待解决了它：
AppActivate "Microsoft Excel"
WScript.Sleep 100 ' wait a bit after making window active
SendKeys "^v"
回答by DMM Wallnut
The problem is that copying and pasting takes some time and VBA is not waiting for it.
问题是复制和粘贴需要一些时间，而 VBA 不会等待它。
First you need to specify second argument of send keys method which is "Wait". Set it to true. Thus VBA execution is waiting for until sending keys is completed.
首先，您需要指定发送密钥方法的第二个参数，即“等待”。将其设置为真。因此 VBA 执行一直在等待，直到发送密钥完成。
Secondly you need to wait until process of copying data to clipboard is completed. "Wait" in sendkeys is not doing it because it's not about sendking keys by VBA but it's about Windows working with clipboard. To do it please use my function IsCopyingCompleted.
其次，您需要等到将数据复制到剪贴板的过程完成。sendkeys 中的“等待”不是这样做的，因为它不是关于通过 VBA 发送密钥，而是关于 Windows 使用剪贴板。为此，请使用我的函数 IsCopyingCompleted。
Here's how final can look like:
这是 final 的样子：
SendKeys "^a", True 'Select all
SendKeys "^c", True 'Copy
Do
DoEvents
Loop Until Me.IsCopyingCompleted()
YourSheet.Paste
Function IsCopyingCompleted() As Boolean
'Check if copying data to clipboard is completed
Dim tempString As String
Dim myData As DataObject
'Try to put data from clipboard to string to check if operations on clipboard are completed
On Error Resume Next
Set myData = New DataObject
myData.GetFromClipboard
tempString = myData.GetText(1)
If Err.Number = 0 Then
IsCopyingCompleted = True
Else
IsCopyingCompleted = False
End If
On Error GoTo 0
End Function
回答by David
I had this same problem - I developed code and it worked on my computer - SendKeys "^v"...but it did not work on another user's computer! I was trying EVERYTHING...delays, appreciate, accessig access from Excel, having the user check various settings, selection.paste...no good. Finally I noticed different syntaxes...we were using the same office version, I'm not sure about Windows. But I entered 6 different syntaxes and had it skip over if it failed....SendKeys with a capital v, enclosed in squigley brackets, each component in squigleys separated by a plus sign, etc. IT WORKED! 2 of the commands put the ^V in the body of outlook...I remove those 2 and I'm golden.
我遇到了同样的问题 - 我开发了代码并且它可以在我的计算机上运行 - SendKeys“^v”......但它在其他用户的计算机上不起作用！我正在尝试一切......延迟，欣赏，从 Excel 访问，让用户检查各种设置，选择.粘贴......不好。最后我注意到不同的语法……我们使用的是相同的办公版本，我不确定 Windows。但是我输入了 6 种不同的语法，如果失败就跳过它....SendKeys 用大写的 v，括在波浪形括号中，波浪形中的每个组件用加号隔开，等等。它起作用了！其中 2 个命令将 ^V 放在 Outlook 的正文中……我删除了那些 2 个，我很高兴。
