Results 1 to 7 of 7

Thread: copy chart from excel to word

  1. #1

    copy chart from excel to word



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi guys, need some help with VBA here. I have created a macro button on my spreadsheet with the purpose of copying all the charts in my spreadsheet to a new microsoft word file when clicked on. I've managed to find the scripts to do that. However, the next tricky part is, i need the macro to copy only Charts with Bar/line.


    For example, in my attached sample, there are 2 charts in my spreadsheet (namely Age chart & Score chart), lets say if the "Age" chart is showing the Bar and the "Score" chart doesn't show the Bar because data is not available (as shown in the image below) and if i click on the macro button, only the "Age" chart should be copied to microsoft word.





    below are the vba script which i managed to get it from other sites. However, the problem here is, the script only capture graph with "trendline". How do i modify it to capture "bar" or "line"




    Code:
    Dim wd As New Word.Application
    Dim doc As Word.Document
    Dim myChart As ChartObject, i as integer, hasTrendline As Boolean
    Set doc = wd.Documents.Add
    wd.Visible = True
    
    
    For Each myChart In ActiveSheet.ChartObjects
      hasTrendline = False
      For i = 1 To myChart.Chart.SeriesCollection.Count
        If myChart.Chart.SeriesCollection(i).Trendlines.Count > 0 Then hasTrendline = True
      Next i
      If hasTrendline Then
        myChart.Copy
        wd.Selection.PasteSpecial _
        Link:=False, _
        DataType:=wdPasteEnhancedMetafile, _
        Placement:=wdInLine, _
        DisplayAsIcon:=False
      End If
    Next myChart

    attached is the sample workfile
    haaa1.xlsm
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	noScore.jpg 
Views:	801 
Size:	61.1 KB 
ID:	3565  

  2. #2
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    376
    Articles
    0
    Excel Version
    2020
    Code:
    Sub Button1_Click()
      With CreateObject("Word.document")
         For Each ch In ActiveSheet.ChartObjects
             If Join(ch.Chart.SeriesCollection(1).Values, "") <> "" Then
                  ch.Copy
                  .Paragraphs.Last.Range.PasteSpecial
             End If
        Next
      End With
    End Sub

  3. #3
    Quote Originally Posted by snb View Post
    Code:
    Sub Button1_Click()
      With CreateObject("Word.document")
         For Each ch In ActiveSheet.ChartObjects
             If Join(ch.Chart.SeriesCollection(1).Values, "") <> "" Then
                  ch.Copy
                  .Paragraphs.Last.Range.PasteSpecial
             End If
        Next
      End With
    End Sub
    hi..thanks for the feedback, but the script is not working at all...nothing happenend after clicked on the macro

  4. #4
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    376
    Articles
    0
    Excel Version
    2020
    You will have to open Word of course....

  5. #5
    Quote Originally Posted by snb View Post
    You will have to open Word of course....
    hi snb, i tried open word and click on the macro button, however, only one chart is copied over to word eventhough both chart have bars

  6. #6
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    376
    Articles
    0
    Excel Version
    2020
    In the example you posted only 1 chart contained bars because only 1 referred to values to be represented in the chart.

    Lets say if the "Age" chart is showing the Bar and the "Score" chart doesn't show the Bar because data is not available (as shown in the image below) and if i click on the macro button, only the "Age" chart should be copied to microsoft word.

  7. #7
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    I tried it, and snb's code is working perfectly.
    It does exactly what you asked.
    There are no values in your second data column, so there are no Bars on the graph, so the code just copies the first one and then terminates.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •