Full Control of Excel:
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: = 'Application Program Call microsoftexcel ';
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 []. Value: = 'column 4 in 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; // 1 cm
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-left 2-Right 3-Top 4-bottom 5-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:
Ifnotexcelid. activeworkbook. savedthen
Excelid. activesheet. printpreview
Endif
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
Example of using VB to Operate ExcelCode
Private sub command3_click ()
On Error goto err1
Dim I as long
Dim J as long
Dim objexl as Excel. Application 'declares 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' adds a working thin
Objexl. Sheets (objexl. Sheets. Count). Name = "book1" 'modify the workbook name
Objexl. Sheets. Add, objexl. Sheets ("book1") 'add a second working thin after the first one
Objexl. Sheets (objexl. Sheets. Count). Name = "book2"
Objexl. Sheets. Add, objexl. Sheets ("book2") 'add a third working thin after the second
Objexl. Sheets (objexl. Sheets. Count). Name = "book3"
Objexl. Sheets ("book1"). Select a 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
Endif
Next
Next
Objexl. Rows (""). Select the first line.
Objexl. selection. Font. Bold = true 'is set to bold
Objexl. selection. Font. size = 24 'set the font size
Objexl. cells. entirecolumn. autofit automatically adjusts the column width.
Objexl. activewindow. splitrow = 1' split the first row
Objexl. activewindow. splitcolumn = 0' split Column
Objexl.Activewindow. freezepanes = True' fixed split objexl. activesheet. pagesetup. printtitlerows = "$1: $1" 'set to print fixed Rows
Objexl. activesheet. pagesetup. printtitlecolumns = "" 'print the title objexl. activesheet. pagesetup. rightfooter = "Print time :"&_
Format (now, "Mm DD, yyyy hh: mm: SS ")
Objexl. activewindow. view = xlpagebreakpreview 'sets the display mode.
Objexl. activewindow. Zoom = 100 'sets 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' to make Excel visible
Objexl. application. windowstate = xlmaximized 'excel display mode is maximized
Objexl. activewindow. windowstate = xlmaximized
Objexl. sheetsinnewworkbook = 3'. Change the default number of new workbooks to three.
Set objexl = nothing 'clear the object
Me. mousepointer = 0' modify the mouse
Exitsub
Err1:
Objexl. sheetsinnewworkbook = 3
Objexl. displayalerts = false' saving is not prompted when it is disabled
Objexl. Quit close Excel
Objexl. displayalerts = true' when it is disabled, the system prompts to save
Set objexl = nothing
Me. mousepointer = 0
End sub