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