[FONT="]I have two Excel workbooks. Each workbook has 55 charts in Sheet1. I want to paste these charts "as picture" in a Word doc. The following code works well for a small number of charts in one workbook. I’m looking for a way to adjust this code so it pastes charts from two workbooks in the following order: Chart1 from Workbook1 --> Chart1 from Workbook2 ---> Chart2 from Workbook1---> Chart2 from Workbook2 and so on.[/FONT]
[FONT="]I'd like to adjust the code so I don't have to insert 110 bookmarks in Word and change each iteration of “copy chart/paste chart” to match the Bookmark name with Chart name. I’m thinking I can use an “Until loop” to select, copy and paste until Chart Name =”Chart 55” with a carriage return after each paste. Or, instead of bookmarks, just paste at Selection, TypeParagraph, then select, copy, and paste next chart until all are transferred. How do I specify there are two Excel workbooks to get charts from?
[FONT="]I'd like to adjust the code so I don't have to insert 110 bookmarks in Word and change each iteration of “copy chart/paste chart” to match the Bookmark name with Chart name. I’m thinking I can use an “Until loop” to select, copy and paste until Chart Name =”Chart 55” with a carriage return after each paste. Or, instead of bookmarks, just paste at Selection, TypeParagraph, then select, copy, and paste next chart until all are transferred. How do I specify there are two Excel workbooks to get charts from?
Code:
[FONT="]Sub CopyCharts2Word()Dim wd As ObjectDim ObjDoc As ObjectDim FilePath As StringDim FileName As StringFilePath = "C:\Users\Name\Desktop"FileName = "Template.docx"'check if template document is open in Word, otherwise open itOn Error Resume NextSet wd = GetObject(, "Word.Application")If wd Is Nothing Then Set wd = CreateObject("Word.Application") Set ObjDoc = wd.Documents.Open(FilePath & "\" & FileName)Else On Error GoTo notOpen Set ObjDoc = wd.Documents(FileName) GoTo OpenAlreadynotOpen: Set ObjDoc = wd.Documents.Open(FilePath & "\" & FileName)End IfOpenAlready:On Error GoTo 0'find Bookmark in template docwd.Visible = TrueObjDoc.Bookmarks("Bookmark1").Select 'copy chart from Excel Sheets("Sheet1").ChartObjects("Chart1").Chart.ChartArea.Copy 'insert chart to Bookmark in template doc wd.Selection.PasteSpecial Link:=False, _ DataType:=wdPasteMetafilePicture, _ Placement:=wdInLine, _ DisplayAsIcon:=False'find Bookmark in template docwd.Visible = TrueObjDoc.Bookmarks("Bookmark2").Select 'copy chart from Excel Sheets("Sheet1").ChartObjects("Chart2").Chart.ChartArea.Copy 'insert chart to Bookmark in template doc wd.Selection.PasteSpecial Link:=False, _ DataType:=wdPasteMetafilePicture, _ Placement:=wdInLine, _ DisplayAsIcon:=False End Sub[/FONT][/FONT][/COLOR]