Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2002-06-02
6
Medium Priority
?
636 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
[X]
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
  • 4
  • 2
6 Comments
 
LVL 4

Accepted Solution

by:
Monchanger earned 150 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

618 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