Copy Excel to Word

[swoozie]

Please look over the code, something isnt right even though I have compared this to a bunch of posts already. I made comments throughout regarding what is happening where. thank you.

Sub CreateWord()
 NewBook = ActiveWorkbook.Name 'Obtains the name of the spreadsheet
 
'This code requires a referece to the Word object model
 Dim Appword As New Word.Application
 Dim wdDoc As Word.Document ' What is this for?
         
 Set Appword = CreateObject("Word.Application") 'There is a new workbook created called object?
 
   Appword.Documents.Add
   
    Windows(NewBook).Activate 'Makes sure I stay in the right place for copying from Excel
    Sheets("CHART").Select
 
For i = 1 To 6
    Windows(NewBook).Activate 'Makes sure I stay in the right place for copying from Excel
    Sheets("CHART").Select
    CName = "Chart " & i 'Selects the  chart to be copied
    Sheets("Chart").ChartObjects(CName).Activate
    Sheets("Chart").ChartObjects(CName).Copy
    
    MsgBox ("We are on Chart: " & CName)
        
    MsgBox (Appword.Name) 'This only says Microsoft Word
    Appword.Selection.PasteAndFormat wdChartPicture 'Errors and states "command is not available"
    
Next i
 
Appword.Visible = True
 
'Save New File as : Need to Change File Location information.  This is where the newly created file is saved to.
'
 
       ChangeFileOpenDirectory _
        Drive & Path
        Appword.ActiveDocument.SaveAs FileName:=FileName & ext, FileFormat:= _
        wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
        True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
        False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
        
 
   Appword.Documents.Save
   Appword.Documents.Close
'~~~~~~~~~~~~~~~~~~~~~~~~
    Windows(NewBook).Activate
    Sheets("CHART").Select
 
Set Appword = Nothing
End Sub

[Ecniv]

Please look over the code, something isnt right even though I have compared this to a bunch of posts already. I made comments throughout regarding what is happening where. thank you.

Sub CreateWord()
 NewBook = ActiveWorkbook.Name 'Obtains the name of the spreadsheet
 
'This code requires a referece to the Word object model
 Dim Appword As New Word.Application
 Dim wdDoc As Word.Document ' What is this for?
'---- when you edit a word doc
'---- the above is that document
 
'---- I don't think you need this as you have created a word app 
'---- (new) already above        
 Set Appword = CreateObject("Word.Application") 
'There is a new workbook created called object?
 
'---- adds a new doc
   Appword.Documents.Add
   
 
    Windows(NewBook).Activate 'Makes sure I stay in the right place for copying from Excel
    Sheets("CHART").Select
 
For i = 1 To 6
    Windows(NewBook).Activate 'Makes sure I stay in the right place for copying from Excel
    Sheets("CHART").Select
    CName = "Chart " & i 'Selects the  chart to be copied
    Sheets("Chart").ChartObjects(CName).Activate
    Sheets("Chart").ChartObjects(CName).Copy
    
    MsgBox ("We are on Chart: " & CName)
        
'This only says Microsoft Word
'---- because you haven't saved the word doc
    MsgBox (Appword.Name)
    Appword.Selection.PasteAndFormat wdChartPicture 
'Errors and states "command is not available"
    
Next i
 
Appword.Visible = True
 
'Save New File as : Need to Change File Location information.  This is where the newly created file is saved to.
'
 
       ChangeFileOpenDirectory _
        Drive & Path
        Appword.ActiveDocument.SaveAs FileName:=FileName & ext, FileFormat:= _
        wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
        True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
        False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
        
 
   Appword.Documents.Save
   Appword.Documents.Close
'~~~~~~~~~~~~~~~~~~~~~~~~
    Windows(NewBook).Activate
    Sheets("CHART").Select
 
Set Appword = Nothing
End Sub

Are you sure that the chart (dot) copy is putting the chart into the clipboard?
.PasteAndFormat ? is that the right method?
Did you record all your movements to have an idea of what excel and word want?

[swoozie]

1) Yes I recorded a macro first to see what the macro showed. Which actually was virtually nothing.

2) From the macro I found the .pasteformat method. Since I want the chart to be a pix and not an embedded object

3) Yes it is copying to the clip board.

On more than one occasion it did manage to copy chart 1 to a word document however it locked up and I had to kill the macro and find the file. I have 6 charts to paste into word. It is a 3 page document. The top is a summary of the charts, then 2 charts displayed below.

Any suggestions?