AppActivate losing focus

[Meg T]

Hi,

I'm trying to open a non-MS application and then use the SendKeys command. The window activates, but then it seems to lose focus by the next line (SendKeys). I can't even get it to work in Notepad with an example of simple code I found online:


Sub Write_Notepad()

AppActivate ("Untitled - Notepad")
SendKeys ("I am sending this from Excel VBA to NotePad")

End Sub


I can open a new Notepad window, but again, SendKeys won't write to it. With some variations of the code, I've had SendKeys text show up in the VBA text editor -- but never Notepad.

I am sure Notepad is activating in part because when I change the name, it throws an exception. This doesn't happen with "Untitled - Notepad."

Any suggestions appreciated!

Meg

 

[Meg T]

Hello everyone,

Thanks for both the code & the pointers. I was able to *everyone's* code to work in Notepad! It is good to be aware of the timing and focus issues w/SendKeys and have an alternative, too. The next step is for me to decipher what exactly you all did and see if I can replicate it with the database program (Sage MAS 90, which didn't look like it was on the site Kenneth referenced).

Kenneth, quick question -- the editor didn't like the "FolderPart" function and claimed it was undefined. When I commented out the "If" clause that contained it, everything was fine. Do I need to set a reference somewhere that I missed?

Anyway, I just ordered a VB programming guide, and I'm looking forward to exploring this more. Thanks again.

Meg

 

[Kenneth Hobson]

Sorry, I must have left that function out. Here is one that does the same thing.

Option Explicit

Sub SavePartcorrect()
  Dim myPath As String, txtPath As String
  Dim rc As Long
  Dim wb As Workbook
  
  Set wb = ActiveWorkbook
  myPath = ThisWorkbook.Path & "\"
  txtPath = myPath & "Test.txt"
  
  On Error Resume Next
  Kill txtPath
  MakeTXTFile txtPath, "First Line" & vbLf
    
  rc = Shell("NOTEPAD.EXE " & txtPath, vbNormalFocus)
  AppActivate rc, True
  
  Application.Wait Now + TimeValue("00:00:01")
  SendKeys "^{End}", True
  SendKeys Application.UserName, True
  SendKeys "{Enter}", True
End Sub

Sub MakeTXTFile(filePath As String, str As String)
  Dim hFile As Integer
  If Dir(FolderPart(filePath), vbDirectory) = "" Then
    MsgBox filePath, vbCritical, "Missing Folder"
    Exit Sub
  End If
  
  hFile = FreeFile
  Open filePath For Output As #hFile
  If str <> "" Then Print #hFile, str
  Close hFile
End Sub

Function GetFileName(Filespec As String)
  Dim FSO As Object, s As String
  Set FSO = CreateObject("Scripting.FileSystemObject")
  s = FSO.GetFileName(Filespec)
  Set FSO = Nothing
  GetFileName = s
End Function

Function GetFolderName(Filespec As String)  'Returns path with trailing "\"
'Requires GetFileName() function above
  GetFolderName = Left(Filespec, Len(Filespec) - Len(GetFileName(Filespec)))
End Function

 

[DeadasDillenger]

Hi All, I've been left a bit of an issue by a former company employee for which the previous posts in this forum seem to be similar to...
Excel 2003 - VB macro as per below the line to take a list of values in a spreadsheet and copy these out (with additional "Down" keystroke) to a different web-based application entry form...I've been told the because the web-based application is quite slow, this is why there is a 1 - 100 selection criteria at the start...
(BTW, I've removed the application out of this script and am trying to pump the data to Notepad for ease of testing in this version of the script.)
This works perfectly on Windows XP and Office 2003, but fails on Windows 7 with Office 2003. I've also tried this with Windows 7 and Office 2013 with a mixed bag of results...sometimes works, more often doesn't !
Notepad is activated but the pasting of the values never appear...I'm guessing it's something to do with application focus but no idea how to resolve !
Please can someone help (I'm in no way, shape or form a VB or Excel Macro person !!) ?
------------------------------------------
Sub allocator()

R1 = Application.InputBox _
(Prompt:="No of invoices", _
Title:="no of items 1-100 ", Type:=1)

If R1 < 1 Then GoTo hash
If R1 > 100 Then GoTo hash

Dim str(1 To 100) As Long

Dim x As Integer
Dim y As Integer


x = ActiveCell.Row
y = ActiveCell.Column

For i = 1 To R1
str(i) = ActiveSheet.Cells(x + (i - 1), y)
Next i

Cells(x + R1, y).Select

AppActivate ("Untitled - Notepad")
Application.Wait Now + TimeValue("00:00:05")

For r = 1 To R1

Application.Wait Now + TimeValue("00:00:02")
Application.SendKeys str(r)

Application.Wait Now + TimeValue("00:00:01")
Application.SendKeys "{DOWN}"
Next r

hash:

End Sub

 

[DeadasDillenger]

Forgot to mention that I've tested on Win7 with and without UAC On/Off.

 

[Kenneth Hobson]

Welcome to the forum!

Please start you own thread. When posting code, click the Go Advanced button in bottom right of a replay and then click the # icon to insert the code tags and paste code between them. Feel free to say that you think that something in this thread, copy this thread's url to that post, might help. And, paste your code. Attach a simple sample file to get the best help. Use the paperclip icon to attach files.

As we said in this thread, Sendkeys() can be used but it is to be avoided if possible. This thread shows both ways. In your thread, we can show you how to use one of the better API methods and make your code more efficient.

Of course to my mind, why not skip Notepad and use MSWord? That is the great thing about a Suite of applications with VBA or such. It gives you a common platform to interact with each through VBA.