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


    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


    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.

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

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

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

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

  • 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


    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


    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)

Participate now!

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