Project 3 – Excel Automation with Visual Basic

The lecture prior to this project will present valuable information pertaining to the Excel Object Library and specifics on writing code to expose the functionality behind Microsoft Office Applications.

Objectives

The last project focused on declaring variables (as strings and singles) to compute and output values in a PictureBox control. This project will focus on using variables that are objects. In particular, this project will use the Microsoft Excel Object Library as a variable object. In this Project, you will use Automation to control and exchange information with Microsoft Excel.

After completing this lab, you will be able to use Automation to:

® Open a Microsoft Excel workbook.

® Change information on a worksheet.

® Create a chart from worksheet information.

 

Exercises

Exercise 1: Controlling Microsoft Excel

In this exercise, you will control Microsoft Excel using Automation.

Exercise 1: Controlling Microsoft Excel

In this exercise, you will control Microsoft Excel using Automation. A Microsoft Excel workbook named Earn.xls has been created for you and can be downloaded from my homepage via the ITP150 web link (www-rcf.usc.edu/~anthonyb). The TA’s will also have the Earn.xls file for you to work on in the lab (in case you don’t download the file from the web page).

A portion of the workbook is shown in the following illustration. Note that the cells C3 and C4 have been named "Growth" and "Inflation," respectively. Also, cells C16:E16 have been named "Net_Profit."

NOTE: Make sure the earn.xls file is in the same folder as your Visual Basic Project.

 

 

u Create a project

1. Start a new Visual Basic project.

2. Modify Form1 to resemble the following:

3. Save the project as XLProject.vbp in a folder titled with your lastname.

u Create an instance of the Microsoft Excel application object

1. Add a reference to the Microsoft Excel 5.0 Object Library.

2. Dimension form-level variables named xl and xlChart. (xl should be an excel.application data type, and xlChart should be of chart type).

3. In the Click event procedure for the Create XL Object button, use the CreateObject function to create an instance of the Microsoft Excel Application object (this is also called instantiation), and assign that object to the variable created in the previous step. For more information see Creating an Instance of Microsoft Excel.

    set xl = CreateObject("Excel.Application")

4. Set the Visible property of Microsoft Excel to True.

5. Use the Microsoft Excel Open method to open Earn.xls.

    x1.Workbooks.Open App.Path & "\Earn.xls"

    xl.Visible = True

6. Test the application. The application should launch the Microsoft Excel Application and open up the earn.xls spreadsheet located in the same folder as your visual basic project.
  1. Exit.

 

u Send information to Microsoft Excel
  1. In the Click event procedure for the Estimate Earnings button, activate the Earnings worksheet and set the values of cells Growth and Inflation to the values of the Growth and Inflation text boxes, respectively.
  2. To activate the Earnings Worksheet, type the variable name, followed by the worksheets property. After you state the property (remember: object.property), you need to specify a value in parenthesis and quotes: ("worksheet_name"). Make sure you substitute the worksheet_name with the actual worksheet you are using from earn.xls. The last thing you need to do is the activate method to initialize the worksheet. The entire line should resemble this:

    3.     object.property("worksheet_name").Method

  3. The next statements for this procedure require you set the growth range and inflation range. Remember, the growth range has already been assigned a name, as well as the inflation range. You should be assigning the range to the text value. The statement for the second line (setting the range for growth) should resemble the following structure:

    4.     object.property("worksheet_name").Range("range_name").Value = text1.text

  4. The last statement is very similar to the previous statement. The only difference is instead of specifying the range as Growth, we will specify the range as Inflation.

5. Test the application.

    — Run the application.

    — Click the Create XL Object button to start Microsoft Excel and open the workbook.

    — Switch to Visual Basic, enter percentages in the text boxes, and test the functionality of the Estimate Earnings button.

    3. Exit Microsoft Excel.

    u Create a chart

    1. In the Click event procedure for the Chart Earnings button, if the xlChart variable added above is Nothing, use the Select method to select the named Range Net_Profit of the Earnings worksheet.

    If xlChart Is Nothing Then   ' checks to see if the Excel chart is running

    xl.Worksheets("Earnings").Range("net_profit").Select

    Note In these steps, you should use the xl module-level variable so it can be used from within all procedures in this exercise.

    2. Use the Add method of the Chart object to create a new chart in the workbook and initialize xlChart.

    set xlChart = xl.Charts.Add()

    3. Set the Type property of the Chart object to xl3DColumn. This is a constant in the Microsoft Excel object library.

    xlChart.Type = xl3DColumn

    4. If the xlChart variable is not Nothing, use the Activate method to make it the active sheet.

    Else

    xlChart.Activate      ' if chart is still in memory, this line activates it

    End If

    5. Test the application.

    — Run the application.

    — Click the Create XL Object button to start Microsoft Excel and open the workbook.

    — Switch to Visual Basic and click the Chart Earnings button.

    u Exit Microsoft Excel

    1. In the Click event procedure for the Exit button, use the Close method to close the ActiveWorkbook object.

    2. Use the Quit method to close Microsoft Excel.

    3. Unload the form and end the application.

    4. Test the application.

    — Run the application.

    — Click the Create XL Object button to start Microsoft Excel and open the workbook.

    — Switch to Visual Basic and click the Exit button.

  1. Save the project. Submit the folder titled ‘your lastname + first initial’ (e.g. borqueza) and copy it to the Maverick Server from the ITP Labs. You will find a folder under the project directory titled: Project3.

GOOD LUCK!