Solved

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

Posted on 2002-06-02
6
615 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now