Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Charts

Posted on 2015-01-17
4
Medium Priority
?
175 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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

721 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