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

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

SearchSOA

TheServerSide

SearchCloudApplications

Close