Tip

Inserting an Excel chart into a Word document

Please let other users know how useful this tip is by rating it at the bottom of the page. Do you have a tip or code of your own you'd like to share? Submit it here.

With this code you can build a chart in Excel using only code. Excel will open, save the file, create a new Word document and insert the Excel chart into the Word document without the user ever seeing it. After it's done, the Excel file is erased from the hard drive. 

  
Dim xlsapp As New Excel.Application() 
Dim xlsBook As Excel.Workbook 
Dim xlsdoc As Excel.Worksheet 
Dim xlschart As Excel.Chart 

xlsBook = xlsapp.Workbooks.Add 
xlsdoc = xlsBook.Worksheets.Add 
xlschart = xlsBook.Charts.Add 

'Here we are just filling some cells with values to build our chart 

With xlsdoc 
    .Range("A4").Value = "1000mhz" 
    .Range("A5").Value = "2000mhz" 
    .Range("A6").Value = "3000mhz" 
    .Range("A7").Value = "4000mhz" 
    .Range("A8").Value = "5000mhz" 
    .Range("B4").Value = "30" 
    .Range("B5").Value = "45" 
    .Range("B6").Value = "40" 
    .Range("B7").Value = "50" 
    .Range("B8").Value = "20" 

    .Range("C4").Value = "40" 
    .Range("C5").Value = "40" 
    .Range("C6").Value = "50" 
    .Range("C7").Value = "30" 
    .Range("C8").Value = "30" 
End With 

This is where the fun begins 
Dim xlsAxisCategory, xlsAxisValue As Excel.Axes 
Dim xlsSerie As Excel.SeriesCollection = xlschart.SeriesCollection

With xlschart 
    .ChartType = Excel.XlChartType.xlLine 'Here we are setting the type of
chart we want 
    .SetSourceData(xlsdoc.Range("A4:C8")) 'This is the range from where the
chart will be built 
   
    xlsAxisCategory = .Axes(, Excel.XlAxisGroup.xlPrimary) 
    xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True 
    xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text
 = "Megahertz" 
    xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).HasMajorGridlines = False
    xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).HasMinorGridlines = False 
    xlsAxisValue = .Axes(, Excel.XlAxisGroup.xlPrimary) 
    xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
    xlsAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text 
= "Decibels" 
    xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasMajorGridlines = False 
    xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasMinorGridlines = False 

    .PlotArea.Interior.ColorIndex = 2 
    .ChartArea.Interior.ColorIndex = 2 
    .ChartArea.Interior.PatternColorIndex = 1 

    xlsSerie.Item(1).Name = "First set of values" 
    xlsSerie.Item(2).Name = "Second set of values" 

    .Location(Excel.XlChartLocation.xlLocationAsNewSheet) 
    xlsBook.SaveAs(Application.StartupPath & "MyNewChart.xls") 
End With 

xlsapp.Quit() 
xlsapp = Nothing 

'At the top of the form: 

Imports System.IO 

' In the "Word File" Button Click event: 
Dim app As New Word.Application() 
Dim doc As New Word.Document() 

doc = app.Documents.Add() 
doc.Shapes.AddOLEObject(, Application.StartupPath 
& "\MyNewChart.xls", , , , , , , "300", "380", "320") 
app.Visible = True 

File.Delete(Application.StartupPath & "MyNewChart.xls")

This was first published in March 2004

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

Back to top