Copy charts from Excel to Word using Late Binding

[paddysheeran]

Hi All,

I currently have a script that copies data and charts from Excel to Word using Early Binding. We have had no issues with this until recently a colleague has updated to Exel 2013. We now have the missing reference file. To get round this I would like to switch to Late Binding but so far I have been unable to amend this with the code debugging. A small sample of the current code is:

 Public wdApp As New Word.Application
Public wdDoc As Word.Document
Public t As Word.Range
Public wsSource As Worksheet
Sub Word_Report()
    
    WordReportTemplate = ActiveWorkbook.Path & "\SampleReport.docx" 'Location of Word report template
        
    Set wdDoc = wdApp.Documents.Add(WordReportTemplate) 'Adds the word template as a new word document
    Set t = wdDoc.Bookmarks("graph_to_import").Range
    
    Set wsSource = ActiveWorkbook.Sheets("SourceSheet")
    wsSource.Activate
    
    ActiveSheet.ChartObjects("faults").Select
    ActiveChart.ChartArea.Copy
    t.PasteAndFormat (wdPasteDefault)
End Sub

I would like to know how to change the above. I have something like this currently but it is not working (it errors on the t.PasteAndFormat (wdPasteDefault) line) :

 Public wdApp As Object
Public wdDoc As Object
Public t As Object
Public wsSource As Worksheet
Sub Word_Report()
    
    
    Set wdApp = CreateObject("Word.Application")
    
    WordReportTemplate = ActiveWorkbook.Path & "\SampleReport.docx" 'Location of Word report template
        
    Set wdDoc = wdApp.Documents.Add(WordReportTemplate) 'Adds the word template as a new word document
    Set t = wdDoc.Bookmarks("graph_to_import").Range
    
    Set wsSource = ActiveWorkbook.Sheets("SourceSheet")
    wsSource.Activate
    
    ActiveSheet.ChartObjects("faults").Select
    ActiveChart.ChartArea.Copy
    t.PasteAndFormat (wdPasteDefault)
End Sub

its probably somethign simple but I've not had to use it before

thanks in advance

Paddy.

[Aflatoon]

Try adding:

Const wdPasteDefault as long = 0

to the start of the code. You can't use Word's constants without a reference to the Object library so you have to define them yourself.