none
Using WORD VBA to Copy EXCEL Charts to the current WORD document RRS feed

  • Question

  • I have an EXCEL workbook with about 150 graphs in it. The workbook contains macros that have been through the QA process, so no changes may be made to the macros in the workbook. So, I would like to write some VBA code that will copy all of the graphs into the word document. I slightly modified another post to get the following VBA macro.

    Sub copyChart()
        Dim exApp As Excel.Application
        Dim originalExl As Excel.Workbook
        Dim wdDoc As Document
        Dim myPath As String, myPath1 As String, myPath2 As String
        Dim rng As Range

        myPath = ThisDocument.Path
        myPath1 = myPath & "\Blank FSPEED Analysis v1.0d.xlsm"
        myPath2 = myPath & "\test.docx"

        'disable screen update
    '    Application.ScreenUpdating = False

        Set exApp = CreateObject("Excel.Application")
        Set originalExl = exApp.Workbooks.Open(myPath1)
        exApp.Visible = True
    '    Set wdDoc = Documents.Add
     
        originalExl.Worksheets("HOP").SeriesCollection(1).Copy  **** STOPS HERE
        wdDoc.Content.PasteSpecial DataType:=wdPasteMetafilePicture

        Set rng = wdDoc.Content
        rng.Collapse Direction:=wdCollapseEnd
        rng.InsertParagraphAfter
        Set rng = wdDoc.Content
        rng.Collapse Direction:=wdCollapseEnd

        originalExl.Worksheets("HOP").Range("AO19").Copy
        rng.PasteSpecial DataType:=wdPasteEnhancedMetafile

        wdDoc.SaveAs myPath3

        'close the files
        wdDoc.Close
        originalExl.Close SaveChanges:=False
        tempExl.Close SaveChanges:=False
        exApp.Quit
    End Sub

    It stops at the indicatated line with message "Objrct does not support this property or method."

    Not usre what to do. This is obviously for one chart only, but if this works I will work on loops to get them all.

    Can anyone help?

    Tuesday, October 22, 2013 12:25 PM

Answers

  • Try this version:

    Sub CopyChart()
        Dim objXL As Object ' Excel.Application
        Dim objWBook As Object ' Excel.Workbook
        Dim objWSheet As Object ' Excel.Worksheet
        Dim objChart As Object ' Excel.ChartObject
        Dim wdDoc As Document
        Dim myPath As String, myPath1 As String, myPath2 As String
        Dim rng As Range
    
        myPath = ThisDocument.Path
        myPath1 = myPath & "\Blank FSPEED Analysis v1.0d.xlsm"
        myPath2 = myPath & "\test.docx"
    
        'disable screen update
        Application.ScreenUpdating = False
    
        Set objXL = CreateObject("Excel.Application")
        Set objWBook = objXL.Workbooks.Open(myPath1)
        Set objWSheet = objWBook.Worksheets("HOP")
        Set wdDoc = Documents.Add
    
        For Each objChart In objWSheet.ChartObjects
            objChart.Copy
            Set rng = wdDoc.Content
            rng.Collapse Direction:=wdCollapseEnd
            rng.PasteSpecial DataType:=wdPasteEnhancedMetafile
            Set rng = wdDoc.Content
            rng.InsertParagraphAfter
        Next objChart
    
        Set rng = wdDoc.Content
        rng.Collapse Direction:=wdCollapseEnd
    
        objWSheet.Range("AO19").Copy
        rng.PasteSpecial DataType:=wdPasteEnhancedMetafile
    
        wdDoc.SaveAs myPath2
    
        'close the file
        wdDoc.Close
        objWBook.Close SaveChanges:=False
        objXL.Quit
        Application.ScreenUpdating = True
    End Sub


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

    Tuesday, October 22, 2013 12:48 PM
  • You can solve the problem of overlapping charts by changing both lines

    rng.PasteSpecial DataType:=wdPasteEnhancedMetafile

    to

    rng.PasteSpecial Placement:=wdInLine, DataType:=wdPasteEnhancedMetafile

    (The VBA help says that the wdInline is the default, but apparently that's not true)


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

    • Marked as answer by MOSCRay Tuesday, October 22, 2013 5:45 PM
    Tuesday, October 22, 2013 2:59 PM

All replies

  • Try this version:

    Sub CopyChart()
        Dim objXL As Object ' Excel.Application
        Dim objWBook As Object ' Excel.Workbook
        Dim objWSheet As Object ' Excel.Worksheet
        Dim objChart As Object ' Excel.ChartObject
        Dim wdDoc As Document
        Dim myPath As String, myPath1 As String, myPath2 As String
        Dim rng As Range
    
        myPath = ThisDocument.Path
        myPath1 = myPath & "\Blank FSPEED Analysis v1.0d.xlsm"
        myPath2 = myPath & "\test.docx"
    
        'disable screen update
        Application.ScreenUpdating = False
    
        Set objXL = CreateObject("Excel.Application")
        Set objWBook = objXL.Workbooks.Open(myPath1)
        Set objWSheet = objWBook.Worksheets("HOP")
        Set wdDoc = Documents.Add
    
        For Each objChart In objWSheet.ChartObjects
            objChart.Copy
            Set rng = wdDoc.Content
            rng.Collapse Direction:=wdCollapseEnd
            rng.PasteSpecial DataType:=wdPasteEnhancedMetafile
            Set rng = wdDoc.Content
            rng.InsertParagraphAfter
        Next objChart
    
        Set rng = wdDoc.Content
        rng.Collapse Direction:=wdCollapseEnd
    
        objWSheet.Range("AO19").Copy
        rng.PasteSpecial DataType:=wdPasteEnhancedMetafile
    
        wdDoc.SaveAs myPath2
    
        'close the file
        wdDoc.Close
        objWBook.Close SaveChanges:=False
        objXL.Quit
        Application.ScreenUpdating = True
    End Sub


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

    Tuesday, October 22, 2013 12:48 PM
  • Stops at "objChart.Copy" says "The specified dimension is not valid for the current chart type."

    Changed it to "objChart.CopyPicture" and it ran, but the charts overlap each other. Will work on this, but the real problemis solved.

    Thank You!!

    Tuesday, October 22, 2013 1:37 PM
  • You can solve the problem of overlapping charts by changing both lines

    rng.PasteSpecial DataType:=wdPasteEnhancedMetafile

    to

    rng.PasteSpecial Placement:=wdInLine, DataType:=wdPasteEnhancedMetafile

    (The VBA help says that the wdInline is the default, but apparently that's not true)


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

    • Marked as answer by MOSCRay Tuesday, October 22, 2013 5:45 PM
    Tuesday, October 22, 2013 2:59 PM
  • Thamks, that works!!
    Tuesday, October 22, 2013 5:45 PM