copy chart from excel to word

[wongth7]

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 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" instead??

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

 

[Worf]

Hi

When you say data not available, does this mean the source range at the sheet will be blank?

 

[NoSparks]

Worf, best ignore this poster.

copy chart from excel to word
copy chart from excel to word
copy chart from excel to word [SOLVED]

 

[wongth7]

the data field is with formula..but sometimes when it did not capture data from the reference table, it will show null value.

(e.g: im using vlookup in the data field, but if the formula did not capture any data from the reference table, the chart will not show line/bar)

 

[Worf]

NoSparks

Thanks for that.

Wongth7

1)Please note this forum rule:

Rule #10:
We prefer that members do not cross-post questions to other forums, but when this does occur members should make clear that they have cross-posted and provide links to the cross-posts. Cross-posted questions that do not comply may be deleted or locked. Serial offenders may have their posting privileges revoked. For a discussion on the issues with cross-posting, see this link: Excelguru Help Site - A message to forum cross posters

2) If you still need help on this, post here at this thread.