Copy Multiple Excel Tables to Word

[Fanel]

Please help!
I am new to VBA, and I need help to make the following macro to import from XL ("Sheet2 ") multiple tables 1,2,3..., import them to a specific Wd doc Bookmarks 1, 2, 3, each table from sheet2 linked to one Bookmark from Wd doc.
Based on the macro attached I managed to bring only the first table from Sheet2 and copy to designated spot Bookmark2. Now I need to bring the next table (e.g. III.1 table below) an copy-it to Wd Bookmark3, and so on.
One way, maybe set macro to search the specific text above table (e.g. Chapter III.1), and import only the table below that text.
NOTE: In Wd doc "Fisa.docx" I have infomation that I dont want to be erased when the tables are imported from Exl. Thank you!

[/COLOR]Sub ExportExcelDataToWordDocument2()



    'Dim wdExcelApp As Application               'Excel is the default library (optional)
    Dim wdWordApp As Word.Application   'Word app
  
  Application.ScreenUpdating = False
  
' Creating a new instance of Word
    Set wdWordApp = New Word.Application 'instantiate a new instance of Word 2010
  
  
    With wdWordApp
      
        ' Making Word Visible on the screen
        .Visible = True             'iff false, document is invisible.
        .Activate ' make it the top pane, bring it to the front.
        


      
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' create a new Word Document based on the specified template
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        .Documents.Add "C:\Users\stefan.georgescu\Desktop\Fisa.dotm"
      
        'as before, copy the whole table from sheet to clipboard.
        Sheet2.Activate
        Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy
      
        .Selection.GoTo what:=-1, Name:="bookmark2" ' -1 means "wdgotobookmark"
        .Selection.Paste      'paste from the clipboard to the Word Doc.
        
  
      
      
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' Save WORD Document
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim TheFileName As String
        TheFileName = "C:\Users\stefan.georgescu\Desktop\Fisa.docx"
          
        '(SaveAs is for Office 2003 and earlier - deprecated)
        .ActiveDocument.SaveAs2 TheFileName
            'replaces existing .doc iff exists
          
          
        ' Close Documents and Quit Word
        .ActiveDocument.Close 'close .DOCx
        .Quit 'exit Word
    End With
  
  Application.ScreenUpdating = True
  
    'MEMORY CLEANUP
    Set wdWordApp = Nothing 'garbage collection
    'Set wdExcelApp = Nothing 'OPTIONAL
  
  
End Sub
[COLOR=#2A2E2E]

[/IMG]

 

[Macropod]

Re: Export multiple Tables from Excel to a Word document using VBA

The default behaviour for tables in Excel is to expand automatically if you add a new row at the bottom or a new column on the right; they'll also expand automatically if you add a new row or column within the existing range. See: https://www.contextures.com/xlExcelTable01.html Accordingly, if you create and use one appropriately, there won't be any issues regarding the range adjusting to take account of rows/columns being added/deleted.

PS: If you want a macro to process your tables, don't number them with Roman numerals like you've done in post 10.

 

[Fanel]

Re: Export multiple Tables from Excel to a Word document using VBA

I renamed the tables, they are not numerals now. I was trying to make the tables expand (search until it finds blank box) with OFFSET formula from NameManager, but when I run the macro it doesen't bring me the selected tables in Wd.

 

[Fanel]

Re: Export multiple Tables from Excel to a Word document using VBA

Now my tables in Excel looks like this

https://ibb.co/ckKzxs6
In NameManager for first table (example name Gestiune SSC) the formula is =Sheet2!$A$2:$F$22
Now I want the formula tot search where it finds text in that range and that info to transfer to Wd (no blanks).
I made a test with OFFSET formula, but the macro doesn't want to bring any info to Wd.
Thanks

 

[Macropod]

Re: Export multiple Tables from Excel to a Word document using VBA

The macro won't 'work' as it wasn't written for tables, as such, and we don't know what you've named them. As for trying to make them expand via the Offset formula, that suggests you're using named ranges, not table names. The two are entirely different. Read the link I posted.

 

[Fanel]

Re: Export multiple Tables from Excel to a Word document using VBA

Yes, I've used "name ranges" based on the macro provided by Dante in initial post. Now my macro looks like this.

Sub ExportExcelDataToWordDocument2()



     Application.ScreenUpdating = False
    'Dim wdExcelApp As Application               'Excel is the default library (optional)
    Dim wdWordApp As Word.Application   'Word app
    ' Creating a new instance of Word
    Set wdWordApp = New Word.Application 'instantiate a new instance of Word 2010
    '
    ' Set tables and marks
    tablas = Array("GestiuneSSC", "AlimATM", "Depridangajati")     'name of your tables
    marcas = Array("bookmark1", "bookmark2", "bookmark3") 'corresponding with the tables above
    '
    With wdWordApp
        ' Making Word Visible on the screen
        .Visible = True             'iff false, document is invisible.
        .Activate ' make it the top pane, bring it to the front.
        
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' create a new Word Document based on the specified template
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        .Documents.Add "C:\Users\stefan.georgescu\Desktop\Fisa.dotm"
        
        Sheet2.Activate
        For i = LBound(tablas) To UBound(tablas)
            Range(tablas(i)).Copy
            .Selection.GoTo what:=-1, Name:=marcas(i) '"bookmark2" ' -1 means "wdgotobookmark"
            .Selection.Paste      'paste from the clipboard to the Word Doc.
        Next
        
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' Save WORD Document
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim TheFileName As String
        TheFileName = "C:\Users\stefan.georgescu\Desktop\Fisa.docx"
        '
        '(SaveAs is for Office 2003 and earlier - deprecated)
        .ActiveDocument.SaveAs TheFileName
        'replaces existing .doc iff exists
        ' Close Documents and Quit Word
        .ActiveDocument.Close 'close .DOCx
        .Quit 'exit Word
    End With
    Application.ScreenUpdating = True
    'MEMORY CLEANUP
    Set wdWordApp = Nothing 'garbage collection
End Sub

 

[Macropod]

Re: Export multiple Tables from Excel to a Word document using VBA

So, do you want your ranges to expand automatically using table names, or are you going to stick with named ranges?

 

[Fanel]

Re: Export multiple Tables from Excel to a Word document using VBA

Doesn't matter, until the source tables from Sheet2 (tables Gestiune SSC; Alim ATM; Dep rid angajati...) are dynamic and each time I modify them (in excel) to be imported accordingly in Word (specific bookmarks).
But, from what I understand, if I stick with "named ranges" (how I managed to do the macro), probably they wont be dynamic, or I don't know how to make them dynamic.
Then, please, if you have the time to help me and write the code so I can have the tables source dynamic, I will be grateful!

 

[DanteAmor]

Re: Export multiple Tables from Excel to a Word document using VBA

To name a table: select the range from "A1" to "F16" and in the name box type, for example: "tabla1", then select the range from "A18" to "F33" and in the name box type "tabla2" and so on with each mark.


In the macro in these lines you must relate each table with its respective mark

    tablas = Array("tabla1", "tabla2", "tabla3")    'name of your tables
    marcas = Array("bookmark1", "bookmark2", "bookmark3") 'corresponding with the tables above

Execute the macro.

I apologize for all the confusion that I caused.



I apologize for all the confusion that I caused.
Correct, to create tables, select the range of cells from A1 to F16, select from the Insert menu, Table, OK, in the table name field, type "GestiuneSSC", select the next range, select from the Insert menu, Table, Accept, in the table name field, type "AlimATM" and so on with each table.

Try the macro again

 

[Macropod]

Re: Export multiple Tables from Excel to a Word document using VBA

Assuming the tables/named ranges you want to copy are now named Table2 to Table6, only a few small changes to my original code are needed:

Sub ExportExcelDataToWordDocument()
Dim wdApp As New Word.Application, wdDoc As Word.Document, i As Long
Const StrFl As String = "C:\Users\stefan.georgescu\Desktop\Fisa"
With wdApp
  'Hide the Word session
  .Visible = False
  ' create a new Word Document based on the specified template
  Set wdDoc = .Documents.Add(StrFl & ".dotm")
  With wdDoc
    'loop through tables 2 to 6 and copy them to the corresponding Word bookmarks
    For i = 2 To 6
      If .Bookmarks.Exists("bookmark" & i) Then
        'copy the table to clipboard.
        ThisWorkbook.Sheets("Sheet2").Range("Table" & i).Copy
        'paste from the clipboard to the Word Doc.
        .Bookmarks("bookmark" & i).Range.Paste
      End If
    Next
    'Save Word Document
    .SaveAs2 StrFl & ".docx"
    'Close Document
    .Close False
  End With
  'exit Word
  .Quit
End With
'MEMORY CLEANUP
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub

As you can see, only one line of code and two lines of comments have changed.

 

[Fanel]

Re: Export multiple Tables from Excel to a Word document using VBA

Hello, Dante, and thank you again for all your support!
I've made the adjustment accordingly, the macro works but I have 2 issues:
1) In Wd it doesn't brings the Header of the table (see img URL);
2) I have one error in line "Range(tablas(i)).Copy", Run-time err. 1004 Method "Range of object"_Global failed.
Please help!

https://ibb.co/y6gdRL2