Data center management system-link between vb and excel 2, data center management system excel
Since the documents I was looking for made me think that creating a table is a huge misunderstanding, I directly created a table in the folder of the student management system, open the table directly during running. However, there are still many misunderstandings.
Later, I saw Zhou Kun's blog and felt that he was much better than my code. Many problems in my code are solved here. (ORZ !!! After one afternoon, I suddenly felt a bit white .)
Click the "export as excel" button to create a table and write myflexgrid to the table.
Let's take a look at my code:
|
Set xlApp = CreateObject ("Excel. Application") 'create an EXCEL Application class |
|
XlApp. Visible = true' set EXCEL to be Visible |
1. |
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 |
2. |
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 |
- I have already defined it, and this line of code is to open an existing workbook, but the add method is to create a new workbook. The new workbook becomes the active workbook of the current table. (I always thought "add" means adding content)
- In this line of code, I can only control the first column of the first row of the table. What if dozens of rows are displayed? You can use loops to view the code in the system.
Let's take a look at the code I saw today:
Dim xlsAppAs Excel. Application 'defines Excel programs
DimxlsBook As Excel. Workbook 'defines a Workbook
DimxlsSheet As Excel. Worksheet 'defines a Worksheet
|
Dim I As Long |
|
Dim j As Long |
SetxlsApp = CreateObject ("Excel. Application") 'create an Application
Set xlsBook = xlsApp. Workbooks. Add |
Create a new workbook. The new workbook becomes the active workbook of the current table. |
SetxlsSheet = xlsBook. Worksheets (1) 'sets the application table
With xlsApp
. Rows (1). Font. Bold = True |
How to set the font in excel |
End
'Write the content of myflexgrid. Rows to the workbook.
For I = 0 To myflexgrid. Rows-1 |
Loop statements are well utilized. |
For j = 0 To myflexgrid. Rows-1 |
|
XlsSheet. Cells (I + 1, j + 1) = "'" & myflexgrid. Rows. TextMatrix (I, j) |
'The above can clearly summarize this rule. |
Next j
Next I
XlsApp. Visible = true' display workbooks
'Xlssheet. PrintOut preview: = true' to go to the print preview page.
'Xlsbook. SaveAs App. Path & "\ Test.xls" 'Save Path and file name |
It would be too troublesome to click a table and the file name would be the same. So I think this code is a bit redundant. |
SetxlsApp = Nothing 'release Control
This is much better than I did.
Extended: (more excel operations)
1) display the current window:
|
ExcelID. Visible: = True; |
2) Change the Excel title bar:
ExcelID. Caption: =' |
The application calls Microsoft Excel '; |
3) Add a new workbook:
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). Use 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; |
Vb reads the data of an excel file, copies the data to another excel2 file, and saves the name of excel2 by time. For more information, see add.
On error resume next
'Enable error capture. If an error occurs, go to the next line.
Dim myapp as object
Dim wk1 as object, wk2 as object
Set myapp = CreateObject ("Excel. Application ")
Myapp. enableevents = false
'Cancel the EXCEL program to respond to the event
Myapp. calculation =-4135
'Cancel automatic formula update
Myapp. visible = false
'Cancel EXCEL program display
Set wk1 = myapp. workbooks. open ("E: \ REPORT. XLS", 1)
'Read-only open the corresponding table
If wk1 is nothing then
Msgbox "An error occurred while opening the worksheet! "& Chr (10) & err. description
Exit sub
'Exit the program if a thin working error is opened.
Endif
Err. clear
Set wk2 = myapp. workbooks. add
'Add a blank workbook.
Wk1.sheets ("sheet1"). cells. copy
'Copy content
Wk2.sheets ("sheet1"). range ("a1"). PasteSpecial-4163 'xlpastevalues
'Paste the data content first
Wk2.sheets ("sheet1"). range ("a1"). PasteSpecia-4122 'xlpasteformats
'Paste the cell format
Wk2.saveas "D: \" & format (now (), "YYYYMMDDHH") & ". xls"
'Save the worksheet
Wk2.close 0
Wk1.close 0
Set wk2 = nothing
Set wk1 = nothing
Set myapp = nothing
'Close the working thin, release the object
Copy content from sheet1 to sheet2 using VB code in excel
Without specific instructions, I can only give an example and answer it myself:
In column A of Sheet1, locate the cells with A data value greater than 5 (assuming there is only one) and copy the cells B1 to sheet2.
Dim I As Long
For I = 1 To Sheets ("Sheet1"). [A65535]. End (xlUp). Row
If Sheets ("Sheet1"). Cells (I, 1)> 5 Then
Sheets ("Sheet2"). Cells (1, 2) = Sheets ("Sheet1"). Cells (I, 1)
End If
Next I
Sheets ("Sheet1"). [A65535]. End (xlUp). Row is used to obtain the maximum number of rows not empty in column.