I had a bit of a weird request recently and thought I’d share the solution in case anyone else ever needs to do the same thing.

The request was simple: Append data from a database, at the click of a button, to the open NotePad instance.

Windows APIs To The Rescue

Now, typically, this would not have been the approach I would have taken for altering a text file. There are much better approach for manipulating such files, such as:

but my client wanted things to be done this way.  So where there’s a will, there’s a way!

Here’s how I solved the problem.

#If VBA7 Then
    Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
    Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As String) As LongPtr
#Else
    Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
    Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long
#End If
Public Const WM_SETTEXT = &HC
Public Const WM_GETTEXT = &HD
Public Const WM_GETTEXTLENGTH = &HE


'***** Don't forget we can also easily work directly with the Text file via OpenFile,
'      FSO without needing to use APIs or show the user in the UI *****
'---------------------------------------------------------------------------------------
' Procedure : NotePad_AppendText
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Insert a string into the active Notepad instance
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
' References:
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInput            : (String)     String to insert into Notepad
' bOverWriteContent : (True/False) Should the string overwrite the existing Notepad content
'                       True  => Remove existing content
'                       False => Append to existent content
' bPrefixText       : (True/False) If bOverWriteContent = False, should the String prefix
'                     or suffix the existing content
'                       True  => Prefix existing content
'                       False => Suffix existing content
'
' Usage Examples:
' ~~~~~~~~~~~~~~~
' Append the string to the current content
' Call NotePad_AppendText("Some Text." & vbcr & "Another line of text.")
'
' Overwirte the current content with the string
' Call NotePad_AppendText("Some Text." & vbcr & "Another line of text.", True)
'
' Prefix the current content with the string (Append)
' Call NotePad_AppendText("Some Text." & vbcr & "Another line of text.", False, True)
'
' Append the string after to the current content (suffix)
' Call NotePad_AppendText("Some Text." & vbcr & "Another line of text.", False)
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2024-05-11              Initial Release
' 2         2024-05-15              Added 64-bit CC
' 3         2024-05-16              Added lHwnd loop when starting Notepad from scratch
'---------------------------------------------------------------------------------------
Public Function NotePad_AppendText(ByVal sInput As String, _
                                   Optional bOverWriteContent As Boolean = False, _
                                   Optional bPrefixText As Boolean = False)
    #If VBA7 Then
        Dim lHwnd             As LongPtr
        Dim lEditWindowHwnd   As LongPtr
        Dim lRetVal           As LongPtr
        Dim lContentLength    As LongPtr
    #Else
        Dim lHwnd             As Long
        Dim lEditWindowHwnd   As Long
        Dim lRetVal           As Long
        Dim lContentLength    As Long
    #End If
    Dim sContent              As String

    lHwnd = FindWindow("Notepad", vbNullString)
StartOver:
    If lHwnd <> 0 Then
        lEditWindowHwnd = FindWindowEx(lHwnd, 0&, "Edit", vbNullString)

        If bOverWriteContent Then
            sContent = sInput
        Else
            lContentLength = SendMessage(lEditWindowHwnd, WM_GETTEXTLENGTH, 0&, 0&)   'Get the current content length
            #If VBA7 Then
                sContent = Space(CLng(lContentLength) + 1)
            #Else
                sContent = Space(lContentLength + 1)  ' Allocate buffer
            #End If
            SendMessage lEditWindowHwnd, WM_GETTEXT, lContentLength + 1, ByVal sContent   'Retrieve the content
            sContent = Replace(sContent, Chr(0), "")    'Remove NULL Characters

            If bPrefixText Then
                sContent = sInput & sContent    'Prefix
            Else
                sContent = sContent & sInput    'Suffix
            End If
        End If
        DoEvents
        lRetVal = SendMessage(lEditWindowHwnd, WM_SETTEXT, 0, ByVal sContent)

        NotePad_AppendText = True
    Else
        'NotePad application not found, no Hwnd, so not currently running
        '   Maybe start it and loop the process???
        Shell "notepad.exe", vbNormalFocus
        DoEvents
        Do While lHwnd = 0
            lHwnd = FindWindow("Notepad", vbNullString)
            'Could add a sleep here possibly
            DoEvents
        Loop
        GoTo StartOver
    End If
End Function

This is application independent, so it will work in any VBA application (Access, Excel, Word, …).

    ' Windows 11
    If getOperatingSystem >= "11" ThenA
            lHwnd2 = FindWindowEx(lHwnd, 0, "NotepadTextBox", vbNullString)
            If lHwnd2 <> 0 Then
                lEditWindowHwnd= FindWindowEx(lHwnd2, 0, "RichEditD2DPT", vbNullString)
            End If
        '  Windows 10
        Else
         lEditWindowHwnd= FindWindowEx(lHwnd, 0, "Edit", vbNullString)
    End If