1) Create Excel objects
The Excel object model includes 128 different objects, from simple objects such as rectangles, text boxes, to PivotTable reports, diagrams, and other complex objects. Here is a brief description of the most important and the five most used objects.
(1) Application Object
The Application object is at the top level of the Excel object hierarchy, representing the environment in which Excel itself runs.
(2) Workbook object
The workbook object is directly below the Application object, representing an Excel workbook file.
(3) Worksheet Object
The worksheet object is contained in the Workbook object, representing an Excel worksheet.
(4) Range object
A Range object is contained in a Worksheet object that represents one or more cells in an Excel worksheet.
(5) Cells object
The cells object is contained in a Worksheet object that represents a cell in an Excel worksheet.
If you want to start an Excel, use the workbook and Worksheet objects, the following code launches Excel and creates a new workbook that contains one worksheet:
Dim zsbexcel As Excel.Application
Set zsbexcel = New Excel.Application
zsbexcel.Visible = True
如要Excel不可见,可使zsbexcel.Visible = False
zsbexcel.SheetsInNewWorkbook = 1
Set zsbworkbook = zsbexcel.Workbooks.Add
2) Set cell and range values
To set the value of each cell in a worksheet, you can use the Range property or the Cells property of the worksheet object.
With zsbexcel.ActiveSheet
.Cells(1, 2).value = "100"
.Cells(2, 2).value = "200"
.Cells(3, 2).value = "=SUM(B1:B2)"
.Range("A3:A9") = "中国人民解放军"
End With
To set the font and border of a cell or range, you can take advantage of the borders and font properties of the Range object or the Cells object:
With objexcel.ActiveSheet.Range("A2:K9").Borders '边框设置
.Line = xlBorderLine
.Weight = xlThin
.ColorIndex = 1
End With
With objexcel.ActiveSheet.Range("A3:K9").Font '字体设置
.Size = 14
.Bold = True
.Italic = True
.ColorIndex = 3
End With
With an in-depth understanding of the various settings for Excel cell and range values, you can create complex, aesthetically pleasing, and responsive reports that are specific to your needs.