Use vb.net to write programs that control Excel

excel| Program | control

Use vb.net to write programs that control Excel

You've always written some VBA programs in Excel, and you can basically solve the problem with hundreds of lines of code. Never used VC or VB, Delphi to do this kind of thing. I think if the report, the relative size and function of a little more, this should be the focus of Excel development.

This time using the vb.net to develop a access to Excel read data and modify the small program, there are some empirical things and everyone share, if wrong also please correct me.

This is roughly divided into several sections to illustrate:

1, the preparation part:

In the preparation section we need to add an Excel library reference to the project: I use office2003, and its library file is the Microsoft Excel 11.0 Object Library. If you use excel2000, it uses the Microsoft Excel 9.0 Object Library.

When necessary, we add the Imports namespace.

In this program I just access the data in Excel, do not need a namespace ha.

2. Declare some objects:

These objects are not all needed, the front three should have, the back, what you need to declare the object:

Dim Excelapp as Excel.Application ' declares a Application object

Dim ExcelWorkbook as Excel.Workbook ' declares a Workbook object

Dim ExcelSheet as Excel.Worksheet ' declares a Worksheet object

Wait a minute... Such as:

Dim Excelrange as Excel.Range ' declares a Range object

3. Access an Excel file:

Excelapp = New Excel.Application

ExcelWorkbook = Excelapp. Workbooks.Open (strfile) ' Access to workbook: This strfile is the path to the file, which I get from the Open File dialog box.

ExcelSheet = ExcelWorkbook. Sheets.item (1) ' Access to worksheet: Item uses index value to get reference to sheet object

Excelsheet.activate

We can get the values in this file:

Define a String variable:

Dim Strcellvalue As String

Strcellvalue=excelsheet.cells (1,1). Value ' Gets the contents of the A1 cell.

When you modify it, the reverse is OK:

Excelsheet.cells (1,1). value= Strcellvalue

4, after the aftermath:

At every exit of the program, manipulate the Excel object, save or not save:

Excelworkbook.save

Excelworkbook.close

Excelapp=nothing

5 Other:

Need to be aware that the code above requires some fault-tolerant mechanisms, such as:

If not ExcelWorkbook are nothing Then this way to achieve the correct operation of the program

You need to judge that Excel is not open now. If an Excel process is running. Your code is likely to affect the open Excel, such as Excelworkbook.close, although the previous very good only specified the workbook, but not very arbitrary reckless operation, we need to determine the current operation of Excel.