Keep Excel text formatting when transferring data to Word with Excel VBA

BURTPRI

New Member
Joined
May 8, 2020
Messages
16
Office Version
  1. 2010
Platform
  1. Windows
Hi Guys!

I'm using the following Excel VBA code to generate a WordDoc and then transfer text from my ExcelDoc to the WordDoc:

VBA Code:
Sub Generate_WordDoc()

Dim WordDoc As Object
Dim FileName, TagName, TagValue As String
Dim CustRow As Long
Dim WordContent As Word.Range

With Sheet1
Set wordapp = CreateObject("Word.Application")
      wordapp.Visible = True   
    Set WordDoc = wordapp.Documents.Open(FileName:="C:\File location\Name of file.docx", ReadOnly:=False)
        For CustRow = 2 To 4
        TagName = .Cells(CustRow, 1).Value
        TagValue = .Cells(CustRow, 2).Value
        With WordDoc.Content.Find
        .Text = TagName
        .Replacement.Text = TagValue
        .Wrap = wdFindContinue
        .Execute Replace:=wdReplaceAll
        End With
        Next CustRow     
 
End With
End Sub

It would work perfectly if wasn't by the fact that my WordDoc doesn't keep important formatting like paragraphs and bold text, as you can see in the images below:

1589732357001.png
-> What I have in my WordDoc:
1589732399794.png


Please note that in my WordDoc template I have only the start text (it's not merge fields, it's only text itself):
Company: <<Company>>
Address: <<Address>>
Phone: <<Phone number>>

I guess I need a "paste special" code or something like that but I can't find it...

Also, and if possible, I would like to not show in my WordDoc the text "Company: " if there's no Value on cell B2 of my ExcelDoc.

Hopefully someone will be able and kind to help me with this challenge.

If you read this far, thank you very much!
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,536
Given that your own code has:
Dim WordContent As Word.Range
that implies you have a reference set to Word. Without it, you would get the same error.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,314
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top