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

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
allanganAsked:
Who is Participating?
 
MonchangerConnect With a Mentor Commented:
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
 
allanganAuthor Commented:
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
 
MonchangerCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
allanganAuthor Commented:
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
 
allanganAuthor Commented:
you r the automation expert, thx and wish u all the best!!!
0
 
allanganAuthor Commented:
you r the automation expert, thx and wish u all the best!!!
0
All Courses

From novice to tech pro — start learning today.