Microsoft Office Forums - View Single Post

gmayor · 07-27-2019, 08:25 PM

You had it almost right the first time, but you need to set the last row e.g. as follows. You also need to set the numeric values of the Word commands or they won't work with late binding. You haven't defined all your variables.

Code:

Option Explicit
Sub Export()

Dim wdApp As Object
Dim wd As Object
Dim xlSheet As Worksheet
Dim rng As Range
Dim LastRow As Long
Const wdReplaceAll As Long = 2
Const wdFindContinue As Long = 1

    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

    Set xlSheet = ActiveWorkbook.Sheets("INV")
    With xlSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng = .Range("A1:D" & LastRow)
        rng.Copy
        With wd.Range
            .Collapse Direction:=1
            .InsertParagraphAfter
            .PasteSpecial DataType:=1
            With .Find
                .ClearFormatting
                .Text = vbTab
                .Replacement.ClearFormatting
                .Replacement.Text = " "
                .Execute Replace:=wdReplaceAll, Forward:=True, Wrap:=wdFindContinue
            End With
        End With
    End With
End Sub