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

 

[OddsAre]

Yes, that was me! ...

D'oh

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.

Ah okay, that's what I was thinking.

Thanks so much Scott for your help! It's greatly appreciated.

 

[ScottR]

You're welcome!