Excel Charts

Sub MakeLineChartsInNewWordDoc()
Dim rng As Range
Dim rngChart As Range
Dim intRowsSkipped As Integer
Dim intRowsProcessed As Integer
Dim c As Range
Dim intRowCount As Integer

If MsgBox("This macro will create a graph in a new Word document for each line in the spreadsheet that has data.", vbOKCancel) = vbCancel Then Exit Sub Else
Application.DisplayStatusBar = True
Set rng = Intersect(ActiveSheet.Columns(3), ActiveSheet.UsedRange)
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
Debug.Print rng.Address
intRowCount = rng.Rows.Count

Dim objWord As Object
Dim doc As Object
On Error Resume Next
Set objWord = GetObject(, "word.application") 'gives error 429 if Word is not open
If Err = 429 Then
    Set objWord = CreateObject("word.application") 'creates a Word application
    Err.Clear
End If
On Error GoTo 0
objWord.Visible = True
Set doc = objWord.Documents.Add

'Application.ScreenUpdating = False

For Each c In rng
    If c.Offset(0, 1).Value = "" Then
        intRowsSkipped = intRowsSkipped + 1
        With objWord
            .Selection.TypeParagraph
            .Selection.TypeText Text:=c.Value & " - NO DATA TO CHART"
            .Selection.TypeParagraph
            .Selection.TypeParagraph
        End With
        GoTo NextRow
    End If
    
    Set rngChart = Range(c, c.End(xlToRight))
    Debug.Print rngChart.Address, c.Value
    
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range(ActiveSheet.Name & "!" & rngChart.Address)
    ActiveChart.ChartType = xlLine
    ActiveChart.PlotBy = xlRows
    ActiveChart.SeriesCollection(1).MarkerStyle = xlMarkerStyleCircle
    ActiveChart.CopyPicture
    With objWord
        .Selection.PasteAndFormat (13) '13=wdChartPicture
        .Selection.TypeParagraph
        .Selection.TypeText Text:=c.Value
        .Selection.TypeParagraph
        .Selection.TypeParagraph
    End With
    intRowsProcessed = intRowsProcessed + 1
    'Remove the chart object
    ActiveChart.Parent.Delete
    Application.StatusBar = intRowsProcessed + intRowsSkipped & " of " & intRowCount & " completed"
NextRow:
Next
Set objWord = Nothing
Application.StatusBar = False
MsgBox "Finished - " & intRowsProcessed & " rows charted, " & intRowsSkipped & " rows with no data skipped"
End Sub

Select all Open in new window