Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Copy chart to Word (VBA)

    Hi,

    I have little experience in working between applications with VBA for Office. I am willing to learn, so if anyone could help me with this small project to copy a chart form Excel to a document in Word and slot it into a certain position in the doc.

    Here is the code I have set up so far, but there are obvious problems due to my inexperience. I do have a reference activated to the Word object library in Excel.

    TX

    <pre>Sub Project()
    Dim OpenWord As Long

    'Draw up chart
    Range("A1").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$D$4")
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.Select
    ActiveChart.Copy

    'Open Word and paste chart into document
    OpenWord = Shell("C:Program FilesMicrosoft Office 2007OFFICE12WinWord.exe", 1)
    Application.Documents.Open "C:ProjectReport"
    Selection.HomeKey Unit:=wdStory
    Selection.Find.ClearFormatting
    Selection.Find.Text = "report of month sales"
    Selection.Find.Execute
    Selection.EndKey Unit:=wdLine
    Selection.TypeParagraph
    Selection.Paste

    End Sub

    </pre>

    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy chart to Word (VBA)

    Instead of using Shell, I'd use Automation to control Word from Excel. See WendellB's tutorial Automation 101.

    The attached text file contains a modified version of your code. I haven't actually tested it.
    Attached Files Attached Files

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy chart to Word (VBA)

    Thank you very much for that code. I will test it now...
    Cheers
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy chart to Word (VBA)

    Hi Hans,

    I get an error when the chart gets copied... ( ActiveChart.Copy )

    It says : The specified dimension is not valid for the current chart type.
    Any ideas?

    TX

    PS: Tx for the automation 101 links...
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy chart to Word (VBA)

    Try

    ActiveChart.ChartArea.Copy

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy chart to Word (VBA)

    Stunning...
    The macro is great! Thanks Hans
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •