Copy Excel table to Word Bookmark - issue with intersecting bookmarks

[Jon von der Heyden]

Hi All

Using Excel, but think I need help more related to Word.

I am using a class in Excel to manage report creation in a Word document (template - for want of a better word).

Public Function RangeToBookmark(ByVal rngTarget As Excel.Range, ByVal strBookmark As String) As Boolean    Dim blnContinue     As Boolean
    Dim wrdDocument     As Object
    Dim wrdRange        As Object
    Dim lngRow          As Long
    Dim lngColumn       As Long
    
    'Exit if there is no document to edit (result = False)
    blnContinue = Not Me.WordDocument Is Nothing
    If Not blnContinue Then Exit Function


    Set wrdDocument = Me.WordDocument
    
    'Exit if the bookmark cannot be found (result = False)
    On Error Resume Next
        Set wrdRange = wrdDocument.Bookmarks(strBookmark).Range
    On Error GoTo 0
    blnContinue = Not wrdRange Is Nothing
    If Not blnContinue Then Exit Function
    
    'Attempt to paste range to the bookmark
    On Error Resume Next
        Call wrdRange.Tables(1).Delete
        Call rngTarget.Copy
        Call wrdRange.PasteExcelTable(LinkedToExcel:=False, WordFormatting:=False, RTF:=False)
        With wrdRange.Tables(1).Rows
            .HeightRule = 1
            .Height = 1.25
        End With
        Call wrdDocument.Bookmarks.Add(Name:=strBookmark, Range:=wrdRange)
        Application.CutCopyMode = False
    On Error GoTo 0
    
    'Result = True if all steps successful
    RangeToBookmark = (Err.Number = 0)
    
    Set wrdRange = Nothing
    Set wrdDocument = Nothing
End Function

This one function words great in most circumstances. However:

Say I have two bookmarks called:
TBL001
and
TBL002

with one beneath the other (i.e. a single carriage return).

It appears that the Bookmark Ranges are deemed intersecting. What I mean is that when I paste the first Excel table to TBL001 it is fine. However when we paste the second table to TBL002 then because the Bookmark Ranges intersect the method Call wrdRange.Tables(1).Delete will delete the first pasted table before pasting the second table. This means I have a single table output, not two.

Hope this makes sense. The immediate work around is to have two carriage returns between the bookmarks. But the effect of this is that the report doesn't look as good.

Any suggestions on how I can handle tables in two Bookmark Ranges where the Ranges sit one beneath the other?

 

[Macropod]

The simple fix might be to have an empty paragraph between the two bookmarks. Note that Word tables must be separated by at least an empty paragraph; otherwise you'll end up with a single table even if you do manage to get the output from both.

 

[Jon von der Heyden]

Hi Paul

Thank you for responding.

I think my assumption of the ranges being contiguous is not correct.

So for example, if the code element is Word content:

Bookmark: TBL001
{paragraph}
Bookmark: TBL002

There is a break between the two bookmarks (I can see the paragraph icon).

I notice that the first pasted table is overwritten with the second table. Both bookmarks are present, but they occupy the exact same range.

In my code, the main elements are:

Get the range from the bookmark

Set wrdRange = wrdDocument.Bookmarks(strBookmark).Range

Delete the table that is currently positioned in the bookmark range

Call wrdRange.Tables(1).Delete

Paste the table from Excel to the bookmark range

Call rngTarget.Copy
Call wrdRange.PasteExcelTable(LinkedToExcel:=False, WordFormatting:=False, RTF:=False)

Reapply the bookmark

Call wrdDocument.Bookmarks.Add(Name:=strBookmark, Range:=wrdRange)

Is there any obvious reasons why the bookmarks will overlay each other as a result of this routine?

 

[Norie]

Why are you deleting the existing table in the bookmark range?

Have you checked where wrdRng points to after you do that?

 

[Jon von der Heyden]

Hi Norie

It's driven by Excel and the tables are elements of financial statements. Both structure and contents are likely to change.

Can you elaborate on 'points'? Can you recommend how I check the range points (could spare me having to look it up).

 

[Norie]

All I mean is where/what in the document wrdRange 'points' to.

 

[Macropod]

'Points' as in where it is pointing to. In this case, it's determined by whatever is being passed to the Function as the strBookmark argument. You don't just need a paragraph break between the two bookmarks - you need at least an empty paragraph between them. Try:

Public Function RangeToBookmark(ByVal rngTarget As Excel.Range, ByVal strBookmark As String) As Boolean
    Dim wrdRange        As Object
    RangeToBookmark = False
    If Me.WordDocument Is Nothing Then Exit Function
    With Me.WordDocument
        If .Bookmarks(strBookmark).Exists Then
            Set wrdRange = .Bookmarks(strBookmark).Range
            With wrdRange
                .Text = vbCr
                rngTarget.Copy
                .PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
                Application.CutCopyMode = False
                With .Tables(1).Rows
                    .HeightRule = 1
                    .Height = 1.25
                End With
            End With
            .Bookmarks.Add Name:=strBookmark, Range:=wrdRange
            RangeToBookmark = True: Set wrdRange = Nothing
        End If
    End With
End Function

 

[Jon von der Heyden]

Thank you Paul - I'm going to give that a go.

Could you clarify a few assumptions I am making?

1. You are not deleting the table. Is that not needed now because you let the Text property to vbCr?

2. vbCr (carriage return) is what is actually determining that there is a paragraph between tables?

Thanks for your help.

 

[NdNoviceHlp]

Maybe gmaxey's contribution here will help? Dave
http://www.mrexcel.com/forum/excel-questions/916620-issue-bookmark-2.html

 

[Macropod]

Could you clarify a few assumptions I am making?

1. You are not deleting the table. Is that not needed now because you let the Text property to vbCr?

2. vbCr (carriage return) is what is actually determining that there is a paragraph between tables?

Replacing the bookmark range's content with just a paragraph break, which is what .Text = vbCr does, both deletes the table and inserts and empty paragraph for the new table to be pasted over. Without that empty paragraph, the range collapses to whatever was following it, in this case the empty paragraph between the two tables it seems your destination document usually already has - then that gets pasted over and the two tables merge. Calling the function a second time would then replace the merged table - which I suspect is what you were seeing. Inserting the paragraph break is also less error-prone than trying to explicitly delete a table that might not be there.