Excel VBA - Exporting named tables in excel to words using bookmarks

[kohminski]

Hi,

I am very new to Excel VBA, I am hoping I can set a macro to export Excel tables to Word.

In Excel, I have set of tables on each tab, each table is specifically named. All table has the same width, we want to maintain the format in Excel to Word.

In Word, I have set of bookmarks, the idea is that each bookmark has a name corresponding to the Excel table (i.e. Bookmark named 1 where table name is also 1).

What we would like to achieve is to export tables to where the bookmarks are located in Word.

Can anyone help me set up a macro?

Thank you.

 

[John_w]

Try this macro as a start - edit the line where indicated to specify the full name of your Word document.

Public Sub Copy_Tables_To_Word_Bookmarks()

    Dim WordFile As String
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim ws As Worksheet
    Dim table As ListObject
   
    WordFile = "C:\path\to\Word document.docx"    'CHANGE THIS
   
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(WordFile)
   
    For Each ws In ActiveWorkbook.Worksheets
        For Each table In ws.ListObjects
            table.Range.Copy
            wdDoc.Bookmarks(table.Name).Range.PasteExcelTable False, False, True
        Next
    Next
   
    Application.CutCopyMode = False
   
    wdDoc.Activate

End Sub

I found that the PasteExcelTable method doesn't give exactly the same table format as when manually copying and pasting each table from Excel to Word. The best result was with the PasteExcelTable RTF argument as True. The 3 arguments are:

PasteExcelTable LinkedToExcel As Boolean, WordFormatting As Boolean, RTF AS Boolean

 

[kohminski]

Hi John,

Really appreciate your time in replying to my post.

I am trying to do my own research on how to write this code, but to be more specific on my circumstances:

- We have an existing word document called "Proforma", which contains paragraphs around the tables. Each table is bookmarked.
- We then have an excel document contains all the tables. There are several tabs, which contains tables.
- Each table is named individually, it has specific width on each column and row.
- Using macro, we would like to constantly update the tables only in the existing word document.

Referring to your code above, can you please advise:
- rather than creating a new object, can we refer to the existing word document,
- can we code to refer to the tables on the every active tabs in the excel document
- can we specify the length and width of the table?

Thank you for looking into this. Much appreciate it.

Thanks,

 

[John_w]

Have you tried the code as posted? And does it (nearly) achieve what you require?

- rather than creating a new object, can we refer to the existing word document,

I have modified the code to copy and paste the Excel tables to an already open Word document named 'Proforma'. See below.

- can we code to refer to the tables on the every active tabs in the excel document

I'm not sure what you mean by active tabs. Do you mean multiple selected tabs (called grouped sheets in Excel). The code loops through all the worksheets in the active workbook and all the tables in each worksheet

- can we specify the length and width of the table?

Of the table, as copied to Word? Probably, with extra code.

Public Sub Copy_Tables_To_Word_Bookmarks2()

    Dim wdApp As Object
    Dim wdDoc As Object
    Dim ws As Worksheet
    Dim table As ListObject
    Dim i As Long
    
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    On Error GoTo 0
    
    Set wdDoc = Nothing
    If Not wdApp Is Nothing Then
        For i = 1 To wdApp.Documents.Count
            If InStr(1, wdApp.Documents(i).Name, "Proforma") Then
                Set wdDoc = wdApp.Documents(i)
                Exit For
            End If
        Next
    End If
    
    If wdDoc Is Nothing Then
        MsgBox "The Proforma document is not open", vbExclamation
        Exit Sub
    End If
    
    For Each ws In ActiveWorkbook.Worksheets
        For Each table In ws.ListObjects
            table.Range.Copy
            'PasteExcelTable LinkedToExcel As Boolean, WordFormatting As Boolean, RTF AS Boolean
            wdDoc.Bookmarks(table.Name).Range.PasteExcelTable False, False, True
        Next
    Next
    
    Application.CutCopyMode = False
            
    wdDoc.Activate

End Sub

 

[kohminski]

Hi James,

I have tweaked the code regarding opening the word document as below - this manages to locate and open the word document.

Public Sub Copy_Tables_To_Word_Bookmarks()

Dim WordFile As String
Dim wdApp As Object
Dim wdDoc As Object
Dim ws As Worksheet
Dim table As ListObject

WordFile = "Location\Test.docx"

Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(ThisWorkbook.Path & "/" & "Test" & ".docx")


The below code doesn't seem to work at all.

For Each ws In ActiveWorkbook.Worksheets
For Each table In ws.ListObjects
table.Range.Copy
wdDoc.Bookmarks(table.Name).Range.PasteExcelTable False, False, True
Next
Next

Application.CutCopyMode = False

wdDoc.Activate

End Sub

To clarify:
1. Below is a screenshot of a table named "range_soci" on SOCI tab in excel.
- I have another table named "range_sofp" on SOFP tab in excel.
- The idea is to have many tabs in excel i.e. SOCI, SOFP, etc tabs, each tabs will have a couple of tables.

2. I would like to define the width of each column A, B, C and D.
we would like to count pixels for column, and then define, e.g. column A = 575 pixels, column B 90 pixels, column C 150 pixels and column D 150 pixels.

In word document, i have added 2 bookmarks corresponding to the table names (range_soci and range_sofp).

Once again, really appreciate your help on this - looking forward to your advice on this.

Thanks,

 

[John_w]

Are you sure they are proper tables? The fact that nothing happens when you run the macro suggests that there aren't any tables in the workbook. The names range_soci and range_sofp suggest named ranges, not tables.

 

[kohminski]

I see what you mean, I have formatted as tables for now.

When I ran the same VBA code again, I get an error message saying:

Thanks,

 

[John_w]

You specify the full name of the Word document in the WordFile variable, but don't use it in the wdApp.Documents.Open line. Change that line to:

    Set wdDoc = wdApp.Documents.Open(WordFile)