Solved: From Excel, create table of selected rows in Word

[srikanth_s]

I'm not sure if this belongs under the Excel Help or Word Help but thought I'd give it a shot.

I'm working on a macro that will generate a word doc, do some formatting, and insert user-selected rows from a spreadsheet.

My code often produces a clipboard-related run-time error, but I don't see any rhyme or reason in why and when this occurs. One pattern: the more time I leave between running instances of the macro, the less likely it appears that the error will occur.

The exact error is "Run-time error '4605': This method or property is not available because the Clipboard is empty or not valid." Debugger points to the line

 
msWord.Selection.PasteExcelTable False, True, False

I think this might have something to do with a) system resources / clipboard performance, b) error handling / managing multiple instances of word .... but I'm really not sure.

Full code:

Private Sub cmdSendToWord_Click()
    Dim i
        Dim msWord As Object
        Set msWord = CreateObject("Word.Application")
        msWord.Visible = True
        msWord.Documents.Add
    With msWord
        .Selection.TypeParagraph
        .Selection.InsertDateTime DateTimeFormat:="M/d/yyyy", InsertAsField:=False, _
            DateLanguage:=1033, CalendarType:=0, _
            InsertAsFullWidth:=False
        .Selection.TypeParagraph
        .Selection.TypeParagraph
        .Selection.TypeText Text:="Dear "
        .Selection.FormFields.Add Range:=.Selection.Range, Type:=70
        .Selection.TypeText Text:=":"
        .Selection.TypeParagraph
        .Selection.Font.Size = 1
        .Selection.TypeParagraph
        .Selection.Font.Size = 11
        .ActiveDocument.FormFields("Text1").Result = "RECIPIENT"
        .Selection.FormFields.Add Range:=.Selection.Range, Type:=70
        .ActiveDocument.FormFields("Text2").Result = "INTRODUCTORY TEXT"
    End With
    i = 4
    Do
        If Range("B" & i).Text = "" Then
            Exit Do
        End If
        
        If Range("e" & i).Text = True Then
            Range("B" & i, "D" & i).Copy
'            msWord.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:=wdInLine, DisplayAsIcon:=False
             msWord.Selection.PasteExcelTable False, True, False
'            msWord.Selection.TypeParagraph
        End If
        i = i + 1
    Loop
    msWord.Selection.TypeParagraph
    msWord.Selection.FormFields.Add Range:=msWord.Selection.Range, Type:=70
    msWord.ActiveDocument.FormFields("Text3").Result = "CONCLUDING TEXT"
    Application.CutCopyMode = False
    ActiveSheet.Shapes("Picture 125").Copy
    msWord.ActiveDocument.PageSetup.DifferentFirstPageHeaderFooter = True
    msWord.ActiveWindow.ActivePane.View.SeekView = 9
    msWord.ActiveWindow.Selection.Paste
    msWord.ActiveWindow.ActivePane.View.SeekView = 0
    msWord.ActiveDocument.FormFields.Shaded = Not msWord.ActiveDocument.FormFields.Shaded
    msWord.ActiveDocument.Protect Type:=2, NoReset:=True
End Sub

Any guidance anyone can offer would be great. Thanks!

[Dave]

Trial adding the following after each paste to clear the clipboard. HTH. Dave
[VBA]
Application.CutCopyMode = False
[/VBA]

[mdmackillop]

Have you tried a mail-merge approach? I think it would be simpler.

[srikanth_s]

Dave -- I added the cutcopymode line after paste, but alas, to no avail.

MDMackillop - I will take a look at the mail merge approach, but if anyone has an idea of how to address the specific error I'm facing, I would love to hear it.

Thanks!

Srikanth

[srikanth_s]

I've used mail merge for the straightforward purpose of creating labels, envelopes, etc. I'm failing to see the similarity between that kind of project and what I'm doing here. Can anyone elaborate on that?

[mdmackillop]

Can you post sample data so we can try your code.

[srikanth_s]

Here goes ... Thanks in advance for any help you can provide, and sorry for any hard-to-read / poorly written code!

[srikanth_s]

Update --

I appear to be getting the run-time error 4605 (clipboard empty or not valid) far more often -- if not exclusively -- when running this code in Excel 2003 installed on a Citrix (remote) session.

On my own machine running Excel 2007, I don't seem to get this error at all.

Unfortunately, I haven't been able to wrangle anyone running Excel 2003 on their own machine to test this for me. I'd love to see how it works for folks running 2003 or other older versions.

[mdmackillop]

On 2007 I can run the program multiple times, unless I open Word to view the file. In this case I get Error 462; The Remote Server Machine does not exist or is unavailable.

[srikanth_s]

Hmmmm. Appreciate your taking the time to run it. Does debugger point to a particular line of code?

I'm feeling resigned to the fact that my users won't be able to run this in a citrix session because of the way citrix manages clipboards--appears to interfere with code that does copying and pasting.

[mdmackillop]

It doesn't like ActiveDocument
[VBA]
.ActiveDocument.PageSetup.RightMargin = InchesToPoints(1)

[/VBA]

[srikanth_s]

Aha. Looks like I need a . before the Inchestopoints methods because of the way I'm invoking Word (as object).

I'll mark this as solved but if anyone is comes across run-time error 4605 related to the clipboard being empty / not valid I'd love to hear it.

Oh. Um -- how do I mark this as solved?