Consulting

Results 1 to 7 of 7

Thread: VBA paste special to Word

  1. #1
    VBAX Newbie
    Joined
    May 2018
    Posts
    2
    Location

    VBA paste special to Word

    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
    EndSub

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,424
    Location
    Last edited by macropod; 05-11-2018 at 04:12 PM.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    VBAX Contributor
    Joined
    Jul 2016
    Posts
    172
    Location
    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

  4. #4
    VBAX Contributor
    Joined
    Jul 2016
    Posts
    172
    Location
    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]
    Last edited by Kilroy; 05-18-2018 at 10:46 AM.

  5. #5
    VBAX Contributor
    Joined
    Jul 2016
    Posts
    172
    Location
    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

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,424
    Location
    Kilroy: According to the OP the question was answered a week ago @ https://www.mrexcel.com/forum/genera...size-word.html. You might want to check the code there.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  7. #7
    VBAX Contributor
    Joined
    Jul 2016
    Posts
    172
    Location
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •