[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2002-06-02
6
Medium Priority
?
641 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…

873 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