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 calls 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 []. 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; // 1 cm
9) insert a paging character before Row 3:
ExcelID. WorkSheets [1]. Rows [8]. PageBreak: = 1;
Reference code: ActiveSheet. HPageBreaks (1). Location = Range ("A22 ")
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: = \ 'lishu \';
ExcelID. ActiveSheet. Rows [1]. Font. Color: = clBlue;
ExcelID. ActiveSheet. Rows [1]. Font. Bold: = True;
ExcelID. ActiveSheet. Rows [1]. Font. UnderLine: = True;
14) on the page? Br/> 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) \ 'indicates whether to save when it is disabled (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
Use VB to Operate Excel
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 \ 'to Add a workbook.
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 \ 'Select a workbook <book1>
For I = 1 To 50 \ 'Write Data cyclically
For j = 1 To 5
If I = 1 Then
ObjExl. Selection. NumberFormatLocal = "@" \ 'sets the format to text
ObjExl. Cells (I, j) = "E" & I & j
Else
ObjExl. Cells (I, j) = I & j
End If
Next
Next
ObjExl. Rows (""). Select \ '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 \ 'Auto Adjust 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 "\'
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.
ObjExl. ActiveSheet. PageSetup. Orientation = xlLandscape 'sets the print direction (horizontal)
\ '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 \ 'excel display mode to maximize
ObjExl. ActiveWindow. WindowState = xlMaximized \ 'work thin display mode to maximize
ObjExl. SheetsInNewWorkbook = 3 \ '. Change the default number of new job shards 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 \ 'indicates saving when it is disabled
Set objExl = Nothing
Me. MousePointer = 0
End Sub