Creating charts in MSWord with excel macro

[nbuk]

Currently, I have the following code which creates charts within excel:

With Sheet1.ChartObjects.Add(Left:=390, Width:=375, Top:=25, Height:=225)
        .chart.SetSourceData Source:=Sheet22.Range("A1:L2")
        .chart.ChartType = xlColumnClustered
        .chart.ChartTitle.Characters.Text = "REVERB1 Downtime " & StartDate & "-" & EndDate
        .chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Minutes"
        .chart.Legend.delete
        End With
        
        With Sheet1.ChartObjects.Add(Left:=775, Width:=375, Top:=25, Height:=225)
        .chart.SetSourceData Source:=Sheet22.Range("A3:L4")
        .chart.ChartType = xlColumnClustered
        .chart.ChartTitle.Characters.Text = "REVERB2 Downtime " & StartDate & "-" & EndDate
        .chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Minutes"
        .chart.Legend.delete
        End With
        
        With Sheet1.ChartObjects.Add(Left:=390, Width:=375, Top:=260, Height:=225)
        .chart.SetSourceData Source:=Sheet22.Range("A5:L6,A8:L8")
        .chart.ChartType = xlColumnClustered
        .chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Minutes"
        .chart.HasTitle = True
        .chart.ChartTitle.Characters.Text = "REVERBs Downtime " & StartDate & "-" & EndDate
        End With

I want to modify the code so that the charts are not created in Sheet1, but in a Word file. I want to open a blank new word file and create all three graphs there.

Any suggestions?

Thanks!

 

[nbuk]

After looking online, I tried adding this before the code, to try to get a new Word file open

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim i As Integer
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Add

I got an error on the first line:

"Compile Error: User-defined type not defined"

 

[nbuk]

I had a minor error in my previous code and my reference was disabled. The below works for opening a word file

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Add

My next issue is how to actually create my charts in the file. I have tried replacing "sheet1" in my original chart code with "wrdApp" and "wrdDoc" with no success as each gave an error.

wrdApp error: "Compile Error: Method or data member not found"
'chart.objects' section was highlighter

docApp error: "Run-time error '438': Object doesn't support this property or method"
The whole first line of the chart code was highlighted

Help please...

 

[nbuk]

I have gone through several books but I am still unsure as to how I should proceed. I am sure that I am near a solution, I just can't reach it.

Any suggestions are greatly appreciated.

 

[nbuk]

Solution:

        Dim wrdApp As word.Application
        Dim wrdDoc As word.Document
        Set wrdApp = CreateObject("Word.Application")
        wrdApp.Visible = True
        Set wrdDoc = wrdApp.Documents.Add
        
        With Sheet1.ChartObjects.Add(Left:=390, Width:=375, Top:=25, Height:=225)
        .chart.SetSourceData Source:=Sheet22.Range("A1:L2")
        .chart.ChartType = xlColumnClustered
        .chart.ChartTitle.Characters.Text = "REVERB1 Downtime " & StartDate & "-" & EndDate
        .chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Minutes"
        .chart.Legend.Delete
        .chart.CopyPicture Appearance:=xlScreen, Format:=xlPicture
        End With
        
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
        Placement:=wdInLine, DisplayAsIcon:=False
        
        With Sheet1.ChartObjects.Add(Left:=775, Width:=375, Top:=25, Height:=225)
        .chart.SetSourceData Source:=Sheet22.Range("A3:L4")
        .chart.ChartType = xlColumnClustered
        .chart.ChartTitle.Characters.Text = "REVERB2 Downtime " & StartDate & "-" & EndDate
        .chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Minutes"
        .chart.Legend.Delete
        .chart.CopyPicture Appearance:=xlScreen, Format:=xlPicture
        End With
        
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
        Placement:=wdInLine, DisplayAsIcon:=False
        
        With Sheet1.ChartObjects.Add(Left:=390, Width:=375, Top:=260, Height:=225)
        .chart.SetSourceData Source:=Sheet22.Range("A5:L6,A8:L8")
        .chart.ChartType = xlColumnClustered
        .chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Minutes"
        .chart.HasTitle = True
        .chart.ChartTitle.Characters.Text = "REVERBs Downtime " & StartDate & "-" & EndDate
        .chart.CopyPicture Appearance:=xlScreen, Format:=xlPicture
        End With
        
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
        Placement:=wdInLine, DisplayAsIcon:=False
        
        Set wrdDoc = Nothing
        Set wrdApp = Nothing
        
        'Delete charts
        Dim chtObj As ChartObject

        For Each chtObj In Sheet1.ChartObjects
            chtObj.Delete
        Next

Hope this helps someone