In addition to importing data into Microsoft Excel from another application, you may also want to export data from Microsoft Excel to another application so that you can create a presentation with it or combine it with other data.
You may want to present your data in an application other than Microsoft Excel. The following example charts the data from Sheet1 in the active workbook (the imported data from Microsoft Project) and places it in a PowerPoint slide for presentation. The code starts the PowerPoint application, creates a presentation, and adds one slide to it. The slide is then prepared to accept a chart, and you can chart the data from Sheet1 by using the Microsoft Excel ChartWizard.
Set pptApp = CreateObject("PowerPoint.Application.7")
Set pptPres = pptApp.Presentations.Add
Set pptSlide = pptPres.Slides.Add(1, 1)
Set pptSlideObj = pptSlide.Objects _
.AddOleObject("Excel.Chart", 1000, 1000, 5000, 5000)
Set pptChart = pptSlideObj.Object
With pptChart
.ChartWizard Source:=Worksheets("sheet1").Range("A1:B4"), _
Gallery:=xlPie, Format:=7, PlotBy:=xlColumns, _
CategoryLabels:=1, SeriesLabels:=0, HasLegend:=2
.HasTitle = True
.ChartTitle.Text = "My Schedule"
.ChartTitle.Font.Color = RGB(0, 0, 255)
.HasLegend = True
End With
pptPres.SaveAs "MayWork.ppt"
The customized Chart object that the example in the preceding section creates can be placed in a Word document by means of OLE Automation. However, the approach is different because you use WordBasic statements and functions instead of objects, properties, and methods to refer to and work with the WordBasic object.
The following example takes the first Chart object from Sheet1 in the active workbook, copies it to the Clipboard, and pastes it into the Word document called "MyDoc.doc."
Dim wdbasic As Object
Worksheets("sheet1").ChartObjects(1).Copy
Set wdbasic = CreateObject("word.basic")
With wdbasic
.FileOpen "C:\msoffice\winword\MyDoc.doc"
.EditPaste
.FileSave
End With
Note
The preceding example embeds the chart in the Word document. To link the chart into the Word document, use the EditPasteSpecial method. For more information about linking and embedding, see "Communicating with Embedded Word Objects" later in this chapter.
When you use WordBasic commands as part of OLE Automation, you must use conventional arguments instead of named arguments — that is, you must specify arguments by position instead of by name. If you leave out a conventional argument, you must indicate the missing value with a comma; you can, however, omit trailing commas. The following line of WordBasic code uses named arguments to apply drop cap formatting.
FormatDropCap .Position = 1, .Font = "Arial", .DropHeight = "3", \
.DistFromText = "6"
The following Visual Basic code does the same thing as the preceding example, but using conventional arguments. Your code must have already created the object variable wordobj and opened a file in Word before you run this line.
wordobj.FormatDropCap 1, "Arial", 3, 6
Note
The syntax for most statement and function entries in WordBasic Help describes the correct order for conventional arguments. There are some exceptions, however. For a complete list of these exceptions and the correct order of their arguments, see the Position.txt file included on the companion disk in the Microsoft Word Developer's Kit.
In addition to calling individual WordBasic commands, you can use the ToolsMacro command to run existing Word macros from outside Word. The following code runs a Word macro called "CreateDocandFormat." For more information about the ToolsMacro command and its arguments, see "ToolsMacro" in Help.
Dim wordobj As Object
Set wordobj = CreateObject("Word.Basic")
wordobj.toolsmacro "CreateDocandFormat", True