copy chart from excel to word

W

wongth7

New member
Joined
Jun 5, 2015
Messages
8
Reaction score
0
Points
0
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.

attachment.php




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
View attachment haaa1.xlsm
 

Attachments

  • noScore.jpg
    noScore.jpg
    61.1 KB · Views: 866
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
 
snb said:
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
Click to expand...

hi..thanks for the feedback, but the script is not working at all...nothing happenend after clicked on the macro
 
snb said:
You will have to open Word of course....
Click to expand...

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
 
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.
Click to expand...
 
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.
 
You must log in or register to reply here.
Back
Top