export excel chart

  • trying to export an excel 2000 chart to a word doc using VB. I've tried everything. All the code I found on the web won't work. I keep getting a runtime error stating that the export did not work. Here is an example of the code

    ActiveSheet.ChartObjects("Chart 1").Activate
    Pic = ActiveChart.Export("c:\BOFFEO.gif", FilterName:="GIF")

  • Hi and welcome to the board :)

    Here is a working sample how we can paste an embedded chart into a Word-document:

    Option Explicit

    Sub Export_Chart_Word()
    'We need to set a reference to the MS Word Library x.x
    'via the Tools | Reference in the VB-editor
    Dim wkBook As Workbook
    Dim wsSheet As Worksheet
    Dim chObject As ChartObject
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim BMRange As Word.Range
    Dim oShape As Word.InlineShape

    Set wkBook = ThisWorkbook
    Set wsSheet = wkBook.Worksheets("Sheet1")

    With wsSheet
    Set chObject = .ChartObjects("Rapport")
    End With

    Application.ScreenUpdating = False

    'Here we export the embedded chart on Sheet1
    chObject.Chart.Export _
    Filename:=ThisWorkbook.Path & "\Rapport.gif", FilterName:="GIF"

    'Here we create an instance of MS Word (hidden)
    Set wdApp = CreateObject("Word.Application")
    'The document is open.
    Set wdDoc = wdApp.Documents.Open(ThisWorkbook.Path & "\Dennis.doc")

    'Delete the present picture.
    With ActiveDocument.InlineShapes(1)
    End With

    'Here we assume we have before created a bookmark in the document
    Set BMRange = ActiveDocument.Bookmarks("Rapport").Range

    'Here we insert the GIF-file
    With BMRange
    .InlineShapes.AddPicture Filename:= _
    ThisWorkbook.Path & "\Rapport.gif", _
    LinkToFile:=False, _
    End With

    'Save and close the document
    With wdApp.ActiveDocument
    End With

    'Close the Word-app

    Set wdDoc = Nothing
    Set wdApp = Nothing

    With Application
    .CutCopyMode = False
    .ScreenUpdating = True
    End With

    'Delete the created temp-file
    Kill ThisWorkbook.Path & "\Rapport.gif"

    MsgBox "The Reportchart have been copied to Dennis.doc", vbInformation
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!