Solved

Excel Charts

Posted on 2015-01-17
4
161 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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Password on a button in Access 2013 7 33
Field behavior for "locked" form 12 28
DSum for Access 6 42
Error in query expression 3 31
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

943 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now