Creating charts in MSWord with excel macro

nbuk

New Member
Joined
Jun 25, 2010
Messages
30
Currently, I have the following code which creates charts within excel:

Code:
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!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

nbuk

New Member
Joined
Jun 25, 2010
Messages
30
After looking online, I tried adding this before the code, to try to get a new Word file open

Code:
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

New Member
Joined
Jun 25, 2010
Messages
30
I had a minor error in my previous code and my reference was disabled. The below works for opening a word file

Code:
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...
 
Last edited:

nbuk

New Member
Joined
Jun 25, 2010
Messages
30
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

New Member
Joined
Jun 25, 2010
Messages
30
Solution:

Code:
        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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,370
Messages
5,595,774
Members
414,018
Latest member
quang118

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top