Consulting

Results 1 to 7 of 7

Thread: Exporting Excel Charts to Word

  1. #1
    VBAX Newbie
    Joined
    Jan 2008
    Posts
    4
    Location

    Exporting Excel Charts to Word

    Hi all,

    I'm currently working on an excel file that updates a number of charts based on information extracted from an Access Database via VBA which then should export the Charts to a template with bookmarks that's already created. The main objective is to use the Excel file as front-end to users. All applications are within Office 2003.

    The template has Tables in which the bookmarks are created depending on the design of the report (this is static thus not controlled via Excel).

    I have one problem and one question I would like your help with:

    First the problem: When exporting the Chart to a given bookmark in Word the chart is exported in the middle of the document without being "attached" to the specific location. When the word document is created I have to go and manually move the Chart to the desired location where the bookmark is created. (there's only one bookmark to start with for tests purposes)

    Now the question: Is there a way to export the Chart as image to avoid any modifications? Excel sheet with the charts is protected/hidden to avoid this.

    I attach the current code that i'm using

    [vba] ' Create XLChart and copy it.
    Set objChart1 = Workbooks("TEST.XLS").Sheets("Feuil1").ChartObjects("Graphique 2")

    Set oWDBasic = CreateObject("Word.Basic")

    ' Create a new document based on template previously created
    oWDBasic.filenew "C:\BUSINESS PERFORMANCE SCORECARDS\BUSINESS PERFORMANCE MONITOR.DOT"
    ' Insert chart into document where the bookmark 'Chart1' exists
    oWDBasic.EditGoto "RIGChart"

    objChart1.Copy
    oWDBasic.editPaste
    ' Save new document to disk
    oWDBasic.FileSaveAs "C:\TEST1.DOC"
    oWDBasic.FileClose (1)
    oWDBasic.AppClose
    Set objChart1 = Nothing
    Set oWDBasic = Nothing

    MsgBox "Finished"
    [/vba]

    Thanks in advance for your help

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    848
    Location
    I think the document's chart location needs to be selected before the paste. Trial selecting a table cell or bookmark chart location before doing the paste. HTH. Dave

    [VBA]
    objChart1.Copy
    'select the chart location
    oWDBasic.editPaste
    [/VBA]

  3. #3
    VBAX Newbie
    Joined
    Jan 2008
    Posts
    4
    Location
    Hi Dave,

    Thanks for the response, unfortunately it didn't do the trick. I still have the same issue.

    I'm attaching the excel file and due to forum restrictions I still cant upload the Template File in Word. I'll upload it as soon as possible.

    Again, thanks in advance for your advice.

    Juan

  4. #4
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    848
    Location
    Juan The template would be very helpful. There's something about having 5 posts here @ VBAX to gain certain priveleges? Here's my guess @ code syntax or perhaps the the table cell or bookmarked chart location has to be set to a range and then selected. Trial either of these. Good luck. Dave

    [VBA]
    objChart1.Copy

    oWDBasic.Bookmarks("Bookmarkname").Select
    oWDBasic.Tables(1).Cells(1, 1).Select 'or whatever cell

    Set Rng = oWDBasic.Bookmarks("Bookmarkname")
    Set Rng = oWDBasic.Tables(1).Cells(1, 1) 'or whatever cell
    Rng.Select

    oWDBasic.editPaste
    [/VBA]

  5. #5
    VBAX Newbie
    Joined
    Jan 2008
    Posts
    4
    Location
    Actually it goes like this:

    The code I just posted resides in Excel in the same file as the charts, when running the macro to push the charts to the Word template it's when I get the undesired results.

    The template is just below, I had to zip it because the forum does not accept .dot files.

    thanks for the help dave.

    regards,

  6. #6
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    848
    Location
    Jandres... I gave up on using a template as I really don't have a good understanding of how to use them. After several hours of messing around I arrived at a simulation of your desired outcome. Instead of using a template, a blank document file is used. The chart is inserted to the desired table location and then the doc is saved. The doc is then file copied to your outcome doc. The original doc is then re-opened and reset to blank and saved. The table adjusts to size for the chart and the doc retains it's format (I used a chart on sheet 1 and used the "D" drive in the code below). Hope this is of some use. Dave
    [vba]
    Sub XLChartToWordTable()
    Dim oWDBasic As Object, CFile As Object
    'copy chart
    With Sheets("sheet1").ChartObjects(1)
    .CopyPicture
    End With
    'open blank word doc file
    On Error GoTo ErFix
    Set oWDBasic = CreateObject("Word.Application")
    oWDBasic.documents.Open Filename:="D:\BUSINESS PERFORMANCE MONITOR.doc"
    'paste chart to table cell (1,3) and save doc
    With oWDBasic.ActiveDocument.Tables(1).cell(1, 3).Range
    .Paste
    End With
    oWDBasic.ActiveDocument.Close savechanges:=True
    Application.CutCopyMode = False

    'copy file to new file
    Set CFile = CreateObject("Scripting.FileSystemObject")
    'copyfile: source,destination,save
    CFile.copyfile "D:\BUSINESS PERFORMANCE MONITOR.doc", "D:\TEST1.DOC", True
    Set CFile = Nothing
    'restore blank word doc file
    oWDBasic.documents.Open Filename:="D:\BUSINESS PERFORMANCE MONITOR.doc"
    With oWDBasic.ActiveDocument.Tables(1).cell(1, 3).Range
    .Text = ""
    End With
    oWDBasic.ActiveDocument.Close savechanges:=True
    oWDBasic.Quit
    Set oWDBasic = Nothing
    MsgBox "Finished"
    Exit Sub
    ErFix:
    oWDBasic.Quit
    Set oWDBasic = Nothing
    End Sub
    [/vba]

    edit: ps. no need for selection when using "With" and range
    re:edit: should have mentioned the "blank" doc actually has the text and table as you laid out in your attached .dot file. The table cells are blank.

  7. #7
    VBAX Newbie
    Joined
    Jan 2008
    Posts
    4
    Location
    Great job and thanks for the excellent input, I was able to manage it perfectly with your help covering the two initial questions.

    I include the final code to avoid having to clear the Template File.

    Thanks a lot Dave

    [VBA]Dim objChart1 As Object, oWDBasic As Object, oWDDoc As Object
    With ThisWorkbook.Sheets("Feuil2").ChartObjects("Graphique 1")
    .CopyPicture
    End With
    On Error GoTo ErrFix
    Set oWDBasic = CreateObject("Word.Application")
    Set oWDDoc = oWDBasic.Documents.Open("C:\BUSINESS PERFORMANCE MONITOR.DOT")
    With oWDDoc.Tables(1).Cell(1, 2).Range
    .Paste
    End With
    oWDDoc.SaveAs "C:\TEST1.DOC"
    oWDDoc.Close savechanges:=False
    oWDBasic.Quit
    Set oWDBasic = Nothing
    Set oWDDoc = Nothing
    MsgBox "Finished"
    Exit Sub
    ErrFix:
    oWDBasic.Quit
    Set oWDBasic = Nothing
    Set oWDDoc = Nothing[/VBA]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •