Export an Excel table using VB

Select the reference bar from the project menu; select Microsoft
Excel
14. 0 object library, and then select "OK ". Indicates that the Excel Type Library is to be referenced in the project.

The next step is to export data from the mshflexgrid control to an Excel table (Note: The mshflexgrid control starts from 0th rows and 0th columns, while the Excel control starts from 1st rows and 1st columns ). The Code is as follows:

Dim I as long dim J as long if myflexgrid. textmatrix (1, 0) = "" Then msgbox "no data export", vbinformation, "prompt" Exit sub end if 'create an Application Object dim excelapp as Excel. application set excelapp = new excel. application 'Bind set excelapp = Createobject ("Excel. application ") 'write data to the Excel file dim exbook as Excel. workbook dim exsheet as Excel. worksheet set exbook = excelapp. workbooks. add excelapp. sheetsinnewworkbook = 1' is set to visible excelapp. visible = true': Write the content displayed in the mshflexgrid control to excel with excelapp. activesheet for I = 1 to myflexgrid. rows for j = 1 to myflexgrid. cols. cells (I, j ). value = "" & format $ (myflexgrid. textmatrix (I-1, J-1) next J next I end with 'empty and close set exsheet = nothing set exbook = nothing set excelapp = nothing

The above is only the most basic content used for interaction between EXCEL and VB. Here we will make some extensions:

Create an Excel file
Object, using comobj:

Dimexcelapp as Excel. Application

Setexcelapp as new excel. Application

1) display the current window:

Excelapp. Visible = true

2) Change Excel
Title bar:

Excelapp. Caption = 'student computer record ';

3) Add a new workbook:

Excelapp. workbooks. Add;

4) open an existing workbook:

Eexcelapp.workbooks.open('cexceldemo.xls ');

5) set 2nd worksheets as active worksheets:

Excelapp. worksheets (2). Activate;

Or excelapp. worksheets ('sheet2'). Activate;

6) assign values to cells:

Excelapp. cells (). value = 'first row, fourth column ';

12) Clear the cell formula in the fourth column of the first row:

Excelapp. activesheet. cells (1, 4). clearcontents;

13) set the font attribute of the first line:

Excelapp. activesheet. Rows (1). Font. Name = 'shanghai ';

Excelapp. activesheet. Rows (1). Font. Color = clblue;

Excelapp. activesheet. Rows (1). Font. Bold = true;

Excelapp. activesheet. Rows (1). Font. Underline = true;

 

 

K. Print the cell network cable:

Excelapp. activesheet. pagesetup. printgridlines = true;

15) copy operation:

A. Copy the entire Worksheet:

Excelapp. activesheet. Used. range. copy;

B. Copy the Specified Region:

Excelapp. activesheet. Range ('a1e2'). copy;

C. paste the file at A1:

Excelapp. activesheet. range. ('a1'). pastespecial;

D. paste it from the end of the file:

Excelapp. activesheet. range. pastespecial;

16) insert a row or column:

A. excelapp. activesheet. Rows (2). insert;

B. excelapp. activesheet. Columns (1). insert;

17) delete a row or column:

A. excelapp. activesheet. Rows (2). Delete;

B. excelapp. activesheet. Columns (1). Delete;

18) print the preview Worksheet:

Excelapp. activesheet. printpreview;

19) print the output Worksheet:

Excelapp. activesheet. printout;

20) Save the worksheet:

If notexcelapp. activeworkbook. Saved then

Excelapp. activesheet. printpreview

End if

21) Save the worksheet as follows:

Excelapp.saveas('cexceldemo1.xls ');

22) discard the storage:

Excelapp. activeworkbook. Saved = true;

23) Close the workbook:

Excelapp. workbooks. close;

24) Exit Excel:

Excelapp. Quit;

25) set the worksheet Password

Excelapp. activesheet. Protect "123", drawingobjects = true, contents = true, scenarios = true

26) the Excel display mode is maximized.

Excelapp. application. windowstate = xlmaximized

27) The display mode of the working thin is maximized.

Excelapp. activewindow. windowstate = xlmaximized

28) set the default number of thin working Blocks

Excelapp. sheetsinnewworkbook = 3

29) 'whether to prompt for saving when closing (true
Save; false
Do not save)

Excelapp. displayalerts = false