none
Using VBA to copy-paste from Excel to Word RRS feed

  • Question

  • I'd like to create a hotkey macro to copy the selected chart in Excel and to paste it as a windows metafile in word. Googling through various articles I cannot find a solution; many of the solutions propose to create an Excel object with a specific file name identified in the Word VBA code; specifically, I cannot link the macro (hot key) to any named file, but need to use the chart currently selected in Excel and paste-special-picture it to the current location in the open word document (from which I press the hotkey or run the VBA code from the VBA imediate window):

    - in Word Activedocument start a macro  using a hotkey (this I can do)

    - in the macro copy the active chart (or any current selection) in Excel (assuming one instance of Excel running, having one active document with the chart  or any other desired object selected) THIS IS THE PART I'D NEED HELP

    - paste special the clipboard as Windows metafile into Word at the activedocument.selection (this also is ok)

    My problem is that when I CreateObject Excel.application, I still cannot reference any Excel VBA objects. ?

    Friday, December 21, 2012 9:30 PM

Answers

  • Since the Excel workbook should already be open, try something based on:

    Sub GetExcelChart()
    Dim ObjXL As Object, xlWkBk
    ' Test to see if there is a copy of Microsoft Excel already running.
    On Error Resume Next
    Set ObjXL = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
      MsgBox "No Excel Files are open (Excel is not running)"
      Exit Sub
    End If
    For Each xlWkBk In ObjXL.Workbooks
      If xlWkBk.Name = "MyWorkbookName" Then
        xlWkBk.Sheets(1).ChartObjects(1).Copy
        Selection.PasteAndFormat wdChartPicture
      Exit For
      End If
    Next
    Set ObjXL = Nothing
    End Sub

    Simply edit "MyWorkbookName" and the sheet/chartobject references to point to the correct item.

    PS: To access the Excel Object Model while coding, you'd need to set a reference to Excel, via Tools|References, which also allows you to use early binding. The above code uses late binding and does not require the reference to the Excel Object Model to be set beforehand.


    Cheers
    Paul Edstein
    [MS MVP - Word]



    Saturday, December 22, 2012 7:05 AM

All replies

  • Since the Excel workbook should already be open, try something based on:

    Sub GetExcelChart()
    Dim ObjXL As Object, xlWkBk
    ' Test to see if there is a copy of Microsoft Excel already running.
    On Error Resume Next
    Set ObjXL = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
      MsgBox "No Excel Files are open (Excel is not running)"
      Exit Sub
    End If
    For Each xlWkBk In ObjXL.Workbooks
      If xlWkBk.Name = "MyWorkbookName" Then
        xlWkBk.Sheets(1).ChartObjects(1).Copy
        Selection.PasteAndFormat wdChartPicture
      Exit For
      End If
    Next
    Set ObjXL = Nothing
    End Sub

    Simply edit "MyWorkbookName" and the sheet/chartobject references to point to the correct item.

    PS: To access the Excel Object Model while coding, you'd need to set a reference to Excel, via Tools|References, which also allows you to use early binding. The above code uses late binding and does not require the reference to the Excel Object Model to be set beforehand.


    Cheers
    Paul Edstein
    [MS MVP - Word]



    Saturday, December 22, 2012 7:05 AM
  • Thank you Paul, this works - I run the code in Word VBA and access the Excel Application object, e.g. listing the open workbooks! Great! Very much appreciated!
    Wednesday, January 9, 2013 11:46 AM
  • Friday, June 20, 2014 11:08 PM
  • Please don't resurrect old threads. This one was solved nearly 1 1/2 years ago.

    Besides, your video isn't relevant to this thread. Apart from anything else, it is overly complicated, doesn't handle pasting Excel charts into a selection (which is what this thread is about) and provides no useable code.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Friday, June 20, 2014 11:27 PM
  • Many apologies.  Didn't mean to.

    Have a great day.

    Friday, June 20, 2014 11:45 PM