Extending Excel VBA to Work with Other Programs

Extending Excel VBA to work with other programs

To access commands from another application you will need to add a Reference to its Object Library. In the VB Editor, click Tools, then References, to display the References window.

Word example

First of all you need to declare a variable of the type WordApplication. There is more than one way; here we use the New keyword to establish the reference.

Dim apWord as New Word.Application

The program writes some text and pastes a chart to word, closes and saves the file. Select a chart in an Excel worksheet then run this program. Close Word if you have it open.

Sub wordtest()
Dim apword As New Word.Application
Dim ch As Chart
Set ch = ActiveChart
ch.ChartArea.Copy
apword.Visible = True
With apword.Documents.Add
.PageSetup.Orientation = wdOrientLandscape
.Content.InsertAfter vbCrLf
.Content.InsertAfter "hello"
.Content.InsertAfter vbCrLf
.Content.Paste
.Content.InsertAfter vbCrLf
.Content.InsertAfter "bye"
.SaveAs ThisWorkbook.Path & "\" & "wordtest.doc"
' .Close
End With
'apword.Quit
Set apword = Nothing
End Sub / Excel commands to declare an object variable of the type Chart, and copy it
If you want to see your Word document then make it visible
Word VBA statements to start a new document, insert one or two strings and line breaks
Paste the chart
Save to the same folder as your Excel workbook
Comment out Close command if you want to check the word document
Destroy the variable to release memory

Should work OK. Problems:

• program crashes if Word is open (you can research the GetObject method, which works if an instance of Word is already running, and CreateObject which is an alternative to using New as above)
• the “hello” line disappears after the chart is pasted, no obvious reason why this should be, so a lot depends on your appetite for learning another object model and finding ‘workarounds’ for the awkward bits.

Powerpoint

To start an instance of Powerpoint, ensure Powerpoint isn’t running, and run this code from Excel .

Sub ppt_test()
Dim apPpt As New PowerPoint.Application
apPpt.Visible = True
With apPpt.Presentations.Add
' add your Powerpoint commands here
End With
End Sub

It should start a new Powerpoint document.

The only problem now is to learn to program Powerpoint! Maybe something as simple as add a new slide?

In the Powerpoint VB Editor try
Sub addslide()
ActivePresentation.Slides.addslide
End Sub
- doesn’t work! 

A trip to Powerpoint VBA Help and you might find this:

Sub addslide()
Dim pptSlide As Slide
Dim pptLayout As CustomLayout
Set pptLayout = ActivePresentation.Slides(1).CustomLayout
Set pptSlide = ActivePresentation.Slides.addslide(2, pptLayout)
End Sub

If it’s as difficult as that to add a slide then how much more complex is it to paste a chart? Time for me to stop!

An alternative might be to learn to program Powerpoint and use GetObject to open an Excel workbook and import the chart. In any case a book on Powerpoint programming is required, or you might get lucky and find tutorials on the web.

1