PDA

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



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!

Dave
10-16-2009, 06:42 PM
Trial adding the following after each paste to clear the clipboard. HTH. Dave

Application.CutCopyMode = False

mdmackillop
10-18-2009, 02:50 AM
Have you tried a mail-merge approach? I think it would be simpler.

srikanth_s
10-18-2009, 07:23 PM
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
10-19-2009, 02:02 PM
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
10-19-2009, 02:31 PM
Can you post sample data so we can try your code.

srikanth_s
10-19-2009, 03:44 PM
Here goes ... Thanks in advance for any help you can provide, and sorry for any hard-to-read / poorly written code!

srikanth_s
10-20-2009, 01:21 PM
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
10-20-2009, 02:37 PM
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
10-20-2009, 02:42 PM
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
10-20-2009, 03:44 PM
It doesn't like ActiveDocument

.ActiveDocument.PageSetup.RightMargin = InchesToPoints(1)

srikanth_s
10-20-2009, 06:51 PM
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?