Sundeep4sandy

New Member
Hello guys,

I'll explain my scenario and Problem, can you suggest me few options?

scenario:
I've data in my excel sheet and the corresponding pie chart on the same sheet. Example Data as follows
Asset Type Amount
Savings $ 25,000
401k $ 55,000
Stocks $ 15,000

I'm able to get my excel data into the collection and manipulate it and update my excel file so that the pie chart is updated with new refined data.

Problem:

I'm not able to spy the or get the pie chart from the excel sheet.

My task:

I've to get that pie chart from excel to a new word file or powerpoint presentation.

Any suggestions?
 

cs.andras

Active Member
Well, having 0 experience with this particular kind of issue AND avoiding writing any code in VB, I'd suggest this: if the chart is always in a given place, open up excel, maximise it (Global send key events: "<{ALT}{Space}>{ALT}", short wait, then Global send keys "x"); after this, you should be able to see the chart in its given place. If not, then you should be able to navigate to it somehow - that ought to be available through the Excel VBO.
When this is done, just use region mode (surface automation) and Read stage (read image from region) which you can paste into a Word...etc.
 

VJR

Well-Known Member
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
 

Attachments

  • 1. Object Diagram.JPG
    1. Object Diagram.JPG
    59.3 KB · Views: 136
  • 2. Input parameters of Start stage.JPG
    2. Input parameters of Start stage.JPG
    30.3 KB · Views: 137
  • 3. Input parameters of Code stage.JPG
    3. Input parameters of Code stage.JPG
    30.1 KB · Views: 125
  • 4. Output parameters of Code stage.JPG
    4. Output parameters of Code stage.JPG
    23.4 KB · Views: 119
  • 5. Call from the Process.JPG
    5. Call from the Process.JPG
    33.5 KB · Views: 118
  • 6. Input Excel sheet.JPG
    6. Input Excel sheet.JPG
    40.5 KB · Views: 108
  • 7. Output Word doc.JPG
    7. Output Word doc.JPG
    42.9 KB · Views: 101

finocia

New Member
Hai, is there any way shall i copy the data and the chart in excel to the body of the Email? If it is there please advise me how to acheive this.
I have attached below the sample, which i need to copy to the body of the email.

Thanks,
Finocia
 

Attachments

  • Doubt.PNG
    22 KB · Views: 15

syed

Member
Hai, is there any way shall i copy the data and the chart in excel to the body of the Email? If it is there please advise me how to acheive this.
I have attached below the sample, which i need to copy to the body of the email.

Thanks,
Finocia

Done this? I have a solution for this. If required let me know.
 
Top