Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Charts

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

972 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