Consulting

Results 1 to 3 of 3

Thread: Generating Excel Chart in Word

  1. #1
    VBAX Regular
    Joined
    Aug 2012
    Location
    France
    Posts
    13
    Location

    Generating Excel Chart in Word

    Hi

    I am trying to add a excel chart from a word template which is causing me a lot of frustration. Code seems to be simple enough but for some reason I just cannot make it work. I got serveral problems:

    1: cannot edit ChartArea.Width/Height. I get following message "Object doesn't support this function". The PlotArea.Width/Height I am able to set.
    2: I am not able to set the series using cell row and column no reference ie. Range(cells(x,y),Cells(z,y). I really need this but for now I am limited to using following: .SeriesCollection(i).Values = "='Sheet1'!$N$2:$N$52"
    3: Setting specific color to each line is a problem too:
    - I have tried using: .SeriesCollection(i).Border.ColorIndex = xx (which seem to set all all lines the same color
    - nothing happens on this code: .SeriesCollection(i).Interior.Color = RGB(xxx, yyy, zzz)

    Any feedback will be much appriciated...

    below is the code I am using:
    -----------------------------------------------------------------
    Sub createchart()

    Dim WellSecurityChart As Chart
    Dim ChartWorkSheet As Excel.Worksheet

    Set WellSecurityChart = ActiveDocument.Shapes.AddChart.Chart
    Set ChartWorkSheet = WellSecurityChart.ChartData.Workbook.WorkSheets(1)

    With WellSecurityChart
    .ChartType = xlXYScatterLinesNoMarkers
    .PlotArea.Width = 300 ' number not important now
    .PlotArea.Height = 400 ' number not important now
    .ChartArea.Width = 400 ' number not important now
    .ChartArea.Height = 500 ' number not important now
    .SeriesCollection(3).Delete 'deleting default series as automatically setup on creation of chart
    .SeriesCollection(2).Delete
    .SeriesCollection(1).Delete
    '....more setup
    .SeriesCollection.NewSeries
    With .SeriesCollection(1)
    .Name = "='Sheet1'!$F$1"
    .XValues = "='Sheet1'!$H$2:H$52" ' works but I would prefer to use Range(Cells(x,y,),(Cells(z,i))
    .Values = "='Sheet1'!$F$2:$F$52" ' data added elsewhere in code
    '.Border.ColorIndex = 3 'set all lines to red
    '.Interior.Color = RGB(255, 0, 0) ' does not work
    '.Format.Line.ForeColor.RGB = vbRed 'does not work
    End With
    '......adding more series....
    End With
    end sub
    Last edited by AAhrenst; 08-25-2012 at 10:03 AM. Reason: not completed

  2. #2
    MS Excel MVP VBAX Tutor
    Joined
    Mar 2005
    Posts
    246
    Location
    Here are some ideas.

    1. Chart area is fixed by the size of the containing shape. You need to then adjust the size of the parent of the chart.

    [VBA]With WellSecurityChart
    .Parent.Width = 400
    .Parent..Height = 500[/VBA]

    2. You need to reference ranges properly:

    [VBA].XValues = ChartWorkSheet.Range(ChartWorkSheet.Cells(2, 8), ChartWorkSheet.Cells(52, 8))[/VBA]

    3. When Office 2007 came out, we gained a lot of colors (compared to the old Excel palette of 56 color indexes), and we lost a lot of control over the colors. The color object model is obscure, erratic, and buggy, and often the old syntax must be used. At least in 2010 you can record a macro and use it to guess at the required syntax, but you still need to unhide deprecated elements in the object browser and hack through the jungle.

    I don't know why ColorIndex didn't work, except that 2007/2010 don't reliably use ColorIndex. In an XY chart, there's no Interior property. As soon as you try to use .Format.Line, strange and wonderful things start to happen. So...

    [VBA]With .SeriesCollection(1)
    ' both of these work:
    .Border.Color = vbRed
    .Border.Color = RGB(255, 0, 0)[/VBA]
    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com
    _______

  3. #3
    VBAX Regular
    Joined
    Aug 2012
    Location
    France
    Posts
    13
    Location
    Thanks for the reply Jon

    I managed to fix my problems with inserting the excel graphs into word.
    Instead of inserting them directly into word I generate the graphs in excel and manipulate same before they are copied into word. To me this was a lot easier (and eaiser to lookup correct code) and it also sorted out the color problem.
    Since my chart took almost 45 sec to update (even with hidden excel object) while importing DDE variables, I decided not to populate the excel sheet with data but instead used arrays. This did cut the time down to about 15 sec.

    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •