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]

 

[Fanel]

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

Hi, Macro,
Thank you so much for all the time and support, I will try both macros, and start with the one indicated by Dante.
I will come back with feedback after I run also the macro you gave me.

 

[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

And now I see another issue, imports also the empty cells (see img)
https://ibb.co/JqMtnZV

 

[Fanel]

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

Macro, I managed to run your macro, and I have same issues:
1. Tables with no headers;
2. Copies also the blank rows.
Please, tell me what to do!
Tks again guys, you are the best!

 

[Macropod]

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

In that case, I suggest you check what ranges you've applied the names to.

 

[Fanel]

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

No 1 issue: The tables are inserted according to indications made by Dante, from Insert>Table, but in the box (Refers to) where I have the range this is not active, I cannot change the range.
https://ibb.co/8Km0PQn

 

[DanteAmor]

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

Directly in the table, you can take the lower right corner of the table and drag to the left and up to the row and column you want, or you can delete the table and create it again.

 

[Fanel]

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

Tried both, doesn't work
Same two problems:
1. Tables with no headers;
[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]2. Copies also the blank rows, if I have a table without information in cells I want to bring in Wd only the Header of table.[/FONT]

 

[Macropod]

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

Cross-posted at: http://www.vbaexpress.com/forum/showthread.php?64486-VBA-Copy-tables-from-excel-to-Wd without any acknowledgement of the help you've already been given here.
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[Macropod]

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

Also cross-posted at: https://www.excelforum.com/excel-pr...1261338-copy-tables-from-excel-to-wd-vba.html
Evidently you don't care about the cross-posting rules here or on any other forum. Thread closed.