Monday, 18 June 2012

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:

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
                  varDoc.activedocument.SaveAs ThisWorkbook.Path & "/" & "testWord.doc"
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.

No comments:

Post a Comment