Excel Tables export to Word

[mitch]

Hi,

I haven't posted on here, or any other VBA Forum before so if I've done/presented something incorrectly please let me know.

I'm trying to write a script that can export tables from Excel, into Word. Yes, I have seen that this has been discussed in the past, but I haven't seen an example that directly relates to my problem, or one that has a solution close enough for me to tweak to serve my purposes. Due to the nature of my workplace, I'm not allowed to share any information or anything like that so I've attached a picture, and will attempt to explain the issue.

Example.jpg

The GET DATA button brings in data from multiple other workbooks, that are maintained from different sections of my office. The data is sorted into tables FRUITS, ANIMALS and CATS. So the number of rows of these tables are never set, they vary with time. However, the columns are set.I have already completed the GET DATA button and it all works fine.

I want the export button to do the following:
- Export all tables on the worksheet hat have data (for example, CATS would not be exported since it has 0 rows of data present)
- Each Export must start on a new page of the same word document as the previous export.
- The tables must be imported individually so that they can be Auto Fitted to page.

My current progress is quite abysmal on this EXPORT button thus far. I have found a few example blocks of code on the internet but haven't made any worth addition to them that would make them worth including.

Thanks in advance for any help!

[snb]

If you can't share information, I can't share solutions.
Excel<>photoshop.

[mitch]

Understood, I'll see what I can do

[Kenneth Hobs]

Welcome to the forum!

That is fairly advanced so I am not surprised. Export to a Template, new DOCX, or existing DOCX file will need to be known.

Getting help by supplying a short example file(s) is a good way to get help. In the Go Advanced button, click the paperclip icon or below the reply box click the Manage Attachments link to attach file(s).

I am not sure that I have seen Excel "tables" exported to a DOCX file. There are several examples exporting a range to a table in a DOCX. e.g.

'Add Table to MSWord
' http://vbaexpress.com/forum/showthread.php?t=23975
' http://vbaexpress.com/forum/showthread.php?p=168731
' http://www.vbaexpress.com/forum/show...el-to-word-doc


'Import Word Tables
'vog, http://www.mrexcel.com/forum/showthread.php?t=382541
'Ruddles, http://www.mrexcel.com/forum/showthread.php?t=524091


'snb, Word Tables
' http://www.vbaexpress.com/forum/showthread.php?t=45520
' http://www.vbaexpress.com/forum/showthread.php?t=46472

[snb]

@Ken

The Word field "Includetext' does exactly do that.

[mitch]

I have attached what I have so far, still have a fair way to go but am really stuck at the export stage. The main worksheet that the user will be dealing with is the "Overview" Sheet. The user enters an ID Number in Cell B2 and clicks the "Export" Button. This button filters data from the "Raw" sheets and then exports into word. I'm trying to get the tables from the "ECW_Filtered" and the "OT_Filtered" Sheets to be on the same page as each other, but separated by one line. I am also trying to get the "MB_Filtered" Table on a brand new page, separated from the other 2. As it currently is, each export is pasted over the top of the old one. Any help with this coding will be appreciated as I've been stuck on it for a while now.

Additional Query
I intend to have this eventually done through user forms, and have an entry field where the user enters multiple ID Numbers with comma separators. From there this ID entry will be Split into an array and the WordExcel() Sub will be cycled through around 20-30 times (20-30 ID Numbers Entered). Will using the current method, i.e the Copy Paste method be extremely slow / crash the PC with this many loops?

Thanks

[mana]

>As it currently is, each export is pasted over the top of the old one.


>wdDoc.Activate
>wdDoc.Range.Paste

[mitch]

Yes I know they are the problem lines, but I don't know the correct coding to make it do what I want

[mana]

Dim wdRng As Object


Set wdRng = wdDoc.Range
wdRng.InsertAfter vbCr
wdRng.Collapse 0
wdRng.Paste

[mana]

Sorry,


> Each Export must start on a new page of the same word document as the previous export.

Dim wdRng As Object


Set wdRng = wdDoc.Range
wdRng.Collapse 0
wdRng.InsertBreak 7
wdRng.Paste

[mitch]

Thanks! The code you linked works great. After some tinkering I worked out that InsertBreak 3 did what I wanted for the first part and InsertBreak 7 worked for the other.

[mdmackillop]

Hi Mitch
Can you put this together and post a workbook as a working solution for the benefit of others.
Regards
MD

[mana]

My test code;

Option Explicit

Sub WordExcel2()
    Dim myDoc As Object
    Dim myID As String

    Set myDoc = GetObject(ThisWorkbook.Path & "\test.docx")
    myDoc.Application.Visible = True
    
    myID = "***_X0001"  'Sheets("Overview").Range("B1").Value
    
    Call FilterID(5, myID, Sheets("ECW_Raw"), Sheets("ECW_Filtered"), myDoc)
    Call FilterID(5, myID, Sheets("OT_Raw"), Sheets("OT_Filtered"), myDoc)
    Call FilterID(1, myID, Sheets("MB_Raw"), Sheets("MB_Filtered"), myDoc)
    
End Sub


Private Sub FilterID(lngCol As Long, strCrit As String, wsR As Worksheet, wsF As Worksheet, wdDoc As Object)
    Dim wdRng As Object
    
    wsF.UsedRange.Offset(1).ClearContents
  
    With wsR.Range("A1").CurrentRegion.Columns("A:F").Offset(, lngCol - 1)
        .AutoFilter
        .AutoFilter 1, strCrit
        If .Columns("A").SpecialCells(xlCellTypeVisible).Count > 1 Then
            Union(.Offset(1).Columns("B"), .Offset(1).Columns("F")).Copy
            wsF.Range("A2").PasteSpecial xlPasteValues
            wsF.Range("A1").CurrentRegion.Copy
            Set wdRng = wdDoc.Range
            wdRng.Collapse 0
            wdRng.InsertBreak 7     '3:wdSectionBreakContinuous, 7:wdPageBreak
            wdRng.Paste
            Application.CutCopyMode = False
        End If
        .AutoFilter
    End With
    
End Sub

[mitch]

This is the workbook I ended up with once I implemented mana's fix, though I think the test code shown above is a bit more elegant than what I have used. Thanks again.

[mdmackillop]

Thanks Mitch