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?

 

[Norie]

Jon

Have you considered using a template for this?

If you used a template then there would be no need to delete existing tables, you could just 'drop' the required tables in their required place.

 

[Macropod]

Indeed, with a template, you could have the tables pre-linked to the data. At most, you then might want to update & unlink them when creating a new document.

 

[Jon von der Heyden]

Hi Paul and Norie

I would love to use a template. We're creating stat packs on the fly, which includes substituting text tokens in paragraphs too. Also, the client needs to be able to create new bookmarks in the document, either for tables or text. Alas, there is a lot more to this project than just the tables and I am also tasked with facilitating this element.

Paul I have tried your code, and variants thereof. There was a small syntactical error checking whether or not a bookmark exists but I uncovered that quickly (and I am very grateful that you exposed that method to me).

Not sure how it behaves for you but vbCr does not flush out the table. It seems to add a new row to the existing table and then attempts to position the pasted table within the first cell of that new row, hence I end up with 2 tables but one is nested and looks very odd. I also tried to redimension the bookmark so that it includes a blank row below (and above) but it doesn't seem to affect the behaviour.

At this point I'm resolved to having two carriage returns between tables, unless you have any other clever tricks up your sleeve?

 

[Macropod]

I don't see new rows or nested tables resulting from the code. That said, you could 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.Exists(strBookmark) Then
        Set wrdRange = .Bookmarks(strBookmark).Range
        With wrdRange
            If .Tables.Count > 0 Then
                .Tables(1).Delete
                .Text = vbCr
            End If
            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]

Hi Paul

Thanks for your persistence. I'm afraid I get the same behaviour as before. I need to have two paragraphs between bookmarks. If I don't then I end up with one table and two bookmarks that evaluate to the same range. In other words, only the second table appears because it overwrites the first, even though there is definitely a paragraph between the bookmarks before the routine is kicked-off.

I have also looked at various document options because I suspect there is something odd with the settings. But if it is, it must be one of my default settings because I get the same behaviour even if I try a new Word document. I think this may describe it:

Open a new Word document.
Add 2 carriage returns.
On the top line add a bookmark called Test1
On the lowest line add a bookmark called Test2

In Excel create two tables with the same names.

When I run the code the first time the results appear OK. However, when I run it subsequently I end up with only Test2 table. It seems as though the bookmarks merge.

This works:
If I add a space character (32) to the blank line between tables, then it works fine, every time. I suspect this is what your addition of vbCr is intended on doing. But in my case the table paste to wrdRange overwrites that inputted character so I end up where I started. So to make this works it seems perhaps I should just manually add a space character on each blank line between tables. I'm not sure if there is a VBA workaround, but I think this is a reasonable manual task.

On the other hand, if you know how to append a vbCr and space to the end of the pasted table, and then add the bookmark to include both table, vbCr and space, then I think this should probably work. I've started looking on MSDN about how to dimension the range, but so far haven't figured it out.

Thanks again for all of your inputs.
Jon