VB development-basic operations for calling excel2000 objects in VB Applications

I. Excel Object Model

To call excel in a VB Application, you must understand the EXCEL object model. The Excel Object Model describes the theoretical structure of Excel and provides many objects. Among them, the most important objects are:

Ii. Call Excel

When Excel is called in a VB Application, Excel is referenced as an external object, the EXCEL object model provides objects that can be programmatically manipulated from within the VB application, as well as related attributes, methods, and events.

1. Add a reference to the Excel library in the VB project.

To access the rich internal resources of EXCEL from the VB Application and make the Excel application run faster, you need to add references to the Excel library in the VB project. The procedure is as follows:

A) Select "Reference" from the "project" menu of vb5 ";

B) In the "Reference" dialog box, select the Excel Library: "Microsoft excel9.0 Object Library ";

C) Click the small box on the left to display the "√" symbol;

D) Press "OK" to exit.

Note: To Call excel in a VB Application, you must install Excel in your computer system.

2. Reference The Application Object

The application object is the top layer of the Excel object model, indicating the entire Excel application. When Excel is called in a VB application, the attributes, methods, and events of the Application object are used. To do this, first declare the object variable:

Dim vbexcel as object

Or directly declare it as an Excel object:

Dim vbexcel as Excel. Application

After declaring the object variable, assign a new or existing application object reference to the variable using the Createobject function or the GetObject function.

A) use the Createobject function to generate a new object reference:

Set vbexcel = Createobject ("Excel. application ")

The string "Excel. Application" is the programming ID of the Excel application. This variable references the Excel application itself.

B) Use the Geto field ect function to open an existing object reference:

Set appexcel = GetObject ("Samp. xls ")

The preceding statement opens the file Samp. xls.

3. common attributes and methods of application objects

Attributes and Methods
The visible attribute is true or false, indicating whether the Excel application is visible.
Left, top attribute: the position of the Excel window;
The size of the Excel window in the height and width attributes;
The windowstate attribute specifies the window status. The value is ximaximized (maximized) x1 minimized (minimized) x1 normal (default ).
Exit Microsoft Excel using the quit method;
The Calculate Method recalculates all opened workbooks, worksheets, or cells.
The Evaluate Method evaluates the mathematical expression and returns the result.

Example 1: Evaluate a mathematical expression:

Dim vbexcel as object
Set vbexcel = Createobject ("Excel. application ")
X = vbexcel. Evaluate ("3 + 5 * (COS (1/log (99. 9 )))")
3. Use an Excel application

As mentioned above, calling an Excel application in a VB Application uses the attributes, methods, and events of the Application object. The following lists common attributes and methods.

1. Use a thin working area

The workbook object represents a workbook currently opened in the Excel application, which is included in the workbooks collection. You can access a workbook object through the workbooks collection or the active workbook object that represents the current active workbook.

Common methods include:

Attribute and method meaning
The add method creates a new blank workbook and adds it to the set.
Open Method to open a workbook.
The activate method activates a workbook and changes the specified workbook to an active workbook object.
Save the existing workbook by the current path and name (if it is saved for the first time, save it to the default name, such as book1.xls ).
The saveas method saves the workbook for the first time or uses another name to save the workbook.
Close the workbook.
Print the workbook using the printout method. Syntax:

Printout (from, to, copies, preview, printer, tofile, collate)

Optional parameters:

From: print the start page number. If this parameter is omitted, it is printed starting from the starting position.
To: The ending page number. If omitted, the last page is printed.
Copies: number of copies to be printed. If omitted, only one copy is printed.
Preview: if it is true, print and preview the specified object in Excel before printing. If it is false or omitted, the object is printed immediately.
Printer: Set the name of the active printer.
Tofile: if it is true, the output is printed to the file.
Collate: if true, each copy is printed one by one.

The following statement prints three copies of the two to five pages of the active workbook:

Activeworkbook. Printout from: = 2 to 5 copies: = 3

Example 2: Generate, save, and close a workbook

Dim vbexcel as Excel. Application
Set vbexcel = Createobject ("Excel. application ")
With vbexcel
. Workbooks. Add
With activeworkbook
. Save as "C:/temp/output. xls"
. Close
End
. Quit
End

2. Use a worksheet

The sheets set indicates all worksheets in the workbook. You can use the sheets set to access, activate, add, rename, and delete worksheets. A worksheet object represents a worksheet.

Common attributes and methods include:

Attribute and method meaning
The worksheets attribute returns the sheets set.
The name attribute worksheet is renamed.
The add method creates a worksheet and adds it to the workbook.
Select a worksheet using the select method.
Copy the worksheet.
The move method moves the specified worksheet to another position in the workbook.
Delete to delete a specified worksheet.
Print the worksheet using the printout method.

Example 3: copy a worksheet from disk C to disk:

Dim vbexcel as Excel. Application
Set vbexcel = Createobject ("Excel. application ")
With vbexcel
. Workbooks. Open "C:/temp/output. xls"
. Workbooks. Open "A:/output1.xls"
. Workbooks ("output. xls"). Sheets ("sales"). Copy
. Workbooks ("output1.xls)
. Workbooks ("output1.xls"). Save
. Workbooks ("output. xls"). Close
. Workbooks ("outputi. xls"). Close
. Quit
End

3. Unit Scope

A range object represents a cell, a row, a column, a selected area, or a 3D area of a worksheet.

Common attributes and methods include:

Attribute and method meaning
In range (ARG), Arg is the A1-style symbol, indicating a single cell or cell area.
Cells (row, col) (row is the row number, Col is the column number) indicates a single cell.
The columnwidth attribute specifies the column width of all columns in the region.
The rowl3eight attribute specifies the row width of all rows in the region.
The Value Attribute specifies the values of all cells in the region (default attribute ).
The formula attribute specifies the cell formula, which is referenced by A1-style.
Select method selection range.
Copy the content of the range to the clipboard.
The c1earcontents method clears the content of the range.
The delete method deletes the specified cell range.

4. Use charts

The chart object represents the chart in the workbook. This chart can be an embedded chart (included in the chartobject object) or a discrete chart worksheet.

Common methods include:

Meaning

Add method to create a chart worksheet. Returns the chart object.
Use the prineout method to print charts.
The chartwizard method modifies the attributes of a given chart. Its syntax is:
　
Chartwizard (source, gallery, format, p1otby, categorylabels,
Serieslabels, haslegend, title, categorytitle, valuetitle, extratitle)

Where:

Source: The area that contains the source data of the new chart. If omitted, the selected embedded chart in the activity chart or activity worksheet is modified.

Gallery: Chart type. The value can be one of the following constants: xlarea, x1bar, xlcolumn, xlline, distance, xlradar, distance, xlcombination, x13darea, x13dbar, x13dcolumn, x13dline, x13dpie, x13 dsurface.

Format: Number of the built-in auto-applied format. If omitted, the default value is selected.

P1otby: Specifies whether data in the series comes from rows (xlrows) or columns (xlcolumns ).

Categorylabels: an integer that represents the number of inspector or number of columns in the source region that contains the classification mark.

Serieslabels: an integer that represents the number of experts or columns in the source region that contain the series logo.

Haslegend: if true is specified, the chart has a legend.

Title: The title Text of the chart.

Categorytitle: category axis title text.

Valuetitle: The title Text of the numeric axis.

Extratitle: the title of a Series axis of a three-dimensional chart, or the title of a second numeric axis of a two-dimensional chart.

You can use the ADD and chartwizard methods in combination to create a chart worksheet that contains the data in the worksheet. In the following example, a new line chart is generated and printed based on the data in cell area A1: A20 in the worksheet "sheetl.

With charts. Add
. Chartwizard Source: = worksheets ("sheet1"). Range ("A1: A20"), Gallery: = xlline, Title: = "line chart"
. Printout
End

5. Use Excel worksheet Functions

Most Excel worksheet functions can be used in VB statements. You can call the Excel worksheet function through the worksheetfunction object. The following sub process uses the min worksheet function to find the minimum value of cells in the specified area, and displays the result value in the message box.

Sub usefunction ()
Dim myrange as range
Set myrange = worksheets ("sheet1"). Range ("B2: F10 ")
Answer = application. worksheetfunction. Min (myrange)
Msgbox answer
End sub

If you use a worksheet function with region reference as the parameter, you must specify a range object. You can use the match worksheet function to search all cells in the A1: A10 area.

Sub findfirst ()
My Var = application. worksheetfunction. Match (9, worksheets (1). Range ("A1: A10"), 0)
Msgbox myvar
End sub

To insert a worksheet function into a cell, you can specify this function as the formula attribute value corresponding to the range object. In the following example, specify the formula attribute in A1: B3 of the current workbook in sheetl as the Rand worksheet function (this function generates two random numbers ).

Sub insertformula ()
Worksheets ("sheet1"). Range ("A1: B3"). formula = "RAND ()"
End sub

The preceding section briefly introduces some objects in the Excel object model and their attributes and methods. For more information, see the section "Microsoft Excel Visual Basic Reference" in Excel 2000. In fact, Microsoft Office applications such as Word, PowerPoint, access, and project can all be called in VB Applications. The principle and steps are the same, but their object models are different.