 # Creating Chart And Pasting Into Word

• 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:

• 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?

[h4]Cheers
Andy
[/h4]

• 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.

• Re: Creating Chart And Pasting Into Word

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

[h4]Cheers
Andy
[/h4]

• 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.

• Re: Creating Chart And Pasting Into Word

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

[h4]Cheers
Andy
[/h4]

• 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...

• 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.

[h4]Cheers
Andy
[/h4]

• 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

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.

• 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.

[h4]Cheers
Andy
[/h4]

• 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:

Thanks!

• Re: Creating Chart And Pasting Into Word

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?

[h4]Cheers
Andy
[/h4]

• 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?

• 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]

[h4]Cheers
Andy
[/h4]

• 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...

• 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?

[h4]Cheers
Andy
[/h4]

• 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.

• Re: Creating Chart And Pasting Into Word

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

[h4]Cheers
Andy
[/h4]

• 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.

• 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)

[h4]Cheers
Andy
[/h4]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!