none
Word macro to copy Excel range RRS feed

  • Question

  • I am trying to do something that should be easy but it's not...Here's what I need to do:

    With a Word document open and an Excel spreadsheet open, I need to run a Word macro to copy a range from the Excel document and paste it into the Word document.   (Ultimately I'm going to be finding a bookmark in the Word document and then doing a PasteSpecial but I can't do that until I can get this worked out.  When I run the following code, the Excel range isn't copied into the Clipboard.  Instead, the Paste command results in the last text I placed in the Clipboard being pasted into my document!  (I can do this all manually without any problem.)

    Please let me know if you have any ideas of why this simple thing isn't working.  Thanks.  Carol

    --------------------------------

    Here's my code (there may be a few extra variable because I've been playing around with this so much):

    Dim xlApp As Excel.Application
    Dim xlSheet As Excel.Worksheet
    Dim wdApp As Word.Application
    Dim xlWB As Excel.Workbook
    Dim i As Integer
    Dim WorkbookToWorkOn As String
    Dim oName


        oName = ActiveDocument.Name
        On Error Resume Next
        ' is Excel open?
        Set xlApp = GetObject(, "Excel.Application")
       
        If Err Then
            ExcelWasNotRunning = True
            MsgBox ("Please open the Financial Workbook for this Project and then start again.")
            Exit Sub
        Else
             xlApp.Visible = True
                    
             For Each xlSheet In xlApp.ActiveWorkbook.Worksheets
                If xlSheet.Name = "Abstract fields" Then
                    xlSheet.Activate
                    Range("A2:B15").Copy
                End If
            Next xlSheet
           
            oName.Activate
            oName.Visible
            
            Selection.Paste
            
        End If
       
        Set xlWB = Nothing
        Set xlApp = Nothing
       
        Exit Sub

    End Sub

    Friday, February 22, 2013 10:01 PM

Answers

  • Change:

    xlSheet.Activate
    Range("A2:B15").Copy

    to:

    xlSheet.Range("A2:B15").Copy

    There is no need to activate the sheet, but you do need to tell the macro which sheet the Range applies to.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by cpnc Sunday, February 24, 2013 1:39 AM
    Saturday, February 23, 2013 8:53 AM

All replies

  • Change:

    xlSheet.Activate
    Range("A2:B15").Copy

    to:

    xlSheet.Range("A2:B15").Copy

    There is no need to activate the sheet, but you do need to tell the macro which sheet the Range applies to.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by cpnc Sunday, February 24, 2013 1:39 AM
    Saturday, February 23, 2013 8:53 AM
  • That's it!!  Thank you so much, Paul.  (I could have sworn that I tried doing that but obviously not)  :-)
    Sunday, February 24, 2013 1:39 AM