Hi all,

I'm trying to export some charts from Excel 2000 to Word 2000. I have managed to export them at 100% full-size into the Word document using an adaptation of the macro listed on p252-253 of Wrox's Excel 2000 VBA book.

What I would like to do is find some method of formatting the size/alignment/type of Chart object within Word, but using Excel VBA, so that I can run everything within Excel as I know nothing about Word VBA, and very little of Excel VBA.

Is this possible? Has anybody got any ideas?

If you can help or offer suggestions as where to look, then I'd appreciate it.

Thanks!

You can do this from your "Excel" program, but you are going to be using "Word VBA" to do. No way around it. I haven't done this myself, but I expect that the embedded Excel chart will have top, left, height, and width, which you will need to set in the wordapp to fix the position. It's likely the alignment can also be set within word, but what do you mean by "type"?

I recommend you try to get a copy of WROX's WORD 2000 VBA book before you start.

Dave

Hi virtualdave,

Thanks for the info. I guess that I will do as you suggest and get the book in order to learn some Word VBA, if that is what I need to set the attributes you suggested. Is there any way of using a percentage reduction rather than specify widths and heights? Maybe other members here might be able to suggest something.

What I meant by type (and I may have used the wrong terminology as I'm still a rookie) is that I'm wondering whether the chart can be changed to a picture, or left as a chart with a link to the original data in Excel.

Once again, Thank You for the suggestions!

MG

Hello all,

I finally managed to come up with some code which I think should work, it runs in Excel 2000, and takes a file with 96 charts and dumps the 96 charts in a set sequence to an open Word 2000 document (The macro searches for an open Word 2000 document and then dumps the charts in to it).

I'm not sure yet about the formatting of the charts, as I am now getting an error!!!

The macro dumps the first chart into the word document, but before it can format the chart picture, I get a "run-time error '424' : Object required" message.

I'm not sure how or what to do about this, but if any of you have any ideas, then I'd appreciate it if you can help.

Here's the macro....


Sub ChartToWord()
    Dim wdApp As Word.Application
    Dim rrc As Integer
    Dim rcounter As Integer
        On Error Resume Next
        Set wdApp = GetObject(, "Word.Application")
        If wdApp Is Nothing Then
            Set wdApp = GetObject("", "Word.Application")
            With wdApp
                .Documents.Add
                .Visible = True
            End With
        End If
        For rcounter = 1 To 16
            For rrc = rcounter To rcounter + 80 Step 16
                Debug.Print rrc
                Charts(rrc).CopyPicture Appearance:=xlScreen, Format:=xlPicture
                On Error GoTo 0
                With wdApp.Selection
                    .EndKey Unit:=wdStory
                    .TypeParagraph
                    .PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, Placement:=wdWrapTopBottom, DisplayAsIcon:=False
                    .ParagraphFormat.Alignment = wdAlignParagraphLeft
                    .ShapeRange.Fill.Visible = msoFalse
                    .ShapeRange.Fill.Transparency = 0#
                    .ShapeRange.Line.Weight = 0.75
                    .ShapeRange.Line.Transparency = 0#
                    .ShapeRange.Line.Visible = msoFalse
                    .ShapeRange.LockAspectRatio = msoTrue
                    .ShapeRange.Height = 173.75
                    .ShapeRange.Width = 226.2
                    .ShapeRange.PictureFormat.Brightness = 0.5
                    .ShapeRange.PictureFormat.Contrast = 0.5
                    .ShapeRange.PictureFormat.ColorType = msoPictureAutomatic
                    .ShapeRange.PictureFormat.CropLeft = 0#
                    .ShapeRange.PictureFormat.CropRight = 0#
                    .ShapeRange.PictureFormat.CropTop = 0#
                    .ShapeRange.PictureFormat.CropBottom = 0#
                End With
            Next rrc
        Next rcounter
        Set wdApp = Nothing
End Sub

You need to capture the objects in order to manipulate them better. If the statement has a return value this is simply:

dim myChart as Picture (I'm not sure this is your object type, I'm guessing)
set mychart = charts(rcc).copypicture
with mychart
  Etc.
end with
set mychart = nothing

Actually you should do this with any generated object.

Dave