Using Office applications in Visual Basic

By Anne-Marie Wright

The purpose of this article is to show you how to connect to Office applications using code, and a very briefly touch on entering data into the files.

It is possible to get Visual Basic to talk to and control Word and Excel in code.

Here is a small example of how to connect to and talk to the applications

Option Explicit

Dim xlsApp As Excel.Application
Dim wrdApp As Word.Application

It is possible to assign the Application to an object variable. As long as the relevant object libraries have been selected. Microsoft Excel 8.0 Object Library for Excel and Microsoft Word 8.0 Object Library for Word.

Check the references by going to the Project menu in the VB IDE and select references. This will list all the librarys that the system is aware of.

Private Sub Command1_Click()
    Set xlsApp = Excel.Application
    With xlsApp
        'Show Excel
        .Visible = True
        'Create a new workbook
        .Workbooks.Add
        'Put text in to the cell that is selected
        .ActiveCell.Value = "Hi"
        'Put text into A3 regardless of the selected cell
        .Range("A3").Value = "This is an example of connecting to Excel"
    End With
End Sub

In this routine we setup the object in the variable xlsApp and make Excel visible to the user. When Excel is started like this it does not contain a workbook, so one has to be created or Opened. In this example we create a new workbook. Once there is a workbook, away we go! you can manipulate the workbook information, print it, Save it, Mail, what ever you can do in Excel

Private Sub Command2_Click()
    'close the workbook
    xlsApp.Workbooks.Close
    'Close Excel
    xlsApp.Quit
End Sub

This routine closes the Application down, first we close the workbook (Warning: This way will bring up a prompt from Excel asking if you want to save changes, if there are any changes since you saved), then we quit the application

Private Sub Command3_Click()
  Set wrdApp = New Word.Application 
  With wrdApp
    'Show Word 
    .Visible = True
    'Create New Document
    .Documents.Add
    'Add text to the document
    .ActiveDocument.Content.Text = "Hi"
    .ActiveDocument.Content.Text = "This is a test example"
  End With
End Sub 

So in this routine we setup the object in the variable wrdApp and make Word visible to the user in exactly the same way as Excel. Again when Word is started like this it does not contain a document, so one has to be created or Opened. In this example we create a new document. Away we go! you can manipulate the document information, print it, Save it, Mail, what ever you can do in Word.

But!!! Word is not so easy to place text about the document, especially if you have worked with Excel. To place text in a certain place easily you need to have a bookmark. Which means you will need to create a template.

Private Sub Command4_Click()
    'Close the current document
    wrdApp.ActiveDocument.Close
    'Close Word
    wrdApp.Quit
End Sub

This routine closes the Application down, first close the document (Warning: This way will bring up a prompt from Word asking if you want to save changes, if there are any changes since you saved), then we quit the application

Private Sub Form_Unload(Cancel As Integer)
    'Clear the memory
    Set xlsApp = Nothing
    Set wrdApp = Nothing
End Sub

This last routine is just there to close this Visual Basic application neatly. It is good programming practice to do this.

Well I hope this brief tutorial is helpful. It does not touch on much of what you can do to the office applications once they're open, but should give you an idea of how to get started.