怎么提高向Excel中写数据的速度??

[I_168_888]

从一个MsFlexGrid控件中读入数据到Excel,代码如下,但是速度太慢,600行24列要近10分钟,怎么才能提高速度?

Private Sub Command3_Click()
On Error GoTo errorCancel

Dim filename As String
CommonDialog1.CancelError = True
CommonDialog1.Filter = "Excel file (*.xls)|*.xls|All file (*.*)|*.*"
CommonDialog1.Flags = CommonDialog1.Flags + cdlOFNOverwritePrompt
CommonDialog1.ShowSave
filename = CommonDialog1.filename
If filename = "" Then
Exit Sub
End If

On Error GoTo error

Dim excelObj As New Excel.Application
Dim wkBook As New Excel.Workbook
Dim wkSheet As New Excel.Worksheet

excelObj.Visible = False
Set wkBook = excelObj.Workbooks.Add()
Set wkSheet = wkBook.Sheets(1)

For i = 0 To MSFlexGrid1.Rows - 1
For j = 0 To MSFlexGrid1.Cols - 1
MSFlexGrid1.Row = i
MSFlexGrid1.Col = j
wkSheet.Range(["A1"]).Offset([i], [j]).NumberFormatLocal = "@"
wkSheet.Range(["A1"]).Offset([i], [j]).Value = MSFlexGrid1.Text
Next j
Next i

For i = 1 To MSFlexGrid1.Cols
wkSheet.Columns(i).EntireColumn.AutoFit
Next i

excelObj.DisplayAlerts = False
wkBook.SaveAs filename
excelObj.DisplayAlerts = True
wkBook.Close
excelObj.Quit
Set excelObj = Nothing

MsgBox "Data save success!", vbOKOnly, "Tip"

Exit Sub
errorCancel:
Exit Sub
error:
ShowError
End Sub

[fongzl]

wkSheet.cells.NumberFormatLocal = "@"

For i = 0 To MSFlexGrid1.Rows - 1
For j = 0 To MSFlexGrid1.Cols - 1
MSFlexGrid1.Row = i
MSFlexGrid1.Col = j

wkSheet.cells(i,j).Value = MSFlexGrid1.Text
Next j
Next i

你试试看，这样会不会快点？

[zephyr_zhao]

application.screenupdate=false

[zephyr_zhao]

那个属性大概是这么拼的把，反正是暂时不刷新屏幕
with wksheet
For i = 0 To MSFlexGrid1.Rows - 1
For j = 0 To MSFlexGrid1.Cols - 1
MSFlexGrid1.Row = i
MSFlexGrid1.Col = j
.cells(i,j).Value = MSFlexGrid1.Text
Next j
Next i
end with
wksheet.range("xxx").NumberFormatLocal = "@" '整个区域一切设

[zephyr_zhao]

还不快的话，只能想办法把msflexgrid的一片区域选中，Copy，然后到Excel，Paste