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 Expert (911 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
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

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

...