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