Data center management system-link between VB and Excel, data center management system excel

Data center management system-link between VB and Excel, data center management system excel

Today, I found a button "export to excel" when I checked the computer status in the data center management system-students. When I go, I have to link my feelings to an excel table. As a result, I broke my little silver tooth and tried to query the connection between vb and excel one afternoon.

My general idea is:

First, reference the Excel Type Library in vb.

Second, declare the excel object. Here, we do not just declare an excel table, but also declare workbooks and worksheets.

The following code:

	Dim xlApp As Excel. Application
	Dim xlBook As Excel. Workbook
	Dim xlSheet As Excel. Worksheet

 

Finally, you can operate the excel table in the VB program.

Basic operations in the IDC management system include:

	Set xlApp = CreateObject ("Excel. Application") 'create an EXCEL Application class
	XlApp. Visible = true' set EXCEL to be Visible
	Set xlBook = xlApp. Workbooks. Open (App. Path & "\ create Microsoft Excel worksheet .xls") 'Open the EXCEL Workbook
	Set xlSheet = xlBook. Worksheets (1) 'open an EXCEL worksheet
	XlSheet. Activate a worksheet
	XlSheet. Cells (1, 1) = myflexgrid. TextMatrix (0, 0 )...... 'Assign a value to the driving column of cell 1
	XlBook. RunAutoMacros (xlAutoOpen) 'Run the start macro in EXCEL

In addition, there are some expansion operations:

XlSheet. PrintOut print the worksheet XlBook. Close (True) 'Close the workbook XlApp. Quit 'End the EXCEL Object Set xlApp = Nothing 'to release the xlApp object

Question 1 (The Role of excel macros ):

EXCEL provides a visual basic editor that opens the Visual Basic Editor. In the Project Properties window, right-click the menu and choose "insert module" to add Module 1 ", in this module, functions and processes can be written in Visual Basic and called macros. EXCEL has two automatic macros: Sub Auto_Open () and SubAuto_Close ()). Their features are: When you use EXCEL to create a workbook containing a macro, the macro will be automatically started. Similarly, when you close a workbook that contains a disabled macro, the macro is automatically disabled. However, when you use the automation function of VB to call an EXCEL worksheet, starting or disabling macros does not run automatically. Instead, you need to use the command xlBook in VB. runAutoMacros (xlAutoOpen) and xlBook. runAutoMacros (xlAutoClose) to run start macro and close macro. (My understanding is that VBA in EXCEL is the application of VB in EXCEL)

Question 2 (subscript out of bounds ):

 

The problem should be solved on the flexgrid Control. When designing the interface, you can solve the problem by setting the initial number of columns to 9.

 

Note:

When using the preceding VB command to operate an EXCEL table, the vbprogram can continue to perform other operations, close the EXCEL file, and perform operations on the EXCEL file unless the EXCEL file is invisible. However, when you close an EXCEL object during an EXCEL operation, you cannot know the vbprogram. If you use an EXCEL object at this time, the vbprogram will generate an automation error. The formation of VB program cannot fully control the EXCEL, making VB and EXCEL out of touch.

Excel installation path when connecting vb to excel

It may be the version number of the reference. The version number of 2007 is different from that of 2003. When it reaches 2003, the reference will be reported to be lost.

How does VB contact EXCEL?

In "Project-Reference", select Microsoft Excel ...... Selected
Private Sub commandementclick ()
Dim xl As Excel. Application
Dim xlbook As Excel. Workbook
Set xl = CreateObject ("excel. application ")
Xl. Visible = False
Dim st As Excel. Worksheet
Set xlbook = xl. Workbooks. Open (App. Path & "\ date.xls ")
Set st = xlbook. Worksheets (1)
Dim I As Integer
I = 1
Do While st. Cells (I, 1). Value <> ""
List1.AddItem st. Cells (I, 1). Value
I = I + 1
Loop
Set xl = Nothing
Set xlbook = Nothing
End Sub

Notes:
'First, you need to replace the Mic in "Project-reference... Excel... Selected
'Set xlBook = xlApp. Workbooks. Open ("file name") 'Open an existing EXCEL Workbook File
'Xlapp. Visible = true' sets the EXCEL object to be Visible (or invisible)
'Set xlSheet = xlBook. Worksheets ("table name") 'sets the activity Worksheet
'Range ("K12"). Select sets the active cell
'Xlsheet. Cells (Row, col) = value' assigns a value to the cell (row, col ).
'Xlsheet. printout' prints the worksheet
'Xlbook. Close (True) 'Close the workbook
'Xlapp. quit' ends an EXCEL Object
'Set xlApp = Nothing 'to release the xlApp object.
'Xlbook. RunAutoMacros (xlAutoOpen) 'Run the EXCEL macro
'Xlbook. RunAutoMacros (xlAutoClose) 'Run EXCEL to close the macro
'Xlbook. Worksheets. count' total number of workbook labels