Data center management system-link between vb and excel 2, data center management system excel

Source: Internet
Author: User

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

  1. 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)
  2. 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:

 

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 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.