Sendkeys bug? Office 07

[Kyle G]

I have a number of macros which take data from a spreadsheet, activate a
third party program and send the data as keys into this program.

This worked perfectly fine in Office 2003, however now with 07 if for any
reason i stop the process and restart sending keys - it is buggy and only
some of the keys get sent! Particularly the ENTER key does not ever work in
the third party program, as well as other seemly random keys at various times
(sometimes they will work, other times not so much). I'm kinda baffled as to
why.

I found a temperary solution by going into my code when this happens
(stopping the macro completely), and find/replace all instances of "Sendkeys"
with "Application.Sendkeys". then retrying, and now it obviously does not
work with the other program. At this point i now switch it BACK to all
"Sendkeys" from "Application.Sendkeys" and it starts working again! That is
until it gets hungup once more . . .

Is this a common bug, and does anyone know of a solution to the issue I've
been encountering?

The code is for a process I'd like to move to other not so VBA savy
colleagues, and with all the problems I've been experiencing since office
2007 it's just not possible yet.

Thanks!

 

[Gary''s Student]

Your SendKeys usually goes into a type of keyboard buffer. You need to allow
the application (Excel) to process the keystrokes at the appropriate time.
Kind of like pitching a bunch of baseballs at a catcher. You need to give
the catcher the opportunity to catch the balls.

Here is an example using DoEvents:

Sub version_2()
For i = 1 To 26000
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
DoEvents
If IsEmpty(Selection.Value) Then
Exit Sub
End If
Next
End Sub

The DoEvents insures that the keystrokes are "fielded" by Excel.

 

[Peter T]

My guess is it's not Excel 2007 but Vista that's preventing your Sendkeys
from working, at least in VBA.. There are workarounds if you search this
group.

Regards,
Peter T