Problem solve Get help with specific problems with your technologies, process and projects.

Inserting an Excel chart into a Word document

Build a chart in Excel using only code.

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 
 = "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
= "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" 

    xlsBook.SaveAs(Application.StartupPath & "MyNewChart.xls") 
End With 

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 last published in March 2004

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.