Excel VBA programming Summary

Recently, some internal projects in the Organization need to use Excel report generation. Although Java poi can have this ability, it may be difficult to switch.. net, with Visual Studio 2003 and Office 2003, some VBA is used. Therefore, we summarize and summarize some materials here for future research.

First, create an Excel Object and use comobj:

Dim excelid as Excel. Application

Set excelid as new excel. Application

1) display the current window:

Excelid. Visible: = true;

2) Change the Excel title bar:

Excelid. Caption: = 'ApplicationProgramCall Microsoft Excel ';

3) Add a new workbook:

 Excelid. workbooks. Add;

4) open an existing workbook:

 Excelid. workbooks. Open ('C: \ Excel \ demo.xls ');

5) set 2nd worksheets as active worksheets:

 Excelid. worksheets [2]. Activate;

Or excelid. worksheets ['sheet2']. Activate;

6) assign values to cells:

 Excelid. cells [1, 4]. Value: ='Column 4 of the first row ';

7) set the width of the specified column (unit: number of characters). Take the first column as an example:

 Excelid. activesheet. Columns [1]. columnswidth: = 5;

8) set the height of the specified row (unit: lbs) (1 lbs = 0.035 cm). Take the second behavior as an example:

 Excelid. activesheet. Rows [2]. rowheight: = 1/0. 035; // 1Centimeter

9) insert a paging character before Row 3:

 Excelid. worksheets [1]. Rows [8]. pagebreak: = 1;

10) Delete the paging character before the 8th column:

 Excelid. activesheet. Columns [4]. pagebreak: = 0;

11) Specify the border line width:

 Excelid. activesheet. Range ['b3: D4 ']. Borders [2]. Weight: = 3;

1-Left2-Right3-Top4-Bottom5-Oblique (\)6-Oblique (/)

12) Clear the cell formula in the fourth column of the first row:

 Excelid. activesheet. cells [1, 4]. clearcontents;

13) set the font attribute of the first line:

Excelid. activesheet. Rows [1]. Font. Name: = 'shanghai ';

Excelid. activesheet. Rows [1]. Font. Color: = clblue;

Excelid. activesheet. Rows [1]. Font. Bold: = True;

Excelid. activesheet. Rows [1]. Font. Underline: = true;

14) set the page:

A. header:

Excelid. activesheet. pagesetup. centerheader: ='Report demo ';

B. footer:

Excelid. activesheet. pagesetup. centerfooter: ='Page & P ';

C. The header to the top margin is 2 cm:

Excelid. activesheet. pagesetup. headermargin := 2/0. 035;

D. footer end margin 3 cm:

Excelid. activesheet. pagesetup. headermargin := 3/0. 035;

E. Top margin 2 cm:

Excelid. activesheet. pagesetup. topmargin := 2/0. 035;

F. The bottom margin is 2 cm:

Excelid. activesheet. pagesetup. bottommargin: = 2/0. 035;

G. The left side is 2 cm:

Excelid. activesheet. pagesetup. leftmargin: = 2/0. 035;

H. Right side: 2 cm:

Excelid. activesheet. pagesetup. rightmargin := 2/0. 035;

I. horizontal center of pages:

Excelid. activesheet. pagesetup. centerhorizontally: = 2/0. 035;

J. Center the page vertically:

Excelid. activesheet. pagesetup. centervertically: = 2/0. 035;

K. Print the cell network cable:

Excelid. activesheet. pagesetup. printgridlines: = true;

15) copy operation:

A. Copy the entire Worksheet:

Excelid. activesheet. Used. range. copy;

B. Copy the Specified Region:

Excelid. activesheet. Range ['a1: e2']. copy;

C. paste the file at A1:

Excelid. activesheet. range. ['a1']. pastespecial;

D. paste it from the end of the file:

Excelid. activesheet. range. pastespecial;

16) insert a row or column:

A. excelid. activesheet. Rows [2]. insert;

B. excelid. activesheet. Columns [1]. insert;

17) delete a row or column:

 A. excelid. activesheet. Rows [2]. Delete;

 B. excelid. activesheet. Columns [1]. Delete;

18) print the preview Worksheet:

 Excelid. activesheet. printpreview;

19) print the output Worksheet:

 Excelid. activesheet. printout;

20) Save the worksheet:

 If not excelid. activeworkbook. Saved then

Excelid. activesheet. printpreview

End if

21) Save the worksheet as follows:

 Excelid. saveas ('C: \ Excel \ demo1.xls ');

22) discard the storage:

 Excelid. activeworkbook. Saved: = true;

23) Close the workbook:

 Excelid. workbooks. close;

24) Exit Excel:

Excelid. Quit;

25) set the worksheet password:

Excelid. activesheet. Protect "123", drawingobjects: = true, contents: = true, scenarios: = true

26) the Excel display mode is maximized.

Excelid. application. windowstate = xlmaximized

27) The display mode of the working thin is maximized.

Excelid. activewindow. windowstate = xlmaximized

28) set the default number of thin working Blocks

Excelid. sheetsinnewworkbook = 3

29) 'whether to prompt for saving when closing (true: Save; false: Do not save)

Excelid. displayalerts = false

30) set the split window and fixed row position

Excelid. activewindow. splitrow = 1

Excelid. activewindow. freezepanes = true

31) fixed print content when printing

Excelid. activesheet. pagesetup. printtitlerows = "$1: $1"

32) set the Print Title

Excelid. activesheet. pagesetup. printtitlecolumns = ""

33) set the display mode (display by page)

Excelid. activewindow. view = xlpagebreakpreview

34) set the display Ratio

Excelid. activewindow. Zoom = 100

35) Let Excel respond to the DDE request

Ex. application. ignoreremoterequests = false

 

UseVBOperationExcel

Private sub command3_click ()

On Error goto err1

Dim I as long

Dim J as long

Dim objexl as Excel. Application'Declare object variables

Me. mousepointer = 11'Change the mouse Style

Set objexl = new excel. Application'Initialize the object variable

Objexl. sheetsinnewworkbook = 1'Set the number of created workbooks to 1.

Objexl. workbooks. Add'Add a working thin

Objexl. Sheets (objexl. Sheets. Count). Name = "book1"'Modify the workbook name

Objexl. Sheets. Add, objexl. Sheets ("book1 ")'Add the second working book after the first one

Objexl. Sheets (objexl. Sheets. Count). Name = "book2"

 Objexl. Sheets. Add, objexl. Sheets ("book2 ")'Add a third working book after the second one

Objexl. Sheets (objexl. Sheets. Count). Name = "book3"

 

Objexl. Sheets ("book1"). Select'Select the workbook <book1>

For I = 1 to 50'Write data cyclically

For j = 1 to 5

If I = 1 then

Objexl. selection. numberformatlocal = "@"'Set the format to text

Objexl. cells (I, j) = "E" & I & J

Else

Objexl. cells (I, j) = I & J

End if

Next

Next

 

Objexl. Rows ("1:1"). Select'Select the first line.

Objexl. selection. Font. Bold = true'Set to bold

Objexl. selection. Font. size = 24'Set the font size

Objexl. cells. entirecolumn. autofit'Automatically adjust the column width

Objexl. activewindow. splitrow = 1' split the first row

Objexl. activewindow. splitcolumn = 0'Split the column

Objexl. activewindow. freezepanes = true'Fixed splitObjexl. activesheet. pagesetup. printtitlerows = "$1: $1"'Set to print fixed Rows

Objexl. activesheet. pagesetup. printtitlecolumns = ""'Print the titleObjexl. activesheet. pagesetup. rightfooter ="Printing time :"&_

Format (now, "yyyyMM dd hh: mm: SS ")

Objexl. activewindow. view = xlpagebreakpreview'Set the Display Mode

Objexl. activewindow. Zoom = 100'Set the display size

'Add a password to the worksheet

Objexl. activesheet. Protect "123", drawingobjects: = true ,_

Contents: = true, scenarios: = true

Objexl. application. ignoreremoterequests = false

Objexl. Visible = true'Make Excel visible

Objexl. application. windowstate = xlmaximized'The Excel display mode is maximized

Objexl. activewindow. windowstate = xlmaximized'The method for displaying the workbook is maximized.

Objexl. sheetsinnewworkbook = 3'Change the default number of new job CIDR blocks to three.

Set objexl = nothing'Clear object

Me. mousepointer = 0'Modify the mouse

Exit sub

Err1:

Objexl. sheetsinnewworkbook = 3

Objexl. displayalerts = false' saving is not prompted when it is disabled

Objexl. Quit'Close Excel

Objexl. displayalerts = true'Saving prompt when closing

Set objexl = nothing

Me. mousepointer = 0

End sub

generally, the recording macro method is used for pivot tables, of course, you can look at the Code
dim excel as Excel. application
dim xbk as Excel. _ workbook
dim XST as Excel. _ worksheet
dim xrange as Excel. range
dim x1_tcache as Excel. export tcache
dim x1_ttable as Excel. pivotTable
dim x1_tfield as Excel. effectfield
dim cnnsr as string, SQL as string
dim rowfields () as string = {"", "", "" }< br> dim pagefields () as string = {"","","","","", ""}

'Server is the server name or IP address of the server.
'Database is the database name
'Table is the table name.

Try
'Start exporting
Cnnsr = "ODBC; driver = SQL Server; server =" + server
Cnnsr = cnnsr + "; uid =; APP = report tools; wsid = reportclient; database =" + database
Cnnsr = cnnsr + "; trusted_connection = yes"

Excel = new excel. applicationclass
Xbk = excel. workbooks. Add (true)
XST = xbk. activesheet

Xrange = XST. Range ("A4 ")
Xrange. Select ()

'Start
Xforwartcache = xbk. w.tcaches. Add (sourcetype: = 2)
Xforwardtcache. Connection = cnnsr
Xforwartcache. commandtype = 2

SQL = "select * from" + Table

Xforwartcache. commandtext = SQL
Xforwarttable = xforwartcache. createpivottable (tabledestination: = "sheet1! R3c1 ", tablename: =" PivotTable 1 ", defaultversion: = 1)

'Prepare row Fields
Rowfields (0) = "Field 1"
Rowfields (1) = "Field 2"
Rowfields (2) = "Field 3"
'Prepare the page Fields
Pagefields (0) = "field 4"
Pagefields (1) = "Field 5"
Pagefields (2) = "Field 6"
Pagefields (3) = "Field 7"
Pagefields (4) = "Field 8"
Pagefields (5) = "Field 9"
Xforwarttable. addfields (rowfields: = rowfields, pagefields: = pagefields)

Xforwartfield = xforwarttable. effectfields ("quantity ")
Xforwardfield. Orientation = 4

'Close the toolbar
'Xbk. showdeskttablefieldlist = false
'Excel. commandbars ("Export tTable"). Visible = false

Excel. Visible = true

Catch ex as exception
If CNN. State = connectionstate. Open then
CNN. Close ()
End if
Xbk. Close (0)
Excel. Quit ()
MessageBox. Show (ex. message, "report tool", messageboxbuttons. OK, messageboxicon. Warning)
End try