How to disable screen updating for another application (notepad)

Question

I have written a macro in vba, which opens a text file with notepad, selects all txt and copies it to Excel. I have to process about 100 files daily in this way and I want to spare the flashing images that I observe. The code is working but the problem is that the command

Application.Screenupdating = False

Is not working with the notepad application. I can only use the normal focus, otherwise the code is not working. How can I execute the code below without observing that the notepad file is opened and processed?

My code is:

Sub GetTextFile()
Application.ScreenUpdating = False
Dim MyPath As String
Dim MyFile As String

MyPath = "C:\Users\bgyona02\Desktop\OLAttachments\"

MyFile = Dir(MyPath & "*.txt", vbNormal)    

Do While Len(MyFile) > 0
  MyFile = Dir
Loop

Debug.Print GetTextFileContent(" C:\Users\bgyona02\Desktop\OLAttachments\" & MyFile)
    'MyFile = Shell("C:\WINDOWS\notepad.exe` C:\Users\bgyona02\Desktop\OLAttachments\" & MyFile, vbNormalFocus)
    'SendKeys "^a", True  '^A selects everything already in the pdf file.
    'SendKeys "^c", True
    'SendKeys "%fx", True
End Sub

I could not find any working solution about this.

Answer 1

There's a quick answer how to run Notepad but hide the window and that it is to use vbHide instead of vbNormalFocus in your Shell command:

Dim strCmd = "C:\WINDOWS\notepad.exe C:\Users\bgyona02\Desktop\OLAttachments\" & LatestFile
MyFile = Shell(strCmd, vbHide)

But I very much doubt that SendKeys would then work on an invisible window....

So, this is not an answer to the question but have you considered using the FileSystemObject and simply read the file without actually opening Notepad.exe ?

Option Explicit

Const FOR_READING = 1

Sub LoadTextFile()
    Dim varTxtContent As Variant
    Dim intLine As Integer

    'Debug.Print GetTextFileContent("D:\temp.txt")

    varTxtContent = Split(GetTextFileContent("D:\temp.txt"), vbCr, -1, vbBinaryCompare)
    For intLine = 0 To UBound(varTxtContent) - 1
        ThisWorkbook.Worksheets("Sheet1").Range("B" & intLine + 1).Value = varTxtContent(intLine)
    Next intLine


End Sub

Function GetTextFileContent(strPath As String) As String
    Dim strContent As String
    Dim objFso As Object
    Dim objFile As Object
    Dim objStream As Object

    strContent = ""
    On Error GoTo CleanUp:

    Set objFso = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFso.GetFile(strPath)
    Set objStream = objFile.OpenAsTextStream(FOR_READING, 0)

    With objStream
        strContent = .ReadAll
        .Close
    End With

CleanUp:
    Set objStream = Nothing
    Set objFile = Nothing
    Set objFso = Nothing
    GetTextFileContent = strContent

End Function

The code will work with accented characters e.g. my test text file is:

â, î or ô
foo
bar foo
baz bar foo

As far as I know, Application.ScreenUpdating will only apply to your Excel session and not other programs running in Windows. So, to actually stop those windows appearing - but still be able to read the content of the window - you would have to do something reasonably complex with the Windows API. Using FileSystemObject is a lot, lot simpler.

HTH.

Answer 2

I finally found a very elegant solution that can handle external applications. All the credit should go for Robert Schutt for writing this masterpiece code. This makes the notepad window 1 pixel, so no flashing images are observed. It looks rather complicated for me but it saved my day:

Option Explicit

Private Declare Function MoveWindow Lib "user32" (ByVal hwnd As Long, ByVal x As Long, ByVal y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal bRepaint As Long) As Long

Public Const GW_HWNDNEXT As Long = 2
Public Declare Function GetParent Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function GetWindow Lib "user32" (ByVal hwnd As Long, ByVal wCmd As Long) As Long
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, lpdwprocessid As Long) As Long

Function ProcIDFromWnd(ByVal hwnd As Long) As Long
   Dim idProc As Long

   ' Get PID for this HWnd
   GetWindowThreadProcessId hwnd, idProc
   ProcIDFromWnd = idProc
End Function

Function GetWinHandle(hInstance As Long) As Long
   Dim tempHwnd As Long

   ' Grab the first window handle that Windows finds:
   tempHwnd = FindWindow(vbNullString, vbNullString)

   ' Loop until you find a match or there are no more window handles:
   Do Until tempHwnd = 0
      ' Check if no parent for this window
      If GetParent(tempHwnd) = 0 Then
         ' Check for PID match
         If hInstance = ProcIDFromWnd(tempHwnd) Then
            ' Return found handle
            GetWinHandle = tempHwnd
            ' Exit search loop
            Exit Do
         End If
      End If

      ' Get the next window handle
      tempHwnd = GetWindow(tempHwnd, GW_HWNDNEXT)
   Loop
End Function

Sub MinimizeNotepad()
    Dim retval As Long, np_retval As Long
    np_retval = Shell("C:\notepad.exe", vbNormalFocus)
    retval = MoveWindow(GetWinHandle(np_retval), 1, 1, 1, 1, 1) ' Application.hwnd
End Sub