Generation of graphs in excel vba

Paul Karam · August 24th, 2007, 11:42 AM

Hello all,
I have generated data in an excel VBA module and would like to generate a simple graph still in vba then export it to Microsoft Word. Is that at all possible or am I dreaming in technicolour.
Cheers

Paul

bi-lya · August 31st, 2007, 07:47 AM

Generate data in range A1:B10 of Sheet1 and run following code:

Code:

Sub ChartInWord()

    Dim BWord As Object
    Dim BDoc As Object

    'create chart
    Charts.Add
    With Charts(1)
        .ChartType = xlColumnClustered
        .SetSourceData Source:=Sheets("Sheet1").Range("A1:B10"), PlotBy:= _
            xlColumns
        .Location Where:=xlLocationAsObject, Name:="Sheet1"
    End With
    ActiveChart.ChartArea.Copy

    'create document Word
    Set BWord = CreateObject("Word.Application")
    With BWord
        .Visible = True ' may not show
        .documents.Add
        Set BDoc = .documents(1)
        .Selection.Paste
        BDoc.SaveAs Filename:="c:\MyWord.doc"
        .Quit
    End With

    Set BWord = Nothing
    Set BDoc = Nothing

End Sub

Paul Karam · August 31st, 2007, 10:29 AM

Thanks a million Bi-lya, you are a champ.
It works perfectly.
paul

Paul