Help with SendKeys - only first SendKey command works

[OddsAre]

Howdy howdy,

I'm trying to paste Excel values into an external program (not an Office program). I usually have a list of maybe 20+ rows to enter, and find myself basically doing the following routine clicks/keys:

Choose the starting row in Excel
copy in excel
Alt-tab to focus on non-Office program
type "o"
tab
tab
tab
type "123"
tab
paste
tab
enter
Alt-Tab (to go back to Excel)
Select the next row and repeat

Basically:
I would like a script that starts with an excel cell active. I want to copy that cell, ALT TAB, TAB, enter "O", TAB, TAB, TAB, "123", TAB, PASTE, TAB, ENTER, ALT-TAB back to Excel, and go down one.

I have figured out how to get Excel to Alt-Tab, but can't for some reason get it to do anything after that:

Sub CopyWaitCopy()
Dim lastrow As Integer

lastrow = InputBox("What is the last row number of the entries?", "Stop!") 
' the above is there so my loop doesn't run forever

1:
 ActiveCell.Copy

Application.SendKeys "%{Tab}", True 'This works, I get it to alt-tab and put focus on the second program.
Application.SendKeys "{Tab}", True
Application.SendKeys "O", True
Application.SendKeys "{Tab}", True
Application.SendKeys "{Tab}", True
Application.SendKeys "{Tab}", True

Application.SendKeys "123", True
Application.SendKeys "{Tab}", True

Application.SendKeys "^V", True
Application.SendKeys "{Tab}", True
'Application.SendKeys "{Enter}", True  ' -- is this how I would register the Enter key being pressed?



Application.SendKeys "%{Tab}", True


ActiveCell.Offset(1, 0).Select

If ActiveCell.row < lastrow Then
GoTo 1
Else
Exit Sub
End If
End Sub

I've read around online and apparently SendKeys isn't the most reliable. If there's no way to do what I'm looking to, that is fine. It's just a really repetitive task and I was hoping to automate with VBA.

Thank you very much for any insight/tips!

Ah, I have - Windows XP, Office 2010

 

[ClimoC]

There's no context. It's sending the Key press to the application, which will deal with it in its current context.

I can't say for certain, but I've not heard of SendKeys being used to control other programs.

 

[ScottR]

When I've tried similar things in the past I've found adding a slight delay between sent keys can help.

Edit: Or using AppActivate to activate your application window rather than alt-tab!

 

[OddsAre]

When I've tried similar things in the past I've found adding a slight delay between sent keys can help.

Edit: Or using AppActivate to activate your application window rather than alt-tab!

I have read about AppActivate, but how do I know what VBA will call the Application? Is it simply what the TaskBar window says, or Task Manager?


Ah! When I add

Application.Wait Now + TimeValue("0:00:02")

between each line, it works! That is odd.


Now, I really don't think this is possible, but I can't use Variables with sendkeys eh? Like

dim Variable as String
String = Activecell.value

Application.SendKeys "%{Tab}", True 'This works, I get it to alt-tab and put focus on the second program. 'Then, somehow on this line, enter the String value in the other program

That way, if I want cells A2, B3, and C4, copied over to the other program, I don't have to do a bunch of coding with ALT-TAB, move
active cell over one, down one, copy, alt-tab, paste, alt-tab, move over one, down one, etc. etc.

Thanks for your help, I appreciate it!

 

[OddsAre]

ADVERTISEMENT

Hm, it doesn't seem my PASTE script part is working:

Application.SendKeys "^V", False

 

[ScottR]

I have read about AppActivate, but how do I know what VBA will call the Application? Is it simply what the TaskBar window says, or Task Manager?

It's what the TaskBar window says.

Now I don't think this is possible, but I can't use Variables with sendkeys eh?

No that should be possible

Application.SendKeys YourVariable

Provided you're using a plain string. You'll have to alter the variable to include any of the sent key characters if necessary

Send the string "fish pie" will result in "fish pie"
Sending "+fish pie" will result in "Fish pie" etc

As you've already stated, send keys is flaky at best so good luck!

 

[ScottR]

ADVERTISEMENT

Hm, it doesn't seem my PASTE script part is working:

Application.SendKeys "^V", False

Application.SendKeys "^v"

 

[OddsAre]

Application.SendKeys "^v"

Thank you so much Scott for your help! Your suggestions worked! Here's my full script (in case you see somewhere I can combine lines/reduce the amount of code).

Random quick question, what's the least amount of time I can set Application.Wait Now + TimeValue("0:00:05") to? Or can I just leave the Application.Wait Now in order for the code to wait and still work. (Note: My code didn't work until someone suggested I added a wait parameter...I did, and it worked! ...that's why I have the wait lines).

Sub CopyWaitCopy()
Dim lastrow As Integer

lastrow = InputBox("What is the last row number of the entries?", "Stop!")
Dim sched As String
Dim Amount As String
Dim Ref As String

Start:


Ref = ActiveCell.Value
sched = ActiveCell.Offset(0, -1).Value
Amount = ActiveCell.Offset(0, 1).Value

' Application.SendKeys "%{Tab}", True
AppActivate "Entering Receipts"
Call AppActivate("Entering Receipts")
Application.Wait Now + TimeValue("0:00:01")
Application.SendKeys "O", True
Application.Wait Now + TimeValue("0:00:01")
Application.SendKeys "{Tab}", True
Application.Wait Now + TimeValue("0:00:01")
Application.SendKeys "{Tab}", True
Application.Wait Now + TimeValue("0:00:01")
Application.SendKeys "{Tab}", True
Application.Wait Now + TimeValue("0:00:01")
Application.SendKeys "123", True
Application.Wait Now + TimeValue("0:00:01")
Application.SendKeys "{Tab}", True
Application.Wait Now + TimeValue("0:00:01")
Application.SendKeys "{Tab}", True
Application.SendKeys sched, True
Application.Wait Now + TimeValue("0:00:01")
Application.SendKeys "{Tab}", True
Application.Wait Now + TimeValue("0:00:01")
Application.SendKeys "{BS}"
Application.Wait Now + TimeValue("0:00:01")
Application.SendKeys Ref
Application.Wait Now + TimeValue("0:00:01")
Application.SendKeys "{Tab}", True
Application.SendKeys Amount
Application.Wait Now + TimeValue("0:00:01")
Application.SendKeys "{Tab}", True
Application.Wait Now + TimeValue("0:00:01")
Application.SendKeys "~", True
Application.Wait Now + TimeValue("0:00:01")
AppActivate "Microsoft Excel"
Application.Wait Now + TimeValue("0:00:01")
ActiveCell.Offset(1, 0).Select
If ActiveCell.row < lastrow Then
GoTo Start
Else
Exit Sub
End If

End Sub

 

[OddsAre]

(I don't know why I can't edit the above post, I don't see "Edit" anywhere).

Hm, for some reason when I ran it (just as I have above), it no longer works. Is this something the code is doing, or is it just an example of why SendKeys and AppActivate are seldom recommended to use?

Thanks for any help/info!

 

[ScottR]

My code didn't work until someone suggested I added a wait parameter...I did, and it worked! ...that's why I have the wait lines

Yes, that was me! It's something I tried once when sendkeys was being flaky and it solved my problem. To answer your question I usually use a two second wait which is generally "safe". However...

(I don't know why I can't edit the above post, I don't see "Edit" anywhere).

Hm, for some reason when I ran it (just as I have above), it no longer works. Is this something the code is doing, or is it just an example of why SendKeys and AppActivate are seldom recommended to use?

Thanks for any help/info!

Probably flakiness rearing it's ugly head unfortunately. Try increasing the wait and you may see a slight increase in reliability. No guarantees unfortunately, as universally observed send keys is a last resort.