PDA

View Full Version : exporting excel charts to Word



basteele
04-27-2009, 05:04 PM
Hi All,

I'm trying to get Excel to export multiple charts into Word. There's an example of how to do this for Powerpoint, so I used that to begin with. But for some reason, my code is opening multiple instances of Word rather than putting the charts into the same file. It doesn't seem to recognize the fact that Word is already open, even though I'm using the GetObject command. Can someone tell me why?

Thanks!
Barrett

__________________________________
Sub export_graphs

'go through each sheet in the excel file
sheetnum = ActiveWorkbook.Sheets.Count
For snum = 1 To sheetnum
Call Export_to_Word(snum)
Next snum

End sub

_______________________________________

Sub Export_to_Word(snum)
Dim oApp As Object

Dim wrdDoc As Word.Document

Dim TestChart As ChartObject

'use active sheet.
SheetName = ActiveSheet.Name
ChartNumber = 1

'Set TestSheet = Sheets(SheetName)
On Error Resume Next
Set TestChart = Worksheets(SheetName).ChartObjects(ChartNumber)
If TestChart Is Nothing Then
GoTo 2
End If
On Error GoTo 0

' Set wrdApp to Word by creating a new instance of Word.
' If Word is already open, you would instead use the GetObject
' method instead.

'Look for existing instance of Word
On Error Resume Next
Set oApp = GetObject("", "Word.Application")
On Error GoTo 0





'Create new instance if no instance exists
If Err.Number <> 0 Then
Set oApp = CreateObject("Word.Application")
End If

'Add a word document to the word app if none exists
If oApp.Documents.Count = 0 Then
Set wrdDoc = oApp.Documents.Add '(, , wdNewBlankDocument)
End If

' Set Word to be Visible.
oApp.Visible = True


' Select the chart to be copied, and copy it.

Worksheets(SheetName).Activate
'ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
ActiveSheet.ChartObjects(1).Chart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
plottitle = ActiveChart.ChartTitle.Text 'Grab the title of the chart to copy and paste onto the slide Title

' paste the chart into word
wrdDoc.Content.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, DisplayAsIcon:=False



' Clean up
Set oApp = Nothing
Set wrdDoc = Nothing
2
End Sub

basteele
04-28-2009, 04:46 PM
Oh, and I noticed that two occurances of Word show up in Task Manager each time this routine is looped through!

basteele
04-28-2009, 05:45 PM
Okay, I found this code online and it works much better than the original code that I posted in this thread. But, now I have a new problem:

If I step through the code, line by line, using the debugger, then it all works fine and the document is created just the way I want. But, if I run the script by hitting the "play" button in the debugger, the code craps out in the middle of the loop with the following error:

Run-time error '4605':
This method or property is not available because the Clipboard is empty or not valid.

If I change the "on error goto 0" line to "on error goto next", then the routine only pastes about half of the charts into the Word document and then quits.

Any ideas of what's going on?

Thanks
Barrett


________________________________________________________________________

Sub export_to_word_new()
On Error Resume Next
Application.ScreenUpdating = False
Application.CutCopyMode = False

Dim xlBook As Workbook
Dim xlChart As ChartObject
Dim xlSheet As Worksheet
On Error Resume Next
Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")
WordApp.ScreenUpdating = False
Dim worddoc As Object
Set worddoc = WordApp.Documents.Add
Set xlBook = ActiveWorkbook
NumSheets = xlBook.Worksheets.Count
NumChSheets = xlBook.Charts.Count
On Error GoTo 0
For Each xlSheet In xlBook.Worksheets
For Each xlChart In xlSheet.ChartObjects

xlChart.Activate
xlChart.Select
xlChart.CopyPicture
WordApp.Selection.TypeParagraph
WordApp.Selection.Paste
WordApp.Selection.TypeParagraph
WordApp.Selection.TypeParagraph
Next xlChart
Next xlSheet
Application.ScreenUpdating = True
WordApp.ScreenUpdating = True
WordApp.Visible = True
Set WordApp = Nothing
Set worddoc = Nothing
Application.CutCopyMode = False
End Sub

lucas
04-28-2009, 07:44 PM
Using an existing Word document and bookmarks might work better. Either way this should give you some insight:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=133

basteele
04-29-2009, 11:47 AM
I'm trying to post some modified code to this forum, but I keep getting a message saying that there are links in my message and they must be removed before it will be posted.

But there aren't any links!

Please help!
thanks
Barrett