Excel to Word using Bookmarks

[hogg]

Hi all!

I've spent a lot of today working out how to copy selected cells from excel into word. I found several solutions but the best one is a piece of code supplied on the KB called "Push Excel Named Range Values to Bookmarks in Word" by Ken Puls:

[VBA]Option Explicit

Sub BCMerge()
Dim pappWord As Object
Dim docWord As Object
Dim wb As Excel.Workbook
Dim xlName As Excel.Name
Dim TodayDate As String
Dim Path As String

Set wb = ActiveWorkbook
TodayDate = Format(Date, "mmmm d, yyyy")
Path = wb.Path & "\pushmerge.dot"

On Error GoTo ErrorHandler

'Create a new Word Session
Set pappWord = CreateObject("Word.Application")

On Error GoTo ErrorHandler

'Open document in word
Set docWord = pappWord.Documents.Open("c:\report.doc")

'Loop through names in the activeworkbook
For Each xlName In wb.Names
'if xlName's name is existing in document then put the value in place of the bookmark
If docWord.Bookmarks.Exists(xlName.Name) Then
docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
End If
Next xlName

'Activate word and display document
With pappWord
.Visible = True
.ActiveWindow.WindowState = 0
.Activate
End With

'Release the Word object to save memory and exit macro
ErrorExit:
Set pappWord = Nothing
Exit Sub

'Error Handling routine
ErrorHandler:
If Err Then
MsgBox "Error No: " & Err.Number & "; There is a problem"
If Not pappWord Is Nothing Then
pappWord.Quit False
End If
Resume ErrorExit
End If
End Sub[/VBA]

Which is great! The only problem I have is the formatting isn't maintained. Is it possible to modify this code to maintain the formatting? I've tried but failed with my limited VBA knowledge!

Thanks for any help!

[Kenneth Hobs]

Use Format to format your output. Not all NumberFormat strings evaluate properly in Format but many will.

[VBA]Sub MyNames()
Dim xlName As Name
For Each xlName In ActiveWorkbook.Names
If Range(xlName).Count = 1 Then
Debug.Print Range(xlName).Value, Format(Range(xlName).Value, Range(xlName).NumberFormat)
End If
Next xlName
End Sub[/VBA]

[hogg]

Sorry Kenneth I don't qiute understand how to implement this? Will the code you've suggeseted keep the formatting present in excel - for example a yellow background or the cell boarder? What I'm trying to imitate is the paste special command when I bring the data into word:

[VBA]Selection.PasteSpecial Link:=False, DataType:=wdPasteRTF[/VBA]

I've seen a code that achieves this but each bookmark has to be progrmmed seperatly rather than the more global approach this code takes - and I have a lot of bookmarks to programme!


Thanks for your help.

[Kenneth Hobs]

Without testing:

Replace:
[VBA]docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)[/VBA]

With:
[VBA]Excel.Range(xlName).Copy docWord.Bookmarks(xlName.Name).Range [/VBA]

[hogg]

Thanks, but it still won't play nice! I'm getting Error No:1004 when I substitute the code as recommended. Any idea where I might be going wrong?

[Kenneth Hobs]

You may want to verify that a non network folder exists before attempting such.
e.g.
[VBA]Dim s As String
s = "c:\"
If Dir(s, vbDirectory) = "" Then
MsgBox s & " does not exist."
Else: MsgBox s & " does exist."
End If[/VBA]

Otherwise, post your code. Obviously, we can't test for specific network folders.

[hogg]

C:\ does exist (I'm assuming thats a good thing!)

I'm using the code as it is in my first post. Everything works except I loose the formatting. I guess the simpler answer would be to embed my bookmarks in word in the format required - I was just hoping to control it all from excel!

[lucas]

Since you are using a template to create the document, why not just format the bookmarks in the template?

[Kenneth Hobs]

Trim your doc file and post for testing if you like.