PDA

View Full Version : Exporting Excel Charts to Word



JAndres
01-15-2008, 05:46 AM
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

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


Thanks in advance for your help

Dave
01-16-2008, 12:10 AM
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


objChart1.Copy
'select the chart location
oWDBasic.editPaste

JAndres
01-16-2008, 01:18 AM
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

Dave
01-16-2008, 08:03 AM
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


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

JAndres
01-17-2008, 01:52 AM
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,

Dave
01-21-2008, 08:30 AM
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

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


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.

JAndres
01-22-2008, 04:09 AM
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

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