Copying contents of text box in Excel to a Word document with formatting

[mcomp72]

With VBA code, I am trying to copy the contents of a text box that is in an Excel worksheet, and paste the contents into a Word document. I have that part working -- however, it is not pasting the formatting of the text along with the text itself. For instance, some of the text is in bold, some is underlined, the font size is not always the same, etc.

Here's what the text box looks like:

When pasted into Word, here is what it looks like:

Here's the code:

Dim TB As Shape

Set TB = ThisWorkbook.Sheets("Sheet1").Shapes("TextBox1")

'This will copy the text in the text box and paste it to the Word document.
'***Currently, it is not pasting the formatting of the text, only the text itself.
Dim MyData As New DataObject
MyData.SetText TB.TextFrame.Characters.Text
MyData.PutInClipboard

objDoc.Range(objDoc.Range.End - 1, objDoc.Range.End).PasteSpecial

'Make the Word document visible.
objWord.Visible = True

Set TB = Nothing

Does anyone have any idea how I would get it to paste the text with the formatting?

 

[DanteAmor]

MyData.SetText TB.TextFrame.Characters.Text
MyData.PutInClipboard

With that instruction you are not copying the formatted text, you are only passing the plain text.

Replace your code with this:

  Dim TB As Shape
  Set TB = ThisWorkbook.Sheets("Sheet1").Shapes("TextBox1")
  TB.TextFrame2.TextRange.Copy

  objDoc.Range(objDoc.Range.End - 1, objDoc.Range.End).PasteSpecial
  objWord.Visible = True

  Set TB = Nothing

 

[mcomp72]

With that instruction you are not copying the formatted text, you are only passing the plain text.

Replace your code with this:

  Dim TB As Shape
  Set TB = ThisWorkbook.Sheets("Sheet1").Shapes("TextBox1")
  TB.TextFrame2.TextRange.Copy

  objDoc.Range(objDoc.Range.End - 1, objDoc.Range.End).PasteSpecial
  objWord.Visible = True

  Set TB = Nothing

Thannks! Definitely an improvement! Only problem now is, the blank lines that were in between the sentences in the text box are gone once pasted to Word.

Here's what ended up in Word:

Any idea how I could get blank lines to appear?

 

[DanteAmor]

Try:

  Dim TB As Shape
  Set TB = ThisWorkbook.Sheets("Sheet1").Shapes("TextBox1")
  TB.TextFrame2.TextRange.Copy

  objWord.Visible = True
  objDoc.Range(objDoc.Range.End - 1, objDoc.Range.End).PasteAndFormat wdFormatSurroundingFormattingWithEmphasis
  'or objDoc.Range(objDoc.Range.End - 1, objDoc.Range.End).PasteAndFormat 20

 

[mcomp72]

I got a compile error, saying variable not defined on the line with "wdFormatSurroundingFormattingWithEmphasis".

I believe it thinks that is a variable name I created.

I wonder if this might be because I did not use early binding in declaring the Word objects? I used late binding, because this file is for someone else, and I was hoping to avoid having to explain to them how to turn on the Reference for Microsoft Word under Tools > References in the VBE.

Here's how I declared the Word objects:

Dim objWord As Object
Dim objDoc As Object

Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.documents.Add

Based on this, do you know if wdFormatSurroundingFormattingWithEmphasis should work? Or is that only going to work with early binding?

 

[mcomp72]

Once I went into Tools > References and checked the box for Microsoft Word 16.0 Object Library, the variable not defined error went away. So yep, that was the cause of that.

However, the information pasted to Word still did not have the blank lines in it.

I also tried replacing wdFormatSurroundingFormattingWithEmphasis with wdFormatOriginalFormatting, but that didn't solve it.

 

[DanteAmor]

I got a compile error, saying variable not defined on the line with "wdFormatSurroundingFormattingWithEmphasis".

Use:

objDoc.Range(objDoc.Range.End - 1, objDoc.Range.End).PasteAndFormat 20

That format worked for me.

However, the information pasted to Word still did not have the blank lines in it.

Review this:

WdRecoveryType enumeration (Word)

Office VBA reference topic

learn.microsoft.com

 

[DanteAmor]

However, the information pasted to Word still did not have the blank lines in it.

In Textbox separate the lines with Shift + Enter

 

[mcomp72]

That solved it! Thanks!!