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