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

I want to search the criteria above each table and bring the table to Wd (e.g.Table below II.1 copy to Wd Bookmark1; Table below III.1 copy to Wd Bookmark2.....). Your Help will be highly appreciated! Thanks!

 

[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.

 

[Macropod]

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

I want to search the criteria above each table and bring the table to Wd (e.g.Table below II.1 copy to Wd Bookmark1; Table below III.1 copy to Wd Bookmark2.....). Your Help will be highly appreciated! Thanks!

You still haven't said anything to indicate how a macro would recognise what range any given table spans. If a given table only ever spans a range like, say, A1:J10, you need to say so. Alternatively, as DanetAmor suggests, you could name the range each table spans.

 

[Fanel]

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

@Dante: the macro work fine, but I don't want the tables to be an exact range (A1"F16).
In my initial post I had a line of code in which I wanted the tables to be Dynamic (I think this was the line of code - Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy).
Pls help!

 

[Fanel]

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

@macroprod: I want all the tables from Sheet2 to be Dynamic, that's why I wanted to search for a specific word above each table (II.1; III.1; III.2...), and then to bring the table below but in Dynamic mode. Could you help me with it, please?Thanks much!

 

[Fanel]

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

@Dante: the macro work fine, but I don't want the tables to be an exact range (A1"F16).
In my initial post I had a line of code in which I wanted the tables to be Dynamic (I think this was the line of code - Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy).
Pls help!

I tried to insert the following lines of code to search in first table Dynamic, begin from A2 and stop where it finds last value in first table, and then I want same thing for next tables:
Worksheets("Sheet2").Activate
Range("A2", Range("A2").End(xlDown).End(xlToRight)).Copy
Set mydoc = wdApp.Documents.Open
Set rngStart = ActiveDocument.Bookmarks("Bookmark1").Range

I have error 424 object required on this line: Set mydoc = wdApp.Documents.Open
Something is missing and I don't understand what?!

 

[DanteAmor]

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

@Dante: the macro work fine, but I don't want the tables to be an exact range (A1"F16).
In my initial post I had a line of code in which I wanted the tables to be Dynamic (I think this was the line of code - Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy).
Pls help!

A table is dynamic, only you add or remove rows and automatically the range of the table is adjusted, then the macro only has to refer to the name of the table and not have to calculate each of the ranges. Try for a while with this dynamic, you will see that it will help you.

 

[Macropod]

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

macroprod: I want all the tables from Sheet2 to be Dynamic, that's why I wanted to search for a specific word above each table (II.1; III.1; III.2...), and then to bring the table below but in Dynamic mode.

Your insistence on making the code work out for itself where the tables are by finding the 'specific word above each table' makes this much harder than it needs to be. You should be using suitably-named tables. I'm not going to waste time coding around your reluctance to do that.

 

[Fanel]

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

Ok, thanks for your help!

 

[Fanel]

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

Dante, thank you very much for helping me!
I followed your indications, but I didn't managed to make the ranges in each table to be Dynamic (the source to be Dynamic), so I can add/erase new data or rows and when I run the macro, all the data to be exported to Word.
Please, is there a setting that I can make? Tks