Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Copy excel chart into word & powerpoint in visual basic 5.0  or 6.0

Posted on 2002-06-02
6
621 Views
Last Modified: 2013-11-25
hi, anyone help,

Does anyone know how to copy excel chart(office 97 or 2000) which is in worksheet and paste to word and powerpoint by using visual basic 5.0 or 6.0
0
Comment
Question by:allangan
  • 4
  • 2
6 Comments
 
LVL 4

Accepted Solution

by:
Monchanger earned 50 total points
ID: 7050415
This should do it.
Notice that there are some small changes you need to do to get it working:
1) Set your file names of your own Excel & Word documents. If you use already-open documents, activate them (make them the ones you are working on) in word/excel, and use the ActiveDocument (for word) & ActiveWorkbook.ActiveWorksheet (for excel) functions.
2) Change the way I select the chart object - mine gets the first chart in the worksheet. Good if you only have one there ...



    ' Get a reference to Word
    On Error Resume Next
    Dim objWord As Word.Application
    Set objWord = GetObject(, "Word.Application")
    On Error GoTo 0
    If objWord Is Nothing Then
        Set objWord = CreateObject("Word.Application")
    End If
    ' Open your file
    ' You'll have to set your code to your own file, unless
    ' you work with an already active application
    Dim myWordDoc As Word.Document
    Set myWordDoc = objWord.Documents.Open("Your file name")
   
    ' Same note as above
    On Error Resume Next
    Dim objExcel As Excel.Application
    Set objExcel = GetObject(, "Excel.Application")
    On Error GoTo 0
    If objExcel Is Nothing Then
        Set objExcel = CreateObject("Excel.Application")
    End If
   
    ' Open the xls file
    objExcel.Workbooks.Open ("Your file name")
   
    Dim myExcelSheet As Excel.Worksheet
    ' Enter the worksheet you use here
    Set myExcelSheet = objExcel.ActiveWorkbook.Worksheets(1)
   
    ' Search for the chart object we want to copy
    Dim objTheChartInExcel As Excel.Shape
    Dim intShapeIterator As Integer
    intShapeIterator = 1
    Do While intShapeIterator <= myExcelSheet.Shapes.Count And objTheChartInExcel Is Nothing
   
        'Provide your own test to recognize the chart you want.
        ' Mine simply takes the first
        If myExcelSheet.Shapes(intShapeIterator).Type = msoChart Then
            Set objTheChartInExcel = myExcelSheet.Shapes(intShapeIterator)
        Else
            intShapeIterator = intShapeIterator + 1
        End If
    Loop
   
    ' Copy the chart
    objTheChartInExcel.Copy
' If you only want the picture, and not an embeded chart (doesn't require excel on the computer that opens the doc, loads faster and saves memory) - you can use the CopyPicture() function
'    objTheChartInExcel.CopyPicture
    Set objTheChartInExcel = Nothing
   
    ' Go to the location you wish in the word document and paste the chart
    ' (this simply places it at the top of the first page)
    myWordDoc.Goto(wdGoToPage, wdGoToAbsolute, 1).Paste
   
   
    'Don't forget to clean up after yourself
    Set myExcelSheet = Nothing
    Set myWordDoc = Nothing
    objExcel.Quit
    Set objExcel = Nothing
    objWord.Quit
    Set objWord = Nothing
   
    'etc.


Hope this is enough :-)
0
 

Author Comment

by:allangan
ID: 7056728
But if i copy and paste this comment, it come out with the error

Compile Error:
Variable not defined  

'If myExcelSheet.Shapes(intShapeIterator).Type = msoChart Then'

at statement msoChart

what should I declare for the msoChart

0
 
LVL 4

Expert Comment

by:Monchanger
ID: 7056834
msoChart belongs to the MsoShapeType enum which can be referenced thru the "Microsoft Office 9.0 Library"

You can also define it as:
const msoChart = 3
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:allangan
ID: 7059682
how can i paste a chart to the place i wanted in word and powerpoint as i used

'ppApp.ActiveWindow.View.Paste' in powerpoint, but it

paste at the place which is not i want????


another thing is that the above coding running smoothly at Office 97 but after i migrate to office 2000, i can't see the chart in word. is there any idea about it????
0
 

Author Comment

by:allangan
ID: 7063838
you r the automation expert, thx and wish u all the best!!!
0
 

Author Comment

by:allangan
ID: 7063839
you r the automation expert, thx and wish u all the best!!!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question