Excel 2003 VBA, The following do NOT work: SendKeys to Word and Notepad, Clear Word Clipboard, and Quit Notepad. Unwanted Prompts.

[JoeDBugger]

The following desired functionality is not working or I've been unable to find an implementation in Excel 2003 VBA:

1. Select All and Copy using SendKeys to Word (does NOT work). However, the DEBUG implementation provided instructs the user to do the Select All and Copy manually. This is to prove that the Paste into Notepad works, which it does.

2. Save using SendKeys to Notepad (does NOT work)

3. Clear Word Clipboard by launching Word again (does NOT work)

4. Quit Notepad (no implementation found)

The following quoted prompts are unwanted:

1. "You placed a large amount of text on the Clipboard. Do you want this text to be available to other applications after you quit Word? (Yes/No/Cancel)" I clicked the "No" button.

2. "The text in the <FILE name>.txt file has changed. Do you want to save the changes? (Yes/No/Cancel)" I clicked the "Yes" button.

3. "<FILE name>.txt The file contains characters in Unicode format which will be lost if you save this file as an ANSI encoded text file. To keep the Unicode information, click the Cancel below and then select one of the Unicode options from the Encoding drop down list. Continue? (OK/Cancel)" I clicked the "OK" button.

when:

1. Word is programmatically Quit
2. Notepad is manually Quit
3. Notepad is manually Quit

respectively.

Option Explicit
Sub CopyWordDocumentPasteInPlainTextFile()
 
    Dim psWordDocumentFileName As String
    Dim psWordDocumentFullFileName As String
    Dim psWordDocumentFullPathFileName As String
    Dim psWorkbookCurrentWorkingDirectory As String
    Dim psNotepadFullFileName As String
    Dim psNotepadFullPathFileName As String
 
    Dim vScriptingFile As Variant
    Dim vTextFile As Variant
 
    Dim lFileNumber As Long
 
    Dim osWordApplication As Word.Application
    'Dim osWordApplicationToClearClipboard As Word.Application
    Dim osWordDocument As Word.Document
 
    Dim bWait As Boolean
 
    Dim lReturnValue As Long
 
    psWorkbookCurrentWorkingDirectory = "Name of Current Working Directory goes here"
 
    psWordDocumentFileName = "File Name of Word Document goes here"    '  Exclude file extension
    psWordDocumentFullFileName = psWordDocumentFileName & ".doc"
    psWordDocumentFullPathFileName = psWorkbookCurrentWorkingDirectory & "\" & psWordDocumentFullFileName
 
    psNotepadFullFileName = psWordDocumentFileName & ".txt"
    psNotepadFullPathFileName = psWorkbookCurrentWorkingDirectory & "\" & psNotepadFullFileName
 
    Set vScriptingFile = CreateObject("Scripting.FileSystemObject")
    'Set vTextFile = vScriptingFile.CreateTextFile(psNotepadFullFileName, True)
    Set vTextFile = vScriptingFile.CreateTextFile(psNotepadFullPathFileName, True)
 
    lFileNumber = FreeFile    '  Next free file number
 
    bWait = True
 
    On Error Resume Next
    Set osWordApplication = GetObject(, "Word.Application")
 
    If Err.Number <> 0 Then
        Set osWordApplication = CreateObject("Word.Application")
    End If
    On Error GoTo 0
 
    Set osWordDocument = osWordApplication.Documents.Open(psWordDocumentFullPathFileName)
    osWordApplication.Visible = True
 
    SendKeys "^A", bWait    '  Does NOT Select All!!
    SendKeys "^C", bWait    '  Does NOT Copy!!
    MsgBox ("DEBUG (CopyWordDocumentPasteInPlainTextFile):  Click OK.  " & _
            "At the breakpoint, go to the Word Document, notice if all the text has " & _
            "been selected, and Ctrl/A, Ctrl/C.  Next, Run (F5) the Macro.")
 
    lReturnValue = Shell("C:\Windows\Notepad.exe " & psNotepadFullFileName, vbNormalFocus)    '  Set Break here
 
    '    If I just run (normal operation) the Macro after the break above, this paste works!
    '    HOWEVER, if I step through the Macro after the break above, this paste fails!
    SendKeys "^V", bWait    '  This worked for pasting the MANUALLY selected and copied
                            '  Word Document text into Notepad
 
    SendKeys "^S", bWait    '  Does NOT save the Notepad file after doing the paste above!!
 
    '    One solution to clear the Word Clipboard I came across on the Internet was to
    '    launch Word again, so I don't get the prompt below.
    '
    '    FAILS to clear the Clipboard when active.
    'Set osWordApplicationToClearClipboard = CreateObject("Word.Application")
 
    osWordDocument.Close
 
    osWordApplication.Quit    '  Prompt:  You placed a large amount of text on the _
                                 Clipboard.  Do you want this text to be available to _
                                 other applications after you quit Word?  (Yes/No/Cancel) _
                                 I clicked the "No" button.
 
                              '  I do NOT want the prompt above to come up.
 
    'osWordApplicationToClearClipboard.Quit
 
    Set osWordApplication = Nothing
    Set osWordDocument = Nothing
 
    Close #lFileNumber    '  Did NOT save AND physically close the file
 
                          '  Prompt:  The text in the D617Z013-01 Rev B4.txt file has _
                             changed. _
                             Do you want to save the changes?  (Yes/No/Cancel) _
                             I clicked the "Yes" button.
 
                          '  I do NOT want the prompt above to come up.
 
                          '  Prompt:  D617Z013-01 Rev B4.txt _
                                      The file contains characters in Unicode format which _
                                      will be lost if you save this file as an ANSI encoded _
                                      text file.  To keep the Unicode information, click _
                                      the Cancel below and then select one of the Unicode _
                                      options from the Encoding drop down list.  Continue? _
                                      I clicked the "OK" button.
 
                          '  I do NOT want the prompt above to come up.
 
End Sub    '  End Sub CopyWordDocumentPasteInPlainTextFile

Thank you for your help. I have been searching the Intranet and Internet for an implementation to resolve each of these issues. However, I haven't found them. I thought I found an implementation to clear the Word Clipboard, but it doesn't work. I suspect that a new copy of Word is not being launched.