Importing Excel Chart to Word RRS feed

  • Question

  • I need to import a chart from Excel into Word, and I can't figure out what is wrong with this code - it opens the Excel file, goes to the correct sheet, but it doesn't insert the chart into the Document!  Can anyone help?  I'm using Office 2011 on a Mac.

    Private Sub My_Chart()
    Dim xlapp As Object
    Dim xlbook As Object
    Dim xlsheet As Object
    Dim xlchart As Object
    Dim Excelwasnotrunning As Boolean
    On Error Resume Next
    Set xlapp = GetObject(, "Excel.Application")
    If Err Then
    Excelwasnotrunning = True
    Set xlapp = CreateObject("Excel.Application")
    End If
    On Error GoTo ErrMsg
    With xlapp
    Set xlbook = .Workbooks.Open("Macintosh HD:Users:...Data.xlsx")
    Set xlsheet = xlbook.Worksheets("myChart")
    Set xlchart = xlsheet.ChartObjects(1)
    End With
    xlchart.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    If Excelwasnotrunning = True Then
    End If
    Set xlapp = Nothing
    Set xlsheet = Nothing
    Set xlchart = Nothing
    If Err.Number > 0 Then
    MsgBox Err.Number & vbCr & Err.Description
    Exit Sub
    End If
    End Sub

    Sunday, December 16, 2012 1:28 AM

All replies

  • I do not have Mac but that is a way to copy Chart do Word doc.

    Private Sub CopyChartToWordDocFromXL()
        Dim currentChart As Chart, Fname$
        Dim sPatch$: sPatch = Application.DefaultFilePath    'or another path
        Set currentChart = ActiveSheet.ChartObjects(1).Chart 'no of char in activesheet
            currentChart.CopyPicture xlScreen
        Dim WDApp As Object
        Set WDApp = CreateObject("Word.Application")
            WDApp.Visible = True
            WDApp.Documents.Open FileName:=sPatch & "\tymczasowy.docx" 'your doc file
            'Select your place
        Set WDApp = Nothing
    End Sub

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Sunday, December 16, 2012 6:29 PM
  • I just tried your code on Mac Office 2011 and it works OK here. So I suppose it is important to re-check obvious stuff.

    e.g. if you haven't referenced the Excel object model via Tools->References. If you haven't, "xlScreen" and "xlPicture" will be 0 - I haven't looked up what that means, but nothing is pasted, and in fact I don't think there is anything to paste.

    Peter Jamieson

    Saturday, December 22, 2012 11:45 AM