Can you mail merge in Word and bring the font colour from the data set through ?

Hi,

I am actually trying to do this in Publisher but I'm sure if it is possible in Word, it will work in Publisher too.

I have a set of data in Excel. One of the columns contains text that is formatted to different colours depending on the data e.g. if the data is 'A' then the font used is green. 'B' would be orange, etc.

Is it possible to mail merge this data with a Word document and bring the font colour from Excel through into Word so that when the actual data is displayed, it is displayed in the same colour in Word as it is in Excel?

Many thanks
theabbeyschoolNetwork ManagerAsked:
Who is Participating?
 
GrahamSkanRetiredCommented:
Here is a simple way to fill the column with colour names in VBA. Note  that in-between shades are not catered for.


Option Explicit

Sub GetColourName()
    Dim r As Integer
    With ActiveSheet
        For r = 2 To 4
            Select Case .Cells(r, 1).Font.Color
            Case 255
                .Cells(r, 2).Value = "Red"
            Case 0
                .Cells(r, 2).Value = "Black"
            Case 65280
                .Cells(r, 2).Value = "Green"
        Next r
    End With
End Sub

Open in new window

0
 
Rory ArchibaldCommented:
No. The recordset that Word is using does not contain any formatting information. You would have to automate the Excel application, open the workbook and copy the data.
0
 
GrahamSkanRetiredCommented:
If you couls arrange an extra column os the worksheet to have text that depends on the colour of the cell, then you could use IF fields to test the text and then display the field in the appropriate colour.

Here Field a  has the data that you wish to colour and Field b has the differentiating text. I have used the words "Red, "Green" and "Black"

There may be a way of using Excel formulae to fill the colour-indicating column. Rory would know. I am sure that it is possible in VBA

a          b
Tom     Green
Dick     Black
Harry   Red  

Here is a picture of the IF and the MERGEFIELDs in the Word document. (Use Alt + F9 to toggle the field display in the Word document)
Clip0002.jpg
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.