Copy Paste to External Application
Sometimes I find myself copy-pasting between Excel and another application.
In this example, I have a table of three columns: First Name, Last Name, Birth Date.
My external application has 3 text boxes, one for each of those values.
I can't just copy the 3 cells from Excel and paste them to my App, because they would all end up in the first text box!
But, by running VBA SendKeys with a specially crafted string, I can send keystrokes for tabbing to the 2nd and 3rd text boxes.
I also need to activate the SendKeys procedure only when my cursor is positioned correctly, or things could get messy.
My approach is to run a macro that sits there listening for F6 before activating SendKeys.
I've also included listening for the Esc key, just in case I change my mind.
' Virtual-Key Codes http://msdn2.microsoft.com/en-us/library/ms645540(VS.85).aspx
Const VK_F6 = &H75
Const VK_ESC = &H1B
Sub Scanning()
Dim lngRow As Long, str As String
lngRow = Selection.row
str = Cells(lngRow, 1) & vbTab & Cells(lngRow, 2) & vbTab & _
Format(Cells(lngRow, 3), "dd-mmm-yyyy")
MsgBox "Click OK, then click the First Name box on the external application, then press F6 on the keyboard"
WaitAndSend str, VK_F6, VK_ESC
End Sub
Sub WaitAndSend(SendString As String, ExecuteKey As Long, CancelKey As Long)
Do
DoEvents
If GetAsyncKeyState(CancelKey) <> 0 Then Exit Do
If GetAsyncKeyState(ExecuteKey) <> 0 Then
SendKeys SendString, True
Exit Do
End If
Loop
End Sub
fzz:
Sendkeys works in the same way a hammer works for driving screws or an ax works for sharpening pencils.
GENERAL keyboard/mouse macros are VERY HANDY, but VBA/VBScript Sendkeys macros are VERY POOR TOOLS with which to accomplish this. A special purpose tool like AutoIt, which can work with any other programs, can manage this much better and much more safely. Also, it can simulate mouse movements and drag/drop operations.
USE THE BEST TOOL FOR THE TASK! For this sort of thing, that's NOT Excel/VBA.
5 April 2008, 3:49 pmRob van Gelder:
AutoIt is a fine program, absolutely. I've used it for automating software installations on hundreds of computers.
5 April 2008, 6:49 pmI'm not certain why you'd think SendKeys does any more or any less than it claims to though.
SendKeys is OK to use in my book.
fzz:
SendKeys is more fragile. That's not always SendKeys's fault. Tools like AutoIt include extra facilities to check exactly which application is active. Also, again not a problem with SendKeys itself but with VBA, there's no VBA equivalent for AutoIt's WinWaitActive. You'd need to call a Windows API function in VBA to ENSURE that the active application were the intended application. Running AppActivate doesn't ensure the indicated application becomes active immediately. Also, AutoIt can use window handles rather than window titles, making it much more robust than VBA. Then there's all the Control* functions that may be used to ENSURE that AutoIt sends particular text to the right place.
5 April 2008, 7:34 pmCharlie Hall:
I don't know if this will work, but I wonder if an alternate solution would be to put the information in the clipboard the way that you want it to come out - is there a way to put your formatted string "str" with the tabs in the clipboard. Then when you paste, would it not deposit in the correct boxes.
If so, I could see this having some interesting applications. When I copy/paste from Excel into an outlook email, it deposits the cells in a table - which I have to remove in order to send the text that is inside the cell. Outlook's paste special is inactive so I have to do some manipulation to get at the data. If one could copy and put the cell information into the clipboard in an non-excel format (text with tabs) then it might be quite useful. And it would be more generic - right now your script is very application specific.
8 April 2008, 11:16 amfzz:
Charlie Hall - Rob's example was pasting data from cells in 3 adjacent columns into fields in a different application. I've just selected similar data in Excel and I'll paste it immediately below.
a b c
As you see, Excel put tabs between the values, and this text field accepted the tabs as part of the string.
Whatever's in the clipboard is pasted in as a single chunk. If the destination field processes individual characters, it might either choke on tab characters, accept them (as above) or treat them as commands to advance to the next field. There's no guarantee of consistency between target applications.
SendKeys, OTOH, sends characters one at a time as if the user had pressed individual keys. If I press the [Tab] key from this edit field, my browser advances me to the Submit Comment button.
This is an example of browsers (and applications generally) treating tab characters (decimal ASCII code 9) differently when processed as part of a multiple byte text block (which happens when pasting from the clipboard) or when processed as a single keystroke.
Windows just doesn't process I/O like character mode stdin/stdout I/O.
8 April 2008, 2:05 pmfzz:
OK, Excel put tabs between the a b c, but this blog converted the tabs to spaces. Maybe using VB tags will work.
fzz:
Nope, but it replaced single tabs with multiple spaces.
8 April 2008, 2:10 pmFelipe:
I would have to dissagree with fzz about Excel VBA.SendKeys may not be the best way to exchange or transfer data between applications but VBA is powerful I think.
10 April 2008, 11:50 amI've developed an application that automatically transfers data within MS Office (Excel,Access,Word)and two non MS office programs (SigmaPlot and R statistics) and I use VBA to acomplish that.
Jon Peltier:
Felipe -
Did you know there is a COM version of R? This means it can be run from inside of Excel, using VBA to drive it. I haven't tried it yet, but maybe soon.
10 April 2008, 4:14 pmDoug Jenkins:
Jon - I don't know why I think having yet another "must do" project for when I have a few days to spare is a good idea, but thanks anyway
Download poge for anyone else interested:
http://cran.r-project.org/web/packages/rcom/
10 April 2008, 4:57 pmDoug Jenkins:
More on RCOM and RExcel here:
http://sunsite.univie.ac.at/rcom/
10 April 2008, 7:03 pmFelipe:
Jon:
11 April 2008, 8:27 amThat's what I am using, If you have R installed on your machine,You will need to download rcom and DCOM and of course RExcel.Although RExcel gives you most of the tools needed to import and export data, one needs to modify the code slightly to adapt it to each particular situation.Also, There's the Rcmdr (R Commander)package that is very helpful for R beginners and don't know how to write R code.A new version of RExcel just came out a couple of days ago,check it out here:
http://rcom.univie.ac.at/download/devel/RExcelInstaller_2.0-6.zip
Russell:
This may not be the place to ask, but I'm trying to do the reverse... I'm using an IeApp navigated to a specific URL that has data I want to copy back to Excel. I'm trying to avoid using SendKeys, but if I did it would go like this:
IeApp.Navigate...
Ctrl A (Select All)
Ctrl C (Copy)
Switch back to Excel
Ctrl V
Any thoughts on this? I'm not a VBA expert - finding the IeApp code was a major accomplishment for me.
18 April 2008, 8:19 pmRob van Gelder:
Russell: Have you tried a Web Query? It's available from Excel's Data menu under Import External Data, New Web Query.
20 April 2008, 3:06 pmRussell:
I'm embarrassed to say I'd never used that tool before, Rob - very nice. It does exactly what I want, except the pages I'm pulling data from have to go through a login sequence and/or the URL is generic - I think that has something to do with how the data is requested - session variables vs passed through URL. Thanks for the reply! If you have any other thoughts, I really appreciate it. Feel bad that I haven't (and from the sounds of things might not) contributed anything to this board before asking for help.
20 April 2008, 11:22 pmThanks again -