Macro to export a table to Word

Dzib · #1 07-27-2019, 05:02 AM

Hi,

I've found this nice macro that works fine to copy/paste a table from Excel to Word:

Code:

Sub Export()

Dim wdApp As Object
Dim wd As Object

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
    Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wd = wdApp.Documents.Add
wdApp.Visible = True

Sheets("INV").Activate
Set Rng = ThisWorkbook.ActiveSheet.Range("A1:D200")
Rng.Copy
    With wd.Range
        .Collapse Direction:=wdCollapseStart
        .InsertParagraphAfter
        .Collapse Direction:=wdCollapseStart
        .PasteSpecial DataType:=1
            With .Find
                 .ClearFormatting
                 .Text = vbTab
                 .Replacement.ClearFormatting
                 .Replacement.Text = " "
                 .Execute Replace:=wdReplaceAll, Forward:=True, Wrap:=wdFindContinue
            End With
    End With
 End Sub

The problem is that the number of columns is fixed but not the number of rows, that's why I've gone until row 200 (I'll never go beyond).
So in the Word doc I have a bunch of empty lines in my table depending on the number of rows is my Excel table.

I've tried to integrate this macro inside my main macro but I can't get it to work, maybe because at first it was a Word macro, dunno if there are differences.

Code:

Sub DeleteEmptyCol2TableRows()
Application.ScreenUpdating = False
Dim Tbl As Table, i As Long
With ActiveDocument
  For Each Tbl In .Tables
    With Tbl
      For i = .Rows.Count To 1 Step -1
        If Len(.Cell(i, 2).Range.Text) = 2 Then .Rows(i).Delete
      Next i
    End With
  Next Tbl
End With
Application.ScreenUpdating = True
End Sub

It could work fine if I run this macro from Word after the Word doc has been created but I'd like to run all from Excel in one click.

Any idea?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Looking for Macro to Export Images	alex20850	Word VBA	2	01-10-2019 06:27 PM
Export embedded .txt or .csv file from Word bookmark to Access table field	eric.okeefe	Word VBA	4	08-29-2017 09:31 AM
Word Macro to delete table row and table header	NorthSuffolk	Word VBA	6	10-11-2016 05:04 AM
Macro to count and export results	rm7885	Word VBA	3	07-23-2014 12:41 PM
Microsoft word table export ? -need help-	--alexander--	Word	2	04-17-2013 11:13 PM