vb用陣列方式快速匯出MSFlexGrid表格資料到Excel表格中

Public Sub ExportToExcel(ByRef objGrid As MSHFlexGrid, ByVal strFileName As String, Optional StartRow As Long = 1, Optional StartColumn As Long = 1)
    Dim objApp As Object
    Dim objWorkbook As Object
    Dim objWorksheet As Object
    Dim objRange As Object
    Dim CellsData() As String
    Dim i As Long, j As Long
    Dim nRows As Long, nColumns As Long

    '構造二維陣列
    nRows = objGrid.Rows
    nColumns = objGrid.Cols
    ReDim CellsData(1 To nRows, 1 To nColumns)
    For i = 1 To nRows
        For j = 1 To nColumns
            CellsData(i, j) = objGrid.TextMatrix(i - 1, j - 1)
        Next
    Next

    '匯出到Excel中
    If StartRow < 1 Then StartRow = 1
    If StartColumn < 1 Then StartColumn = 1
    Set objApp = CreateObject("Excel.Application")
    objApp.ScreenUpdating = False '禁止螢幕重新整理
    Set objWorkbook = objApp.Workbooks.Add
    Set objWorksheet = objWorkbook.Sheets.Add
    Set objRange = objWorksheet.Range(objWorksheet.Cells(StartRow, StartColumn), objWorksheet.Cells((StartRow - 1) + nRows, (StartColumn - 1) + nColumns))
    objRange.Value = CellsData
    objWorkbook.SaveAs strFileName '儲存到指定檔案
    objWorkbook.Close
    objApp.Quit '退出Excel
    Set objRange = Nothing
    Set objWorksheet = Nothing
    Set objWorkbook = Nothing
    Set objApp = Nothing

    '銷燬二維陣列
    Erase CellsData
End Sub

Option Explicit

Private Sub Form_Load()
    Dim i As Long, j As Long

    '填充測試用資料
    Me.MSHFlexGrid1.Rows = 2000
    Me.MSHFlexGrid1.Cols = 10
    For i = 0 To Me.MSHFlexGrid1.Rows - 1
        For j = 0 To Me.MSHFlexGrid1.Cols - 1
            Me.MSHFlexGrid1.TextMatrix(i, j) = i & "行" & j & "列"
        Next
    Next
    Debug.Print Me.MSHFlexGrid1.TextArray(100)
End Sub

Private Sub cmdExport_Click()
    ExportToExcel Me.MSHFlexGrid1, "d:/temp.xls"

    Me.SetFocus
    MsgBox "匯出完畢"
End Sub