VB中从MSFlexGrid记录导出为Excel

                

         机房收费系统中多个窗体用到从MSFlexGrid记录导出为Excel，在VB要导出数据到Excel中，首先要在引用中添加Microsoft  Excel 14.0 Object Library 引用，我的代码中用到了对话框，所以我添加了对话框CommonDialog控件。

 

        由于机房收费系统中多次用到这个功能，我把这些代码写到了模块中，定义了一个公有的函数，具体代码如下所示：

Public Function ExportFlexDataToExcel(flex As MSFlexGrid, g_CommonDialog As CommonDialog)   On Error GoTo ErrHandler     Dim xlApp As Object  Dim xlBook As Object  Dim Rows As Integer, Cols As Integer  Dim iRow As Integer, hCol As Integer, iCol As Integer  Dim New_Col As Boolean  Dim New_Column As Boolean   g_CommonDialog.CancelError = True  On Error GoTo ErrHandler  ' 设置标志  g_CommonDialog.Flags = cdlOFNHideReadOnly  ' 设置过滤器  g_CommonDialog.Filter = "All Files (*.*)|*.*|Excel Files" & _  "(*.xls)|*.xls|Batch Files (*.bat)|*.bat"  ' 指定缺省的过滤器  g_CommonDialog.FilterIndex = 2  ' 显示“打开”对话框  g_CommonDialog.ShowSave      If flex.Rows <= 1 Then       ‘判断表格中是否有数据         MsgBox "没有数据！", vbInformation, "警告"        Exit Function  End If   ‘打开Excel ,添加工作簿   Set xlApp = CreateObject("Excel.Application")  Set xlBook = xlApp.Workbooks.Add  xlApp.Visible = False‘遍历表格中的记录，传递到Excel中  With flex    Rows = .Rows    Cols = .Cols    iRow = 0    iCol = 1    For hCol = 0 To Cols - 1       For iRow = 1 To Rows'获取表格中值，传递到Excel单元格中          xlApp.Cells(iRow, iCol).Value = .TextMatrix(iRow - 1, hCol)       Next iRow       iCol = iCol + 1    Next hCol End With   ‘设置Excel的属性   With xlApp  .Rows(1).Font.Bold = True  .Cells.Select  .Columns.AutoFit  .Cells(1, 1).Select' .Application.Visible = True  End With   ‘获取要保存文件的文件名，选择保存路径  xlBook.SaveAs (g_CommonDialog.FileName)  xlApp.Application.Visible = True  xlApp.DisplayAlerts = False  Set xlApp = Nothing '"交还控制给Excel  Set xlBook = Nothing  MsgBox "数据已经导出到Excel中。", vbInformation, "成功" Exit Function    ErrHandler:  ' 用户按了“取消”按钮  If Err.Number <> 32755 Then  MsgBox "数据导出失败！", vbCritical, "警告"  End IfEnd Function     

      定义好公有函数之后，我们就可以在窗体中多次调用它，格式为：       

    Call ExportFlexDataToExcel  MSFlexGrid, CommonDialog

其中MSFlexGrid为MSFlexGrid的名字，CommonDialog为对话框名。