VB read and write Excel

Recently, Excel and VB are used more than excel, So I simply learned how to process excel in VB. Let's talk about it today. To call excel in VB, open the reference project in the "project" menu of the vbprogramming environment, and select the "Microsoft Excel 11.0 Object Library" item in the project. Because your Excel version number is different, the version number of this option is also different. Because Excel organizes objects in hierarchies, its object model contains many different object elements. Level 1: Application object, that is, Excel itself;

Level 2: workbooks object set, which refers to the workbook file in Excel;

Layer 3: worksheets object set, which indicates a worksheet in Excel;

Layer 4: cells and range objects, pointing to cells in an Excel worksheet.

 

Create a VB project and put a button named excel_out. Define the layers first:

Dim xlapp as Excel. Application 'excel object
Dim xlbook as Excel. Workbook 'workbook
Dim xlsheet as Excel. worksheet 'Worksheet

What we plan to do is: Open/create an Excel file, change the value of a certain unit of a work table in the current state, and then refer to the test.xls file.

Private sub excel_out_click ()
Dim I, j as integer
Set xlapp = Createobject ("Excel. application") 'create an Excel Object
'Set xlbook = xlapp. workbooks. Open (App. Path & "/test.xls") 'Open the existing test.xls File
Set xlbook = xlapp. workbooks. add' create an Excel worksheet File
'Xlbook. runautomacros (xlautoopen) 'execute EXCEL to start the macro
'Xlbook. runautomacros (xlautoclose) 'execute EXCEL to close the macro
Xlapp. Visible = true' sets the EXCEL object to be visible (or invisible)
Set xlsheet = xlbook. worksheets (1) 'set the activity worksheet''
''~~~ The first page of the current workbook, which can also be changed to "table name"

'The following is a simple way to write numbers in some cells.
For I = 7 to 15
For j = 1 to 10

Xlsheet. cells (I, j) = j 'column J of row I on the first page of the current workbook
Next J
Next I

With xlsheet ', set the border to solid
. Range (. cells (7, 1),. cells (28, 29). Borders. linestyle = xlcontinuous
End
 
'Reference the second page of the current workbook
Set xlsheet = xlapp. application. worksheets (2)
Xlsheet. cells (7, 2) = 100' write 2008 to the 7th columns in the second row of the second page
Xlsheet. saveas app. Path & "/test.xls" 'Save disks by specified file name
'Set xlbook = xlapp. application. workbooks. add' create a blank workbook.

Xlapp. Quit 'end Excel object' xlapp. workbooks. Close

Set xlapp = nothing 'to release the xlapp object

End sub

In this way, we can simply operate on Excel files. Here are some more useful materials I have found on the Internet:

1. Create an Excel Object
EOLE = Createobject ('excel. application ′)
2. Add a new workbook
EOLE. workbooks. Add
3. Set 3rd worksheets to activate worksheets.
EOLE. worksheets ("sheet3"). Activate
4. Open a specified workbook
EOLE. workbooks. Open ("C:/temp/ll.xls ″)
5. display the Excel form
EOLE. Visible =. T.
6. Change the Excel title bar
EOLE. Caption = "VB Application calls Microsoft Excel ″
7. assign values to cells
EOLE. cells (). value = xm (XM is the database field name)
8. Set the width of the specified column (unit: number of characters)
EOLE. activesheet. Columns (1). columnwidth = 5
9. Set the height of the specified row (unit: lbs)
EOLE. activesheet. Rows (1). rowheight = 1/0. 035
(Set the Row Height to 1 cm, 1 lb = 0.035 cm)
10. Insert a paging character before the first row
EOLE. worksheets ("sheet1"). Rows (18). pagebreak = 1
11. Delete the paging character before the 4th column
EOLE. activesheet. Columns (4). pagebreak = 0
12. Specify the border line width (the number of borders segments is as follows)
Ole. activesheet. Range ("B3: D3"). Borders (2). Weight = 3
13. Set the four border line types
EOLE. activesheet. Range ("B3: D3"). Borders (2). linestyle = 1
(Number of borders in this example: 1-left, 2-right, 3-top, 4-bottom, 5-oblique, 6-oblique/; linestyle value: 1 and 7-thin, 2-thin, 4-dotted, 9-double thin)
14. Set the header
EOLE. activesheet. pagesetup. centerheader = "Report 1 ″
15. Set footer
EOLE. activesheet. pagesetup. centerfooter = "Page & P ″
16. Set the header to the top margin to 2 cm
EOLE. activesheet. pagesetup. headermargin = 2/0. 035
17. Set the footer margin to 3 cm
EOLE. activesheet. pagesetup. footermargin = 3/0. 035
18. Set the top margin to 2 cm.
EOLE. activesheet. pagesetup. topmargin = 2/0. 035
19. Set the bottom margin to 4 cm.
EOLE. activesheet. pagesetup. bottommargin = 4/0. 035
20. Set the left margin to 2 cm
Veole. activesheet. pagesetup. leftmargin = 2/0. 035
21. Set the right margin to 2 cm
EOLE. activesheet. pagesetup. rightmargin = 2/0. 035
22. Set page horizontal center
EOLE. activesheet. pagesetup. centerhorizontally =. T.
23. Set the vertical center of the page
EOLE. activesheet. pagesetup. centervertically =. T.
24. Set the page size (1-narrow Row 8511 39-Wide Row 1411)
EOLE. activesheet. pagesetup. papersize = 1
25. Print the cell network cable
EOLE. activesheet. pagesetup. printgridlines =. T.
26. Copy the entire Worksheet
EOLE. activesheet. usedrange. Copy
27. Copy a specified region
EOLE. activesheet. Range ("A1: E2"). Copy
28. Paste
EOLE. Worksheet ("sheet2"). Range ("A1"). pastespecial
29. Insert a row before row 2nd
EOLE. activesheet. Rows (2). insert
30. Insert a column before Column 2nd
EOLE. activesheet. columns (2). insert
31. Set Font
EOLE. activesheet. cells (2, 1). Font. Name = "″
32. Set the font size
EOLE. activesheet. cells (1, 1). Font. size = 25
33. Set the font to italic
EOLE. activesheet. cells (1, 1). Font. italic =. T.
34. Set the font of the entire column to bold.
EOLE. activesheet. Columns (1). Font. Bold =. T.
35. Clear cell Formula
EOLE. activesheet. cells (1, 4). clearcontents
36. Print the preview Worksheet
EOLE. activesheet. printpreview
37. Print the output Worksheet
EOLE. activesheet. Printout
38. Save the worksheet
EOLE. activeworkbook. saveas ("C:/temp/22.xls ″)
39. Discard the disk
EOLE. activeworkbook. Saved =. T.
40. Close the workbook
EOLE. workbooks. Close
41. Exit Excel
EOLE. Quit