srikanth_s
10-16-2009, 05:35 PM
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!
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!