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

Working with other Microsoft programs using VBA within Excel

Within Excel you can open another program and even develop a program 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:

Exercise

Step 1: As you have learned how to in the "Free Basics", copy/paste the following macro in a new workbook that you will save as word.xlsm.

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
         Application.CutCopyMode = False

End Sub

Step 2: Enter values in cells A1 and B1 (your first and lat name for example).

Step 3: Run the macro

You end up with a Word document named testWord .Doc in the same directory as the Excel workbook in which the macro runs. The Word document consists of a single sheet with a two cells table with the values of cell A1 and B1 of the workbook.

Notice that you use VBA for Word within the object varDoc that you have created. 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

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.