Convert Excel Range into Word without Tables

Contributor

I have created below code to copy data and paste into word the code is working fine but the problem is when it paste the data into word document then i convert the table into text.

 

Then there are 2 to 3 tables in the Excel data (which i want to keep as it is in word document) which also removes in the word document.

 

Excel tables


and Word removes every table and their words

https://ibb.co/vvTdkJT

 

 

 

 

Sub ExcelWordPaste()

    Dim objWord As Object
    Dim objDoc As Object
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Set objWord = CreateObject("Word.Application")

    objWord.Visible = True

    Range("B3:AP198").Copy

    Set objDoc = objWord.Documents.Add

    With objDoc.Range

        .PasteExcelTable False, False, False
    
        .Tables(1).AutoFitBehavior 2
    
        .InsertAfter vbCr
    
    End With

    objWord.Selection.Rows.ConvertToText Separator:=wdSeparateByTabs, NestedTables:= _
                                         True
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
End Sub

 

 

 

 

6 Replies

@Valiant 

Interesting . 

 

 

Sub ExportToWord()
    Dim WordApp As Word.Application, WordDocument As Word.Document
    
    Set WordApp = New Word.Application
    WordApp.Visible = True
    Set WordDocument = WordApp.Documents.Add
    
    ActiveSheet.UsedRange.Copy
    WordDocument.Range.Paste
    Application.CutCopyMode = False
    
    Dim tbl As Object
    For Each tbl In WordDocument.Tables
        tbl.AutoFitBehavior wdAutoFitContent
    Next
    WordDocument.SaveAs Filename:=Application.ActiveWorkbook.Path & "\" & ActiveSheet.Name
    
    Set WordDocument = Nothing
    Set WordApp = Nothing
    
End Sub

 

 

If it helps you, remember to flag it as solved and hit the like button.

 

 

No, Its not seems that i have the code in the workbook i forgot to paste. can you please check it.
The code is different from yours. I developed it from scratch based on your post. Can you please test it to see if works as you wish?

Yes it works as mine but when i convert the Word Table to Text it removes the Excel Original tables. These are https://ibb.co/G36M177 I do not wat to remove excel original tables in wod after Converting text to table

@Valiant 

Office 365 (16.0.12527...) 64Bits

JulianoPetrukio_0-1632564754689.png

 

Yes its was doing this before as well the real problem is i want to remove the tables (Data should be in Word Document as Text format) When i use this option https://ibb.co/WBNFGJM it convertes the data as Text Format and remove the Excel Original table as well.

I just want that all data should be formatted as text and Excel 3 Original tables will same in Word Document as you posted pictures above.