Hi Sundeep4sandy,
An implementation to your requirement using the code stage it would be as follows.
Be noted that this is a sample working code and works well under general conditions. You might need to make amendments as per your requirement. For example, it assumes that Sheet1 exists so you would need to add code to check the sheet, add Exception Handling, remove hard coded path of Word doc, etc.
- Create a new action page in the MS Excel VBO. This can be a duplicate of any other existing page after making the necessary changes to it. Lets call the new page as 'Copy Chart From Excel2Word' and it looks like 1.jpg in the screenshots.
- 2.jpg: has the Input parameters to the Start stage of the Object
- 3.jpg: has the Input parameters to the Code stage
- 4.jpg: has the Output parameters to the Code stage
This is how the code in the Code stage is:
Refer to the comments in the code.
Code:
Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet As Object
Dim wdApp As Object ' Word.Application
Dim wdDoc As Object ' Word.Document
Try
wdApp = CreateObject("Word.Application")
'This is a sample hard coded path. For using a custom path, pass it as a parameter to the Code stage.
'For creating a brand new file you can make use of the MS Word VBO, Save it by a name and then pass the file path to this Code stage here.
wdDoc = WdApp.Documents.Open("C:\Users\Administrator\Desktop\Test.docx")
'Can be turned to False if you do not want to see the Document while running the process
wdApp.Visible = True
sw = GetWorkbook(Handle, Source_Workbook)
ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
'Chart 1 is the name of the Chart on the Excel sheet. It is seen by clicking on the Chart border and
'then its name shows up in the Name Box at the top left corner
'If your Chart is dynamic and you do not know the Chart name then you need to write code to read the
'chart(s) on that sheet and get the name
'or use ChartObjects(1) if you know there will only be 1 chart on the sheet.
'Copy the chart from excel as a Picture
sheet.ChartObjects("Chart 1").Chart.CopyPicture (Appearance:=1, _
Size:=1, Format:=-4147)
'Paste the copied chart in to the Word application
wdApp.Selection.PasteSpecial (Link:=False, DataType:=3, _
Placement:=0, DisplayAsIcon:=False)
'Parameter constants:
'xlPicture -4147
'wdPasteMetafilePicture 3
'wdInLine 0
'xlScreen 1
'If you need to insert the Chart at a specific location then at first you need to create a Bookmark in the document
'and then make use of that BookmarkName to insert the picture
'wdbmRange = wdDoc.Bookmarks("BookmarkName").Range
'Make use of the below link
'https://msdn.microsoft.com/en-us/vba/excel-vba/articles/exporting-a-chart-to-a-word-document
'Save and close the Word document.
With wdDoc
.Save
.Close
End With
'Quit Word.
wdApp.Quit
Success = True
Catch e As Exception
Success = False
Message = e.Message
Finally
sw = Nothing
ss = Nothing
dw = Nothing
ds = Nothing
excel = Nothing
sheet = Nothing
wdDoc = Nothing
wdApp = Nothing
End Try
- 5.jpg: is the call to this new action from the Process
- 6.jpg: is your Input Excel sheet containing the Pie chart
- 7.jpg: is the Output after running the process