Copy from an Embedded Word Document to a Single Cell

[richh]

I have two embedded Word documents in one of my sheets that allow users to edit I can copy the contents and paste them in a sheet, but the carriage returns break the contents up into multiple cells. As my algorithm is intended to house an entire report on a single row, this is causing an issue.

For instance, the embedded Word document contains the following:

this is line 1
this is line 2

I want the datasheet to copy the object's values into a single cell, such that it would appear as:

this is line1this is line 2

https://answers.microsoft.com/en-us...a/ee2b3cce-3171-4b58-9b08-75606ac6e5be?auth=1

The code that I have implemented is from the page above and was slightly adapted to my needs:

'ws is used to loop through each worksheet; ar is used for my datasheet

x = 98 'column value
For Each Oo In ws.OLEObjects
If InStr(1, Oo.progID, "Word.Document", vbTextCompare) > 0 Then
'Open the embedded document
Oo.Verb xlVerbPrimary

Set wDoc = Object

'Copy the contents to cell A1
wDoc.Content.Copy
ar.Cells(rowF, x).PasteSpecial xlPasteValues
x = x + 1

'Select any cell to close the document
Range("A1").Select
'Done
End If
Next

 

[Domenic]

Instead of doing a copy/pastespecial, assign the Word contents to a string variable, replace vbCr with either vbCrLf to get line breaks within the cell itself, or replace it with nothing ("") or space (" "). Then write the string to your cell.

[FONT=Courier New][COLOR=green]'Copy the contents to cell A1[/COLOR]
strContent = wDoc.Content
strContent = Replace(strContent, vbCr, vbCrLf) [COLOR=#008000]'or replace vbCrLf with "" or " "[/COLOR]
ar.Cells(rowF, x).Value = strContent
x = x + 1[/FONT]

Also, you can add the following declaration...

Dim strContent As String

Hope this helps!

 

[richh]

Worked like a charm! Thank you!!!