Hi,
I am facing a problem related to excel and word macro. I have a sheet in excel with data and charts. I want to copy the table data and chart from excel to word. for that i have created a macro and i successfully copy the data from excel to word tables. but i am facing problem in copying chart.
there are abt 12 charts in excel and i want to copy 2 charts. the same 2 charts are in word in form of image.
I have created bookmark on chart in word and then tried this code
Sub CopyChart(objWorksheet1 As Excel.Worksheet, which As Integer)
Dim curDoc As Document
Set curDoc = Application.ActiveDocument
Dim newDoc As Document
Dim i As Integer
Dim bName As String
Dim myRange As Range
objWorksheet1.Activate
For i = 1 To ActiveDocument.Bookmarks.Count
If ActiveDocument.Bookmarks(i).Name = "NPV_1" Then
objWorksheet1.ChartObjects("Chart 15").Activate
objWorksheet1.ChartObjects("Chart 15").Select
objWorksheet1.ChartObjects("Chart 15").Copy
curDoc.Activate
Selection.GoTo What:=wdGoToBookmark, Name:="NPV_1"
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.PasteSpecial DataType:=wdPasteMetafilePicture
Selection.TypeParagraph
'Selection.PasteAndFormat Type:=wdChartPicture
ActiveDocument.Bookmarks.Add Range:=Selection.Range, Name:="NPV_1"
End If
Next i
End Sub
Display More
where objWorksheet1 is the object of excel worksheet and passed when the function is called..
NPV_1 is the bookmark created on chart in word which needs to be replaced.
Chart 15 is the chart title in excel
curDoc is the object of word document.
After running this macro from word the macro copies the chart from excel activate the word document search for the bookmark deletes the bookmark paste the object correctly. but the problem is below the chart is a table as soon as the chart is deleted the table shifts upwards and the chart is copied over the table. and the table doesn't shift down.
Sorry for such a long story but i want to clear the problem which i am facing.
Please if anyone can help.
Thanks
Nitin