Dynamic Chart in Word using VBA???

  • Hi guys,


    Great looking forum, just found it today when trying to get an answer to this question...


    How do you make a graph/chart in word that can be edited using VBA.


    I would like a graph that a user can make changes to the data using vba textboxes, and maybe change the graph type using tickboxes (pie/line/etc)



    -- One step at a time though ;) if somone could just tell me how to actually just change the value of a "microsoft graph chart" datasheet, i will prob be able to work the rest out myself.


    Thanks for any help.


    Regards. :thanx:

  • Re: Dynamic Chart in Word using VBA???


    Thanks for your reply,


    But the example in the search doesnt work for me.


    I used your example macro on this thread: http://www.ozgrid.com/forum/showthread.php?t=32873


    But when i run it, i get an error from this line:


    Code
    Set objMSGraph = ActivePresentation.Slides(1).Shapes(3).OLEFormat.Object.Application

    -- Runtime error '424' Object Required.


    Heres what i did:


    Added the graph object lib to the references in VBA.
    Then made a graph by "insert -> Object -> Microsoft Graph Chart"
    Pasted your example code.
    Ran it. Got the above error.


    I think the problem could be as simple as i dont know how to give the graph a "Name" -- like from "DEFAULT TEXBOX1" to "inputName" type thing.


    Any more help would be great thanks.

  • Re: Dynamic Chart in Word using VBA???


    Yes you will need to modify the example to suit your own document.
    Also the code is for powerpoint and needs changing for use in word.
    [vba]Set objMSGraph = Activedocument.Shapes(1).OLEFormat.Object.Application [/vba]This should work in Word if the graph is the only shape.
    It might be easier if you posted an example of your doc and graph.

    [h4]Cheers
    Andy
    [/h4]

  • Re: Dynamic Chart in Word using VBA???


    Grrr Word is just not as helpful as Excel, still this code functions.[vba]Sub x()

    Dim objMSGraph As Object
    Dim lngRow As Long
    Dim intCol As Integer
    Dim shpTemp As InlineShape

    Set shpTemp = ActiveDocument.InlineShapes(1)
    Set objMSGraph = shpTemp.OLEFormat
    objMSGraph.Activate

    If objMSGraph.Object.Application.PlotBy = xlColumns Then
    intCol = 2
    Do While objMSGraph.Object.Application.DataSheet.Cells(1, intCol) <> ""
    If InStr(objMSGraph.Object.Application.DataSheet.Cells(1, intCol), "Qrt") > 0 Then
    objMSGraph.Object.Application.DataSheet.Cells(1, intCol) = Replace(objMSGraph.Object.Application.DataSheet.Cells(1, intCol), "Qrt", "Quarter")
    End If
    intCol = intCol + 1
    Loop
    Else
    lngRow = 2
    Do While objMSGraph.Object.Application.DataSheet.Cells(lngRow, 1) <> ""
    If InStr(objMSGraph.Object.Application.DataSheet.Cells(lngRow, 1), "East") > 0 Then
    objMSGraph.Object.Application.DataSheet.Cells(lngRow, 1) = Replace(objMSGraph.Object.Application.DataSheet.Cells(lngRow, 1), "East", "Eastern")
    End If
    lngRow = lngRow + 1
    Loop
    End If
    objMSGraph.Object.Application.Update
    objMSGraph.Object.Application.Quit

    Set objMSGraph = Nothing

    End Sub[/vba]

    [h4]Cheers
    Andy
    [/h4]

  • Re: Dynamic Chart in Word using VBA???


    yay! Thanks andy, that works great.


    Now i just have to work it out. :)



    ----
    edit:


    Got it matey, thanks so much, I can now change the differnt lines.


    Just need to work out how to change the graph view from within VBA.




    Anyway, thanks again andy, you've been really helpful.


    Cheers

  • Re: Dynamic Chart in Word using VBA???


    Ok, i kind of get how it works now, but i've got 2 problems really. :?



    I cannot update a chart while the doc. is protected. -- this is somthing i would really need to do. I'm guessing theres a way round this, maybe unprotect the doc with a macro, then protect it again after update.



    And another problem is that i need to update rows without knowing the previous value.


    Right now its:


    Code
    = Replace(objMSGraph.Object.Application.DataSheet.Cells(lngRow, 2), "value1", "value2")


    where "value1" is where what it looks for to replace (if im wrong about this let me know, its your code matey, im just saying what i think i've worked out :thanx: )


    I need a way to change the value of "value1" without knowing it. like "row1 = 10"


    Cheers for your help again andy, you've saved me today :thanx:

  • Re: Dynamic Chart in Word using VBA???


    Yes you will need to unprotect/protect doc with code. Macro recorder should again come to the rescue.


    With the replace problem you will need to check each cell in the data sheet and then do the replace when appropriate.

    [h4]Cheers
    Andy
    [/h4]

  • Re: Dynamic Chart in Word using VBA???


    Is there anyway to do somthing like this?


    Var1 = objMSGraph.Object.Application.DataSheet.Cells(lngRow, 2).text


    Just to get the value in the cell,


    Or is that hoping too much? :roll:



    Cheers.

  • Re: Dynamic Chart in Word using VBA???


    arrr, i spent ages trying that and it didnt work, then when you showed me it, i realised what i was doing, i was putting the line above " lngRow = 2" - d'oh


    Wow, you've been great mate, never had show much help on the net before.


    I tried your advice about using excel to record macros and see how they work, they work great in excel, but moving them over to word, aint to great.


    Like this:


    Code
    ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.ChartArea.Select
        ActiveChart.ChartType = xlPie


    A nice easy way to change the chart from line graph into a piechart.


    Does it work in word? nope. :roll:


    You'd think it would be the same all the way through the office suite.


    Cheers for your help. :thanx:

  • Re: Dynamic Chart in Word using VBA???


    The same but in relation to the product being used.
    Activesheets won't work in Word as Word deals with documents and paragraphs. Same as PowerPoint deals in terms of slides and presentations.


    The macro recorder in excel will give you a clue to the syntax rather than the verbatim code needed.
    [vba] objMSGraph.Object.Application.Chart.ChartType = xlPie[/vba]

    [h4]Cheers
    Andy
    [/h4]

  • Re: Dynamic Chart in Word using VBA???


    haha, you are a legend man, thanks so much.


    I wish i could paypal you a pint, i've been trying to do this for 2 days before i found this forum.


    :thanx: :thanx: :thanx:


    All the best matey.


    Cheers.

Participate now!

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