VBA paste special to Word

[hhn]

I'm trying to paste paragraphs from Excel to Word. I want to be able to paste "keep text only" so long sentences would naturally go to a second line instead of disappearing in Word. I have tried to modify the paste statement with no lucks. Thank you for your help.

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]

Cross-posted at:
https://www.mrexcel.com/forum/general-excel-discussion-other-questions/1055210-vba-format-size-word.html

and:
https://stackoverflow.com/questions/50280867/copy-usedrange-to-word-document


Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

[Kilroy]

Try this it works for me.


Sub PasteUsedRangeToWordPasteTextOnly()
Set obj = CreateObject("Word.Application")
obj.Visible = True
Set newobj = obj.Documents.Add
ActiveSheet.UsedRange.Copy
obj.Activate
newobj.Range.PasteSpecial DataType:=wdPasteText
End Sub

[Kilroy]

The word document ends up with extra spaces so I usually run this as well:


Sub RemoveSpaceTabReplaceWithTab()
Selection.WholeStory
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = vbTab
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "^32{2,}"
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute Replace:=wdReplaceAll
End With
End Sub]

[Kilroy]

Sorry here is a better functioning bit to fix up the word document. It gets rid of the table and replaces the tabs with spaces.



Sub FixPaste()
Selection.WholeStory
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
Selection.Tables(1).Select
Selection.Copy
Selection.Tables(1).Select
Selection.Tables(1).Delete
Selection.PasteAndFormat (wdFormatPlainText)
With Selection.Find
.Text = vbTab
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "^32{2,}"
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute Replace:=wdReplaceAll
End With
End Sub

[macropod]

Kilroy: According to the OP the question was answered a week ago @ https://www.mrexcel.com/forum/general-excel-discussion-other-questions/1055210-vba-format-size-word.html. You might want to check the code there.

[Kilroy]

Thanks Paul. I'll check it out. I know the above code is a little wonky but I ask for help so I try to help when I can.