VBA Lesson  23: Working with Other Microsoft Programs in VBA for Excel

This is an excerpt from Pierre Leclerc (www.excel-vba.com)

API Working with Windows

API stands for Application Programming Interface and consists of a collection of functions that provide programmatic access to the features of the operating system (Windows). When you use API's within VBA for Excel not only do you control Excel but also most parts of Windows.

Working with other Microsoft programs using VBA within Excel

Within Excel you can open another program and even act   within it using VBA. For example here is a short macro that opens Word, then a new document  to copy/paste the content of 2 cells from Excel to word and save the word document in the same directory as the workbook in which the macro runs:

Sub proWord()
Dim varDoc As Object       

        Set varDoc = CreateObject("Word.Application")

                  varDoc.Visible = True
                  Sheets("Sheet1").Range("A1:b1").Copy
                  varDoc.documents.Add
                  varDoc.Selection.Paste
                  varDoc.activedocument.SaveAs ThisWorkbook.Path & "/" & "testWord.doc"
                  varDoc.documents.Close

        varDoc.Quit

End Sub

Notice that you use VBA for Word within the object varDoc. If you do not know VBA for Word remember that there is also a Macro Recorder in Word. The object varDoc can be visible or you can work within it without bringing it on screen with:
varDoc.Visible = False

Go to the next section
Section 3 : Customized Dialog Windows (Userforms) in VBA for Excel