VBA code for Excel to Word copying problem

[mangodango]

Hello, I was thinking about the VBA macro for exporting a range from Excel to Word document. Following macro seems to be doing it, although I have a problem with the pasted size. The data copied from Excel is too big for a Word document. Is there any way to adjust the copy/paste size, so copied Excel range would fit into the doc?


Many thanks for your insights.

Sub export_excel_to_word()    Set obj = CreateObject("Word.Application")
    obj.Visible = True
    Set newobj = obj.Documents.Add
    ActiveSheet.UsedRange.Copy
    newobj.Range.Paste
    Application.CutCopyMode = False
    obj.Activate
    newobj.SaveAs Filename:=Application.ActiveWorkbook.Path & "\" & ActiveSheet.Name
End Sub

 

[Macropod]

Well, you might start by limiting the scope of what you're copying; otherwise you might try a different paste method. Word supports:
.Paste
.PasteAndFormat
.PasteAppendTable
.PasteAsNestedTable
.PasteExcelTable
.PasteSpecial
any of which might (or might not) be suitable for your situation. Some of those methods take a variety of arguments, too, and some might still necessitate changes to the table's paragraph formatting post-paste. Word tables also have three autofit options you could apply post-paste.