Convert Excel Range into Word without Tables

%3CLINGO-SUB%20id%3D%22lingo-sub-2783306%22%20slang%3D%22en-US%22%3EConvert%20Excel%20Range%20into%20Word%20without%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2783306%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20below%20code%20to%20copy%20data%20and%20paste%20into%20word%20the%20code%20is%20working%20fine%20but%20the%20problem%20is%20when%20it%20paste%20the%20data%20into%20word%20document%20then%20i%20convert%20the%20table%20into%20text%2C%20then%20there%20are%202%20to%203%20tables%20in%20the%20Excel%20data%20(which%20i%20want%20to%20keep%20as%20it%20is%20in%20word%20document)%20which%20also%20removes%20in%20the%20word%20document.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20at%20the%20end%20i%20want%20to%20add%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3ESelection.TypeParagraph%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E2%20to%203%20paragraphs%20line.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20added%20both%20excel%20file%20and%20with%20code%20and%20Word%20document%20with%20the%20result%20i%20am%20trying%20to%20get.%3C%2FP%3E%3CP%3EI%20would%20really%20appreciated%20the%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2783306%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2783565%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Excel%20Range%20into%20Word%20without%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2783565%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F945050%22%20target%3D%22_blank%22%3E%40Valiant%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInteresting%20.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20ExportToWord()%0A%20%20%20%20Dim%20WordApp%20As%20Word.Application%2C%20WordDocument%20As%20Word.Document%0A%20%20%20%20%0A%20%20%20%20Set%20WordApp%20%3D%20New%20Word.Application%0A%20%20%20%20WordApp.Visible%20%3D%20True%0A%20%20%20%20Set%20WordDocument%20%3D%20WordApp.Documents.Add%0A%20%20%20%20%0A%20%20%20%20ActiveSheet.UsedRange.Copy%0A%20%20%20%20WordDocument.Range.Paste%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20%0A%20%20%20%20Dim%20tbl%20As%20Object%0A%20%20%20%20For%20Each%20tbl%20In%20WordDocument.Tables%0A%20%20%20%20%20%20%20%20tbl.AutoFitBehavior%20wdAutoFitContent%0A%20%20%20%20Next%0A%20%20%20%20WordDocument.SaveAs%20Filename%3A%3DApplication.ActiveWorkbook.Path%20%26amp%3B%20%22%5C%22%20%26amp%3B%20ActiveSheet.Name%0A%20%20%20%20%0A%20%20%20%20Set%20WordDocument%20%3D%20Nothing%0A%20%20%20%20Set%20WordApp%20%3D%20Nothing%0A%20%20%20%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20it%20helps%20you%2C%20remember%20to%20flag%20it%20as%20solved%20and%20hit%20the%20like%20button.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2784374%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Excel%20Range%20into%20Word%20without%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2784374%22%20slang%3D%22en-US%22%3ENo%2C%20Its%20not%20seems%20that%20i%20have%20the%20code%20in%20the%20workbook%20i%20forgot%20to%20paste.%20can%20you%20please%20check%20it.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2784426%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Excel%20Range%20into%20Word%20without%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2784426%22%20slang%3D%22en-US%22%3EThe%20code%20is%20different%20from%20yours.%20I%20developed%20it%20from%20scratch%20based%20on%20your%20post.%20Can%20you%20please%20test%20it%20to%20see%20if%20works%20as%20you%20wish%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
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.