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
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.
AutoIt is a fine program, absolutely. I’ve used it for automating software installations on hundreds of computers.
I’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.
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.
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.
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.
abc
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.
OK, Excel put tabs between the a b c, but this blog converted the tabs to spaces. Maybe using VB tags will work.
Nope, but it replaced single tabs with multiple spaces.
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.
I’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.
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.
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/
More on RCOM and RExcel here:
http://sunsite.univie.ac.at/rcom/
Jon:
That’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
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. :)
Russell: Have you tried a Web Query? It’s available from Excel’s Data menu under Import External Data, New Web Query.
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.
Thanks again –
Very useful example. I use sendkeys to paste the header of invoices from Excel to an ERP application. I put the F6 approach into my macro. Now I paste from Excel row by row.
Any idea how to convert a duration of “16h” to “16?. The “16h” is a result of linking from MS Project 2003 file (Duration column) to an Excel 2003 program (Duration column). Thank you
Sorry, Rob, I don’t know where to paste this in the VB Editor (Module, sheet or workbook) and when to press F6. Can you talk us through this a bit? I’m interested in trying it as a surrogate inkey$ routine. Thanks in advance to anyone who helps. I’m using Excel 2000 if it helps.
Timmy:
Copy the code to a Module.
Execute Scanning from your main workbook by pressing Alt F8
Hmmm, it works but the third value pasted is pasted as a date. Bizarre, probably not an issue as the pasting is not what I need, just the key reading (like inkey$). Thanks for your replies, Rob.
Hi Friends,
I am trying to have a macro which when clicked should copy the text from a specific cell and paste in on a specific tab in an internal application. I am a novice, so any help for creating such a macro would be appreciated.
Hi All,
Please help me to find a macro to copy excel invoice data from cell to cell vise and should copy to other application (TOPAZ). It also have to click or automatically enter save key each time it copies/paste the data in the app. Please help me.