Wait for Notepad to be opened

[robw]

Hi,
Can someone please help with this.
I don't understand why this bit of VBA is not working as expected.
It should wait until a Notepad window is opened (asynchronously by the user) before continuing.
If a Notepad window is opened BEFORE I execute the code it behaves as expected, but if the Notepad is only opened WHILE the code is executing it does not (it stays in the loop, with eno=5)

Public Declare Sub sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
Sub gogo()
Dim eno As Integer

'wait until Notepad window is activated (asynchronously by user)

On Error Resume Next
Do
    sleep 1000
    AppActivate "Untitled - Notepad"
    eno = Err.Number
    MsgBox "Err.Number = " & eno
Loop Until eno = 0
       
MsgBox "Notepad is active"

' do things with SendKey
:
:
:

End Sub

Thanks
Rob

 

[Andrew Poulsom]

Why do you need to open Notepad? You should be able to do anything with a text file in VBA.

 

[robw]

Hi Andrew,
In this context Notepad is a surrogate for our company time-booking application, which is extremely un-user friendly. The objective is to collect/prepare my weekly time-bookings in the more friendly excel environment throughout the week and then, on a Friday, "SendKey" them into the time-booking app. (The app does not accept a simpler cut and paste approach, but I have had some success with using SendKey. I just want to make the whole process slicker)

Regards
Rob

 

[RoryA]

Try adding Err.Clear after the Do line.

 

[Andrew Poulsom]

You don't need to open Notepad and use SendKeys. You can write to a text file directly in VBA. What is it that you want to put in the text file and where do you want to save it?

 

[robw]

Thanks Guys,
Err.Clear fixed it... thanks Rory.
Andrew... not sure if you saw my response to your first query(?). I'm ultimately going to write to a web-based time-booking app, not a text file

Thanks for your help
Cheers
Rob

 

[Norie]

Rob

If the application you want to write to is web based then there are probably better ways to do this then using the notoriously unreliable SendKeys.