Copy cells from Excel and Paste Special in Word (Clipboard problems)

[Jubinell]

Hi guys,

In one part of my subroutine, I need to copy a range of cells in Excel and then paste it in Word as unformatted text (so that a table is not created). The cells should replace a field that I created called "<log-in data>"

I have made use of the clipboard as follows:

[vba]
Sub CopyCells()

Dim myRange as Range
Dim wrdApp as Word.Application
Dim wrdDoc as Word.Document

'....

myRange.Copy

With wrdApp.Selection
With .Find
.Text = "<log-in data>"
.Replacement.Text = ""
.Execute
End With
With .Selection
.PasteSpecial DataType:=wdPasteText
End With
End With

End Sub
[/vba]

However, this part of the subroutine has been giving me problems because from time to time I would get "Error: Clipboard cannot be emptied." I figured out that the error is coming from my usage of Remote Desktop while the macro is being run, but there's nothing I can do about it. I must have Remote Desktop open at this time.

My question is, is there a way rewrite this task without using the Clipboard?


Any help is greatly appreciated!

[TonyJollans]

In general you could have a problem, but in this case you could build up the unformatted text string into a variable (or perhaps one per row of cells) ... pseudocode ...

LineofText = cell1.text & vbtab & cell2.text & ...

and then insert it in the Word document ... more pseudocode ...

Range.InsertAfter LineOftext

[Jubinell]

Thanks Tony for the suggestion. My range of cells span in several rows and columns. I guess I can run a double loop for this. My only issue would be time. If I have 200 rows and 5 columns, will this take a while and strain resources?

Basically, I'm trying to copy a table from Excel to Word, but strip off the table format and just keep the tabs. Can I do something cheating, such as insert the table, then select the table and do table-to-text (all in VBA)?

[TonyJollans]

Without copy and paste you have to remove the table format (in Excel) all by yourself. Text to Columns will do some of it but there isn't any equivalent I can think of immediately for rows.