The realization of computer room toll system: How to export data from MSHFlexGrid control to excel in VB

In the computer room charge system, many query forms contain the same function: the data that is queried in the database is displayed in the MSHFlexGrid control, and then the data in the MSHFlexGrid control is exported to the Excel table.

Although before the student information management system, but did not relate to this function, so recorded in this, in their own, retrospective reflection, in everyone, sharing learning.

Method One: Create an empty Excel table in the root directory

1. In the same root directory with the VB project to create the data will be imported Excel table;

2, in the VB event to write code:

Private Sub Cmdexport_click ()    
    Dim i As Integer
    Dim j As Integer
    Myflexgrid.redraw = False    ' Close table redraw, speed up    
     Set xlapp = CreateObject ("Excel.Application")   ' Create Excel object    
     ' to open an existing Excel artifact book file    
     Set xlbook = XlApp.Workbooks.Open (App.Path & "\ Student on machine record. xls")    
    xlapp.visible = True ' To set the visible    
     set Xlsheet of Excel objects = Xlbook.worksheets ("Sheet1") ' Set active sheet for    
        
     i = 0 to myflexgrid.rows-1 ' Row loop for    
            j = 0 to myflexgrid.cols-1 ' column loop    
                 Myflexgrid.row = i    
               myflexgrid.col = J    
              ' Save to Excel                                                                  xlbook.worksheets (' Sheet1 '). Cells (i + 1, j + 1) = Myflexgrid.text                                       Next J    
    Next i    
    Myflexgrid.redraw = True End
Sub

Method Two: Directly refer to the Microsoft Excel 14.0 Object Library with VB

1, in the VB Designer, engineering → reference, reference Microsoft Excel 14.0 Object Library;

2, Write code:

Private Sub Cmdexport_click ()    
    Dim i As Integer
    Dim j As Integer
    Dim xlapp As Excel.Application    
    Dim xlbook As Excel.Workbook    
    Dim xlsheet as Excel.Worksheet    
    Set xlapp = CreateObject ("Excel.Application")   ' Materialized Object xlapp    
    xlapp.visible = True
    Set xlbook = XlApp.Workbooks.Add    
    Set xlsheet = Xlbook.worksheets (1)    
    For i = 0 to myflexgrid.rows-1 for    
        j = 0 to myflexgrid.cols-1    
            myflexgrid.row = i    
            myflexgrid.col = j
  xlsheet.cells (i + 1, j + 1) = Trim (myflexgrid.text)    
        next
    Next End
Sub

The first method does not refer to the Microsoft Excel 14.0 object Library, but you must create a corresponding Excel table in the root directory, and the second method does not need to create a table to refer directly to Microsoft Excel 14.0 object Library can be directly instantiated, more convenient.