Excel Charts

Hello,

I am trying to figure out how to create a chart for each line of data on this excel document. I have attached the file with the first chart done manually as an example. I know that some players have more data than others, so if the chart's have some empty spaces on the x axis that is fine, but I would prefer that they do not. The example is at the bottom of sheet one. Sheet 2 is just a duplicate of the data in sheet 1 I messed something up. I am just looking for a quicker way to create individual line charts for each line of data, and then I will be copying and pasting each chart into word, and writing an analysis on each.

Ryan
PER-Number-Atlantic-and-Pacific.xlsx
ryangrippoAsked:
Who is Participating?
Advertise Here
 
SimonConnect With a Mentor Commented:
This does what you need:
Opens a new Word document
Iterates through each row of the active sheet, building a line graph for just the number of years that have dataand copying the graph to the Word document, with the player's name underneath as a caption.
For players with no data, it produces no graph, just a line with their name and " - NO DATA"

See attached Workbook (working example) and sample output document as well as the code below

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

Open in new window

PER-Number-Atlantic-and-Pacific-v1.xlsm
PlayerGraphs-Sample.docx
0
 
ryangrippoAuthor Commented:
Is there anyway you could post the completed word document? I have no idea what to do with the code you just sent me. However, the sample is perfect and exactly what I need.
0
 
SimonCommented:
Hi Ryan.

1. I posted the updated Excel file too, so you could use that to re-run the routine and generate a complete output document.

2. To use the code I posted, you'd need to access the developer tab in Excel, go to the VBE (visual basic editor environment), insert a new module and paste the code in, then switch back to the spreadsheet view (using the Excel icon at top left) and after ensuring that you're on the sheet that you want to generate charts for, choose the macro from the Tools/Macros menu.

3. At your request, I attach the full output file.PlayerGraphs.docx
0
 
ryangrippoAuthor Commented:
This expert went above and beyond the call of duty, and even provided me with a finish word document. 5 stars. Thank you so much!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.