none
Unselect pasted text/range in WORD RRS feed

  • Question

  • I am trying to copy from an excel file to a word file.

    Firstly, I copy text from a cell in excel and paste as text to word.

    Then I want to unselect the pasted text in word.

    Go back to the excel file and copy a range and paste as a table (or rather pastespecial metafilepicture).

    Unselect the pasted table.

    Then repeat the actions above several times.

    However, as I paste the table, the text that I initially copied gets overwritten. I have tried to "unselect" the selection, to "moveend", "movedown", "insertbreak", "insertparagraph"... though each time the initial text I copied remains selected and I end up pasting over it.

    Monday, April 1, 2019 4:15 PM

Answers

  • Insert the following line below Set wenduseTable = wordAppendix.Range:

        wendUseTable.Collapse Direction:=wdCollapseEnd


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by SBLondon Tuesday, April 2, 2019 11:30 AM
    Tuesday, April 2, 2019 10:44 AM

All replies

  • Try

        Selection.Collapse Direction:=wdCollapseEnd
        Selection.TypeParagraph


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, April 1, 2019 6:42 PM
  • Thank you Hans. I tried your suggestion and unfortunately the same thing happened. I've been stepping into the code to see what happens:

    The initial text gets copied and pasted fine, as I wanted. However, it remains selected so anything else I do in word after that overwrites it. Whether that's inserting a break, paragraph or table. 

    I did try the actions I wanted and recorded a macro, but the "unselecting" or just placing the cursor after the first copied text, does not show as anything. 

    Kind regards, SB

    This is the code I'm using from Excel:

    Sub copytoword()
    
        Dim tableSheet As Worksheet
        Dim eenduseTitle As Excel.Range
        Dim eenduseTable As Excel.Range
        Dim wordApp As Word.Application
        Dim wordAppendix As Word.Document
        Dim wenduseTable As Word.Range
        
        Application.ScreenUpdating = False
        Application.EnableEvents = False
          
        Set tableSheet = Worksheets("Tables")
        
        ' set ranges from Excel
        Set eenduseTitle = tableSheet.Range("A3")
        
        Set eenduseTable = tableSheet.Range("A5:J65")
    
        On Error Resume Next
        
        'Is MS Word already opened?
        Set wordApp = GetObject(class:="Word.Application")
        
        Err.Clear
        If wordApp Is Nothing Then Set wordApp = CreateObject(class:="Word.Application")
        
        If Err.Number = 429 Then
            MsgBox "Microsoft Word could not be found."
            GoTo EndRoutine
        End If
        
        On Error GoTo 0
        
        wordApp.Visible = True
        wordApp.Activate
        
        'Create a New Document
        Set wordAppendix = wordApp.Documents.Add
        
        wordApp.Selection.Text = eenduseTitle.Value '& vbNewLine
        wordApp.Selection.Collapse Direction:=wdCollapseEnd
        wordApp.Selection.TypeParagraph
            
        '    wordApp.Selection.EndOf
        '    wordApp.Selection.EscapeKey
        '    wordApp.Selection.TypeParagraph
        '    wordApp.Selection.InsertBreak
             
        Application.CutCopyMode = False
        
        ''Copy Excel Table Range
            eenduseTable.Copy
        
        Set wenduseTable = wordAppendix.Range
        
        With wenduseTable
            .Select
            .PasteSpecial link:=False, _
                DataType:=wdPasteMetafilePicture, _
                Placement:=wdInLine, _
                DisplayAsIcon:=False
        End With
        
        EndRoutine:
            Application.ScreenUpdating = True
            Application.EnableEvents = True
        
        Application.CutCopyMode = False
        wordApp.Visible = True
    
    End Sub


    Tuesday, April 2, 2019 9:10 AM
  • Insert the following line below Set wenduseTable = wordAppendix.Range:

        wendUseTable.Collapse Direction:=wdCollapseEnd


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by SBLondon Tuesday, April 2, 2019 11:30 AM
    Tuesday, April 2, 2019 10:44 AM
  • That worked. Thanks so much Hans!

    Now I need to modify my title copy+paste as I need to copy and paste over to word a set of three titles and tables for about thirty or so countries. I don't want each set of title + table to overwrite the previous.

    Kind regards, SB

    Tuesday, April 2, 2019 11:30 AM
  • You can use

    wordApp.Selection.EndKey Unit:=wdStory

    to move to the end of the document


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, April 2, 2019 2:50 PM
  • Hello Hans, thank you for the additional suggestion and sorry for the delay. I had other things to work out for this code and some other work to do.

    After a lot of faffing around I now have a loop where a country from a list is selected, from this the tables are populated according to that country's data. Then these tables (+ their titles) are copied and pasted to the word one by one, similar to the code above.

    However, my set of three tables gets overwritten each time the loop runs through. I tried adding the line you suggested after the last table I paste but it didn't seem to work. I'm not sure why. Can you see? Should I use the collapse function instead?

    kind regards, SB

    Code is now:

    Sub countryloop2()
    
    Dim listSheet As Worksheet
    Dim tableSheet As Worksheet
    Dim countryRegion As Excel.Range
    Dim countryName As Excel.Range
    Dim countryRange As Excel.Range
    ' the following are for the copying and pasting to word
    Dim efirstuseTitle As Excel.Range
    Dim efirstuseTable As Excel.Range
    Dim eactualuseTitle As Excel.Range
    Dim eactualuseTable As Excel.Range
    Dim eenduseTitle As Excel.Range
    Dim eenduseTable As Excel.Range
    Dim wordApp As Word.Application
    Dim wordAppendix As Word.Document
    Dim wfirstuseTable As Word.Range
    Dim wactualuseTable As Word.Range
    Dim wenduseTable As Word.Range
    
    'get current working spreadsheet ready
    
        Set listSheet = Worksheets("lists")
        Set tableSheet = Worksheets("Tables")
        Set countryRange = listSheet.Range("CountryTable")
            
    'opening the word document
        Application.ScreenUpdating = False
        Application.EnableEvents = False
    
        On Error Resume Next
        
        Set wordApp = GetObject(class:="Word.Application")
        
        Err.Clear
        If wordApp Is Nothing Then Set wordApp = CreateObject(class:="Word.Application")
        If Err.Number = 429 Then
            MsgBox "Microsoft Word could not be found."
            GoTo EndRoutine
        End If
        
        On Error GoTo 0
        wordApp.Visible = True
        wordApp.Activate
            
        Set wordAppendix = wordApp.Documents.Add
            
    '    MsgBox "named range: " & tableSheet.Range("SelectedCountry").Value
    
    'go through the list of countries for the appendix section
        For Each countryName In countryRange
    '        MsgBox "country: " & countryName & "; " & countryName.Address & ", " & countryName.Value & " | " & countryRange.Address & " ! " & countryRange.VarType
            tableSheet.Range("SelectedCountry").Value = countryName.Value
    '        MsgBox "country selected is: " & tableSheet.Range("B1")
                
            Set efirstuseTitle = tableSheet.Range("Z3")
            Set efirstuseTable = tableSheet.Range("Z5:AI20")
            Set eactualuseTitle = tableSheet.Range("L3")
            Set eactualuseTable = tableSheet.Range("L5:X19")
            Set eenduseTitle = tableSheet.Range("A3")
            Set eenduseTable = tableSheet.Range("A5:J65")
            
            wordAppendix.Content.Text = efirstuseTitle.Value & vbNewLine
            
            efirstuseTable.Copy
             
            Set wfirstuseTable = wordAppendix.Range
            wfirstuseTable.Collapse Direction:=wdCollapseEnd
            
            With wfirstuseTable
                .Select
                .PasteSpecial link:=False, _
                    DataType:=wdPasteEnhancedMetafile, _
                    Placement:=wdInLine, _
                    DisplayAsIcon:=False
            End With
               
            wordAppendix.Content.InsertAfter Text:=" text1" & vbNewLine
            wordAppendix.Content.InsertAfter Text:=eactualuseTitle.Value & vbNewLine
            
            Application.CutCopyMode = False
            eactualuseTable.Copy
            
            Set wactualuseTable = wordAppendix.Range
            wactualuseTable.Collapse Direction:=wdCollapseEnd
            
            With wactualuseTable
                .Select
                .PasteSpecial link:=False, _
                    DataType:=wdPasteEnhancedMetafile, _
                    Placement:=wdInLine, _
                    DisplayAsIcon:=False
            End With
            
            wordAppendix.Content.InsertAfter Text:=" text2" & vbNewLine
            wordAppendix.Content.InsertAfter Text:=eenduseTitle.Value & vbNewLine
            
            Application.CutCopyMode = False
            eenduseTable.Copy
            
            Set wenduseTable = wordAppendix.Range
            wenduseTable.Collapse Direction:=wdCollapseEnd
            
            With wenduseTable
                .Select
                .PasteSpecial link:=False, _
                    DataType:=wdPasteEnhancedMetafile, _
                    Placement:=wdInLine, _
                    DisplayAsIcon:=False
            End With
            
            wordAppendix.Content.InsertAfter Text:=" text3"
            
            wordApp.Selection.EndKey Unit:=wdStory
                     
        Next
    'finished list of countries
    
    EndRoutine:
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Application.CutCopyMode = False
        wordApp.Visible = True
            
    End Sub
    

    Friday, April 5, 2019 12:11 PM
  • Here is a modified version:

    Sub countryloop2()
    
    Dim listSheet As Worksheet
    Dim tableSheet As Worksheet
    Dim countryRegion As Excel.Range
    Dim countryName As Excel.Range
    Dim countryRange As Excel.Range
    ' the following are for the copying and pasting to word
    Dim efirstuseTitle As Excel.Range
    Dim efirstuseTable As Excel.Range
    Dim eactualuseTitle As Excel.Range
    Dim eactualuseTable As Excel.Range
    Dim eenduseTitle As Excel.Range
    Dim eenduseTable As Excel.Range
    Dim wordApp As Word.Application
    Dim wordAppendix As Word.Document
    Dim wfirstuseTable As Word.Range
    Dim wactualuseTable As Word.Range
    Dim wenduseTable As Word.Range
    
    'get current working spreadsheet ready
    
        Set listSheet = Worksheets("lists")
        Set tableSheet = Worksheets("Tables")
        Set countryRange = listSheet.Range("CountryTable")
            
    'opening the word document
        Application.ScreenUpdating = False
        Application.EnableEvents = False
    
        On Error Resume Next
        
        Set wordApp = GetObject(class:="Word.Application")
        
        Err.Clear
        If wordApp Is Nothing Then Set wordApp = CreateObject(class:="Word.Application")
        If Err.Number = 429 Then
            MsgBox "Microsoft Word could not be found."
            GoTo EndRoutine
        End If
        
        On Error GoTo 0
        wordApp.Visible = True
        wordApp.Activate
            
        Set wordAppendix = wordApp.Documents.Add
            
    '    MsgBox "named range: " & tableSheet.Range("SelectedCountry").Value
    
    'go through the list of countries for the appendix section
        For Each countryName In countryRange
    '        MsgBox "country: " & countryName & "; " & countryName.Address & ", " & countryName.Value & " | " & countryRange.Address & " ! " & countryRange.VarType
            tableSheet.Range("SelectedCountry").Value = countryName.Value
    '        MsgBox "country selected is: " & tableSheet.Range("B1")
                
            Set efirstuseTitle = tableSheet.Range("Z3")
            Set efirstuseTable = tableSheet.Range("Z5:AI20")
            Set eactualuseTitle = tableSheet.Range("L3")
            Set eactualuseTable = tableSheet.Range("L5:X19")
            Set eenduseTitle = tableSheet.Range("A3")
            Set eenduseTable = tableSheet.Range("A5:J65")
            
            wordAppendix.Content.InsertAfter Text:=efirstuseTitle.Value & vbNewLine
            
            efirstuseTable.Copy
             
            Set wfirstuseTable = wordAppendix.Range
            wfirstuseTable.Collapse Direction:=wdCollapseEnd
            
            With wfirstuseTable
                .Select
                .PasteSpecial link:=False, _
                    DataType:=wdPasteEnhancedMetafile, _
                    Placement:=wdInLine, _
                    DisplayAsIcon:=False
            End With
               
            wordAppendix.Content.InsertAfter Text:=vbNewLine & "text1" & vbNewLine
            wordAppendix.Content.InsertAfter Text:=eactualuseTitle.Value & vbNewLine
            
            Application.CutCopyMode = False
            eactualuseTable.Copy
            
            Set wactualuseTable = wordAppendix.Range
            wactualuseTable.Collapse Direction:=wdCollapseEnd
            
            With wactualuseTable
                .Select
                .PasteSpecial link:=False, _
                    DataType:=wdPasteEnhancedMetafile, _
                    Placement:=wdInLine, _
                    DisplayAsIcon:=False
            End With
            
            wordAppendix.Content.InsertAfter Text:=vbNewLine & "text2" & vbNewLine
            wordAppendix.Content.InsertAfter Text:=eenduseTitle.Value & vbNewLine
            
            Application.CutCopyMode = False
            eenduseTable.Copy
            
            Set wenduseTable = wordAppendix.Range
            wenduseTable.Collapse Direction:=wdCollapseEnd
            
            With wenduseTable
                .Select
                .PasteSpecial link:=False, _
                    DataType:=wdPasteEnhancedMetafile, _
                    Placement:=wdInLine, _
                    DisplayAsIcon:=False
            End With
            
            wordAppendix.Content.InsertAfter Text:=vbNewLine & "text3" & vbNewLine
            
            wordApp.Selection.EndKey Unit:=wdStory
        Next
    'finished list of countries
    
    EndRoutine:
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Application.CutCopyMode = False
        wordApp.Visible = True
            
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, April 5, 2019 12:44 PM
  • That's great Hans! It worked. I saw that you changed the first instance of the Content.Text.

    I had used the Content.Text or InsertAfter function instead of the "Selection" function for the other two instances as I couldn't get around the title and table remaining selected and getting overwritten. I didn't think to change that first instance.

    Do you know if it should be possible to assign the Excel table to Word in the same way? Perhaps not as I when I paste to Word, it is no longer as a table but as a (paste special) Picture -Enhanced Metafile.

    kind regards, SB

    Friday, April 5, 2019 2:07 PM
  • If you want to paste the table as a table, use wdPasteHTML instead of wdPasteMetafilePicture

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, April 5, 2019 2:37 PM
  • Thank you Hans. 

    I did previously use "paste, keep source formatting" / paste special: MS Excel worksheet object (or range.pasteexceltable). However, the tables, particular the larger one of the set, don't fit within the page properly and the formatting was not right. The tables have to look a particular way which is why I've ended up having to paste as image though I don't think it is ideal and I will try to find another method. Using the wdPasteHTML will also not paste the correct format.

    A reason why I wanted to find a way around the select, copy and paste was partly because of the unselect problem I was having before and also because randomly the table/text sometimes fails to copy to the clipboard. The code above worked, most of the time I ran it however, sometimes it didn't giving me a run time error (4605). I'm hoping that creating variables for the tables in excel and in word, and then assigning one to the other like for the title text, I could get around the clipboard/pasting issues.

    I need to think about it more and speak to a colleague about setting up a Word template that has the format required of the tables, then somehow open this template instead of an ordinary word document and then paste the table as a template table.

    Anyway, have a nice weekend and thanks for your help.

    kind regards, SB

    Friday, April 5, 2019 4:14 PM