0 votes
in VBA by Beginner (36 points)

Hi there!

Another day, another conundrum :S

I have been working on a macro that opens Word

Call Shell("C:\Program Files\Microsoft Office\root\Office16\WINWORD.EXE", vbNormalFocus)

This works perfectly and things happen - UNTIL Outlook decides to crash the party with notifications (which then exists in the foreground).

I read about the AppActivate (https://wellsr.com/vba/2015/excel/appactivate-activate-folder-or-application-if-already-open/)

Do I App.Activate everytime I need to send a keystroke to Word?

P/s: I never quite understood the "purpose" of declaring Public at the start. Or Option Explicit for that matter :( When do I do that?

1 Answer

+1 vote
by Skilled (741 points)
selected by
 
Best answer

Yes, you should use AppActivate before sending a keystroke to Word. 

If you are using VBA's SendKeys statement or Excel's Application.SendKeys method you can send multiple keystrokes each time. I recommend adding the 2nd argument True; for example,
    SendKeys "Hello World!", True
    SendKeys "{ENTER}", True

You will probably notice that each SendKeys will toggle NumLock; this is a common bug in Excel, but it will not be obvious if you have paired SendKeys (2,4,6,...). You can avoid this problem by using WshShell.SendKeys instead (which does not take a 2nd argument):
    Dim WshShell As Object
    Set WshShell = WScript.CreateObject("WScript.Shell")
    WshShell.SendKeys "Hello World!"
    WshShell.SendKeys "{ENTER}"
    ...
    Set WshShell = Nothing
    End Sub

Re. P/S in your question: Public is the default; the purpose of declaring Public is to make it clearly NOT Private. Also, see VBA Option Explicit.

by Beginner (36 points)
@JWoolley

Thanks so much for this! I managed to incorporate this before every instance of keystroke sending was required. (Previously, didn't do this, so sometimes it was sending strokes to the wrong programme!)

Really appreciate the time you took to help me with this!

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...