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

Attach the Excel

 

[Macropod]

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

Attach the Excel

You cannot attach workbooks here. See Posting Aids: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[Macropod]

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

Try something along the lines of:

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 sheets 2 to 6 and copy them to the corresponding Word bookmarks
    For i = 2 To 6
      If .Bookmarks.Exists("bookmark" & i) Then
        'copy the whole worksheet to clipboard.
        Sheets(i).UsedRange.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

 

[DanteAmor]

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

Use the following.

You have to put which table goes in which brand in the section "set tables and marks"

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("tabla1", "tabla2", "tabla3")    '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

 

[Fanel]

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

Thanks

 

[Fanel]

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

@macroPROD & DanteAmor
I tried both macros but they are not what I want @macroprod: Your macro loops and bring tables from al the sheets in my excel, and I dont want this.
in my case I just want to brig multiple tables that are located only in "Sheet2", I want to set a specific word above each table (e.g Chapter I.1 and the table below to copy to Wd Bookmark1; Chapter II.1 and the table below to copy to Wd Bookmark2; Chapter III.1 > Bookmark3.....) and based on that word to copy the below table into designated Bookmark in Wd.
Hope that I make myself clear. Sorry for my bad English.
Please help me!

 

[Macropod]

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

In that case, you need to explain how your 'tables' - as you referred to them in your initial post - are defined. Your:
Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy
doesn't appear to relate to any particular table.

 

[Fanel]

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

In my initial macro, I had just one table in "Sheet2" and that table was copyied in Wd Bookmark2, now I need from the same sheet to bring multiple tables (I think there will be at least 48 tables in "Sheet2"), and each of them to copy in specific Bookmark in Wd. I dont know haw to adapt the macro to my criteria.
P.S. I will try to put an image of the Ex Sheet2.

 

[Fanel]

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

[/URL] free photo hosting[/IMG]