Creating Chart And Pasting Into Word

friendly_fire · Jun 4th 2008

Hi,

I have an excel sheet and I'm searching for a keyword. From that keyword, I can find the data nearby and creating a line chart. How can I paste it into MSWORD?
any help is much appreciated.

so far I have:

Code

Private Function rungraph850(graph850 As Variant) As Variant
    
    If (graph850 = "") Or (graph850 = "False.xls") Then
        Exit Function
    End If
    
    Workbooks.Open (graph850)
    
    Dim wordApp As Word.Application
    Dim wordDoc As Word.Document


    Set wordApp = New Word.Application
    Set wordDoc = wordApp.Documents.Open(ThisWorkbook.Path & "\temp.doc")
    
    Dim xlApp As Excel.Application
    Dim xlchart As Excel.chart
    
    xlchart.ChartType = xlLine
    Dim R As Integer
    
    With Sheets("Sheet1")
        For R = 0 To 390
            
            If (ActiveSheet.Range("A1").Cells(R + 1, "A") Like "*Channel*") Then
                With xlchart
                    xlchart.XValues = (R - 21) / 3 + 128
                    xlchart.Values = graph850.Range("A1").Cells(R + 3, "D").Value
                    xlchart.Name = "850"
                End With
            End If
        Next R
    End With


    
    Application.ThisWorkbook.Activate
    Sheets("Sheet1").Activate
    ActiveSheet.ChartObjects("xlchart").Copy
    
    
    wordApp.Activate
    With wordApp.Selection
    .PasteSpecial DataType:=wdPasteMetafilePicture
    .TypeParagraph
    End With
    
    wordApp.ActiveDocument.Save
    'wordApp.ActiveDocument.Close
    Set wordApp = Nothing
    Set wordDoc = Nothing


    


End Function

Display More

**Andy Pope** · Jun 4th 2008

Re: Creating Chart And Pasting Into Word

You appear to have copy and paste in your code so what exactly is happening that you want to happen?

friendly_fire · Jun 4th 2008

Re: Creating Chart And Pasting Into Word

I want to plot the chart, then copy and paste it into word as a picture. It's not showing up in my temp.doc at all.

**Andy Pope** · Jun 4th 2008

Re: Creating Chart And Pasting Into Word

Forget the copy and paste for a minute does your code even produce a chart?

friendly_fire · Jun 5th 2008

Re: Creating Chart And Pasting Into Word

I'm trying something different now. Instead of copying to Word, I'm attempting to create the chart on sheet2 but the chart comes up blank.

**Andy Pope** · Jun 5th 2008

Re: Creating Chart And Pasting Into Word

If you want more help you will need to post the "something different".

friendly_fire · Jun 5th 2008

Re: Creating Chart And Pasting Into Word

This is what I have currently. I want to add numbers 128 to 251 as xvalues in the chart and add the numbers that I find in the excel as the yvalues in the chart. The values for yvalues are actually spaced 3 rows apart.

I've also tried to create an array from 128 to 251 and store the data in those indices. Is there a way to set xvalues as the indices and yvalues as the value stored in the array?
From what I have searched so far, I've only seen something like .xvalues = "Sheet1!R1C1:R31C1"

for example:
array850(128) = 10
array850(129) = 14
array850(130) = 13
.....
and
xvalues = 128, 129, 130...
yvalues = 10, 14, 13...

Code

Private Function rungraph850(graph850 As Variant) As Variant 


    
    Workbooks.Open (graph850)
    'Dim array850(128 To 251) As Long
    Dim xlApp As Excel.Chart
    Dim xlchart As Excel.ChartObjects
    Dim mychart As Excel.ChartObject
    
    
    'Set xlchart = Charts.Add
    'xlchart.ChartType = xlLine
    Dim R As Integer


    With Sheets("Sheet1")
        For R = 0 To 40
            'xlchart.SeriesCollection(1).XValues = 128 to 251 'this doesnt work
            If (ActiveSheet.Range("A1").Cells(R + 1, "D") < 0) Then
               
                ActiveChart.SeriesCollection(1).XValues = (R - 21) / 3 + 128 'doesnt work either
                'xlchart.SeriesCollection(1).Values = ActiveSheet.Range("A1").Cells(R + 1, "D").Value
            End If


        Next R
       ' xlchart.SeriesCollection(1).Name = "850"
    End With
    


    
    'ActiveWorkbook.Close
    
    'Set xlchart = Nothing




End Sub

Display More

**Andy Pope** · Jun 6th 2008

Re: Creating Chart And Pasting Into Word

This simple code will create a scatter chart using a small array for x and y values.

[vba]
Sub X()

Dim vntArrayX(1 To 3) As Variant
Dim vntArrayY(1 To 3) As Variant
Dim objChart As Chart

vntArrayX(1) = 128
vntArrayX(2) = 129
vntArrayX(3) = 130

vntArrayY(1) = 10
vntArrayY(2) = 14
vntArrayY(3) = 13

Set objChart = ActiveSheet.ChartObjects.Add(1, 1, 300, 200).Chart
With objChart
.ChartType = xlXYScatterLines
With .SeriesCollection.NewSeries
.Name = "XYZ"
.Values = vntArrayY
.XValues = vntArrayX
End With
End With

End Sub
[/vba]

When you then select the series you will see this is it's formula

=SERIES("XYZ",{128,129,130},{10,14,13},1)

The series formula has a maximum length of 1023 characters, so if your array of values is too long you will not be able to use fixed values.

Your current code examples are very confusing as you appear to be declaring objects for referencing chart objects but never using them. I don't know whether the charts already exist or you are creating them.

friendly_fire · Jun 11th 2008

Re: Creating Chart And Pasting Into Word

Thank you for your response Andy. I wasn't able to get back to this assignment til now. I used your example and now I have

Code

Private Function rungraph850(graph850 As Variant) As Variant
    
    If (graph850 = "") Or (graph850 = "False.xls") Then
        Exit Function
    End If
    
    Workbooks.Open (graph850)
    
    Dim vntArrayX(124) As Integer
    Dim vntArrayY(124) As Integer
    
    'Dim objChart As chart
    
    
    'Set objChart = ActiveSheet.ChartObjects.Add(1, 1, 300, 200).chart
    Dim H As Integer


    With Sheets("Sheet1")
        For H = 1 To 392
            If (ActiveSheet.Range("A1").Cells(H, "D") < 0) Then
     
                vntArrayX((H - 23) / 3 + 1) = ((H - 23) / 3 + 128)
                vntArrayY((H - 23) / 3 + 1) = ActiveSheet.Range("A1").Cells(H, "D").Value
                'MsgBox (vntArrayX((H - 23) / 3 + 1))
                'MsgBox (vntArrayY((H - 23) / 3 + 1))
            End If


        Next H
         
        Dim objChart As chart
        Set objChart = ActiveSheet.ChartObjects.Add(1, 1, 600, 300).chart
        With objChart
            .ChartType = xlXYScatterLines
            With .SeriesCollection.NewSeries
                
                .XValues = vntArrayX  'error here
                .Values = vntArrayY


            End With
        End With
    End With


    'ActiveWorkbook.Close
    
    'Set objChart = Nothing
    


End Function

Display More

I'm getting an error at xvalues: "Unable to set the XValues property of the Series class"
What's strange is that my array appears to be correct. I did a msgbox on x and y values and they are storing the values I want. However the chart won't store them.

as always, any help is much appreciated.

**Andy Pope** · Jun 12th 2008

Re: Creating Chart And Pasting Into Word

Are all elements of your array being populated?
If the length of the series formula going to be too long?

If you can not answer these questions can you post an example workbook.

friendly_fire · Jun 12th 2008

Re: Creating Chart And Pasting Into Word

Yes, all elements in x and y are populated. vntArrayX and vntArrayY arrays contain the values that I want. For this graph it will be 124 elements in x and y for the series. I must make another function that creates another graph for 299 elements in the series. Is there a limit ?

If asking the chart to store whatever is in my arrays won't work, could I add my values directly to the xvalues/yvalues in my loop?

Like:
xvalues.add(blah blah)
yvalues.add(blah blah)

Thanks!

**Andy Pope** · Jun 13th 2008

Re: Creating Chart And Pasting Into Word

No your can not add values like that.

I told you the limit is determined by the length of the Series Formula.

Modify your code so it only populates 5 values. Assuming the code works and the chart is generated, select the series and via the formula bar copy and post here the formula.

By the way, what is wrong with using cells to hold the numbers?

friendly_fire · Jun 15th 2008

Re: Creating Chart And Pasting Into Word

It wouldn't accept an array of any size. I still get the error. I would prefer to add the xvalues and yvalues straight from the cells but I'm not sure how to?

**Andy Pope** · Jun 16th 2008

Re: Creating Chart And Pasting Into Word

Does this simple routine work for you?

[vba]
Sub Test()

Dim vntArrayX(1 To 10) As Integer
Dim vntArrayY(1 To 10) As Integer
Dim H As Integer
Dim objChart As Chart

For H = 1 To 10
vntArrayX(H) = H
vntArrayY(H) = H * 5
Next H

Set objChart = ActiveSheet.ChartObjects.Add(1, 1, 600, 300).Chart
With objChart
.ChartType = xlXYScatterLines
With .SeriesCollection.NewSeries
.XValues = vntArrayX
.Values = vntArrayY
End With
End With

End Sub
[/vba]

friendly_fire · Jun 16th 2008

Re: Creating Chart And Pasting Into Word

Hi Andy,
Yes it works.. but up to 64 points only, 1 to 64. Anything 65+ gives run-time error '1004'
application-defined or object-defined error.
Do you know of a way to add to x and yvalues like a queue, while I loop through my spreadsheet? Instead of creating an array, this was what I tried to do originally, but was having difficulty with...

**Andy Pope** · Jun 16th 2008

Re: Creating Chart And Pasting Into Word

I don't know what you mean by queue.

Sorry, why can you not use cells, seeing as dealing with more than a few simple numbers causes errors?

friendly_fire · Jun 16th 2008

Re: Creating Chart And Pasting Into Word

That's what I would like to do, is by using the cells. Adding from the cells directly to the xvalues and yvalues of the chart but I'm not sure how to.
I know how to do it by specifying a range but since my data isn't one line after another, they are spaced 3 lines apart, that is giving me the trouble.

**Andy Pope** · Jun 17th 2008

Re: Creating Chart And Pasting Into Word

Keep it simple.
Create a copy of your chart data in cells with no spaces in between.

friendly_fire · Jun 17th 2008

Re: Creating Chart And Pasting Into Word

I am using a software that generates excels for tests that I run. And there are 300 data lines for each file (spaced 3 lines apart). I would simplify it if I could but this is the main problem. I would be surprised if there is no way to chart this data..
Thanks again Andy.

**Andy Pope** · Jun 17th 2008

Re: Creating Chart And Pasting Into Word

This example, as per that of Slide's problem, contains 2 functions that build an array from a range.
This 2 functions are used in the named ranges which the chart uses as it's data source.

[vba]
Function MakeValuesX(Rng As Range) As Variant

Dim lngNItems As Long
Dim lngItem As Long
Dim rngCell As Range
Dim vntData() As Variant

For Each rngCell In Rng.Cells
If Len(rngCell.Value) > 0 Then
lngItem = lngItem + 1
If lngItem > lngNItems Then
lngNItems = lngItem
ReDim Preserve vntData(1 To lngNItems)
End If
vntData(lngItem) = rngCell.Value
End If
Next
MakeValuesX = vntData

End Function
Function MakeValuesY(Rng As Range) As Variant

Dim lngNItems As Long
Dim lngItem As Long
Dim rngCell As Range
Dim vntData() As Variant

For Each rngCell In Rng.Cells
If Len(rngCell.Value) > 0 Then
lngItem = lngItem + 1
If lngItem > lngNItems Then
lngNItems = lngItem
ReDim Preserve vntData(1 To lngNItems)
End If
vntData(lngItem) = rngCell.Value
End If
Next
MakeValuesY = vntData

End Function
[/vba]

Named range

ChtDataX: =MakeValuesX(Sheet1!$A$2:$A$16)
ChtDataY: =MakeValuesY(Sheet1!$B$2:$B$16)

Charts series formula is

=SERIES(,'92849.xls'!ChtDataX,'92849.xls'!ChtDataY,1)

Creating Chart And Pasting Into Word

Participate now!

Share