Solved

Excel Charts

Posted on 2015-01-17
4
170 Views
Last Modified: 2015-01-18
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
0
Comment
Question by:ryangrippo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40555656
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
 

Author Comment

by:ryangrippo
ID: 40555869
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
 
LVL 18

Expert Comment

by:Simon
ID: 40556326
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
 

Author Closing Comment

by:ryangrippo
ID: 40556637
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

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question