一條一條寫到ececel的單元格中,当然速度慢用CopyFromRecordset 方法Public Sub MSHFlexGrid2Excel(ctlFlexGrid As MSHFlexGrid, mode As Integer) On Error Resume Next
Dim xlApp As New Excel.Application Dim xlWb As New Excel.Workbook Dim xlWs As New Excel.Worksheet
Dim nRow As Integer Dim nCol As Integer
Dim nRows As Integer Dim nCols As Integer
nRows = ctlFlexGrid.Rows nCols = ctlFlexGrid.Cols
'open Excel If mode = 1 Then 'export to excel xlApp.Visible = True Else 'print to printer via excel xlApp.Visible = False End If
Set xlWb = xlApp.Workbooks.Add Set xlWs = xlApp.ActiveSheet
'assign MSHFLexGrid into a recordset 'don't forget to add MsADO in your Project/Reference Dim rs As ADODB.Recordset Set rs = ctlFlexGrid.DataSource
'fill the whole XL body at once, starting from cell A1 xlWs.Cells(1, 1).CopyFromRecordset rs 'autofit the XL column For nCol = 1 To nCols xlWs.Columns(nCol).AutoFit Next 'the actual printing If mode = 2 Then 'to printer xlWs.PrintOut xlApp.DisplayAlerts = False xlWb.Close (False) xlApp.Quit End If 'cleaning up Set xlApp = Nothing Set rs = NothingEnd Sub
這個是原來的代碼!!你能幫我分析一下嘛??謝謝!! Private Sub Command2_Click(Index As Integer) Dim j As Integer Dim strFName As String Dim objXLS As Object Dim STRDAT(4999) As Alrm Dim MODAT(4999) As Alrm, MO Dim i As Integer Dim DataCount As Integer 'データ数格納 Dim iRet As Integer Dim cuWork As Currency Dim TR_Save As Integer
On Error GoTo DiskErr
Select Case Index
Case 0 '待ちメッセージの表示 ' If Existfloppy = False Then ' Form0005.Label1.Caption = ERR_ALARMDATA_MO_001 ' Form0005.Label1.ForeColor = KRED ' Form0005.Show 1 ' Exit Sub ' End If
strFName = LLMODRIVER_SAVETREND & "履歴" & Format(Date, "yyyy") & "_" & Format(Date, "mm") & "_" & Format(Date, "dd") & ".xls"' Set xlApp = CreateObject("Excel.Sheet.8") 'sheet名設定 ' xlApp.Worksheets(1).name = "全履歴データ" '/** Excelをセット **/ Set xlApp = Nothing Set xlApp = New Excel.Application If FileExists(FILE_MO) = True Then Else '履歴保存が存在しません Form0005.Label1.Caption = FILE_MO_NO_EXIST Form0005.Label1.ForeColor = KRED Form0005.Show 1 Exit Sub
On Error Resume Next
Dim xlApp As New Excel.Application
Dim xlWb As New Excel.Workbook
Dim xlWs As New Excel.Worksheet
Dim nRow As Integer
Dim nCol As Integer
Dim nRows As Integer
Dim nCols As Integer
nRows = ctlFlexGrid.Rows
nCols = ctlFlexGrid.Cols
'open Excel
If mode = 1 Then
'export to excel
xlApp.Visible = True
Else
'print to printer via excel
xlApp.Visible = False
End If
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlApp.ActiveSheet
'assign MSHFLexGrid into a recordset
'don't forget to add MsADO in your Project/Reference
Dim rs As ADODB.Recordset
Set rs = ctlFlexGrid.DataSource
'fill the whole XL body at once, starting from cell A1
xlWs.Cells(1, 1).CopyFromRecordset rs 'autofit the XL column
For nCol = 1 To nCols
xlWs.Columns(nCol).AutoFit
Next 'the actual printing
If mode = 2 Then 'to printer
xlWs.PrintOut xlApp.DisplayAlerts = False
xlWb.Close (False)
xlApp.Quit
End If 'cleaning up
Set xlApp = Nothing
Set rs = NothingEnd Sub
http://www.microsoft.com/china/community/Column/32.mspx
我的文件是二進制的
从.csv文件导成excel就十分快了,用ado连接到csv,然后用CopyFromRecordset 方法
Private Sub Command2_Click(Index As Integer)
Dim j As Integer
Dim strFName As String
Dim objXLS As Object
Dim STRDAT(4999) As Alrm
Dim MODAT(4999) As Alrm, MO
Dim i As Integer
Dim DataCount As Integer 'データ数格納
Dim iRet As Integer
Dim cuWork As Currency
Dim TR_Save As Integer
On Error GoTo DiskErr
Select Case Index
Case 0
'待ちメッセージの表示
' If Existfloppy = False Then
' Form0005.Label1.Caption = ERR_ALARMDATA_MO_001
' Form0005.Label1.ForeColor = KRED
' Form0005.Show 1
' Exit Sub
' End If
strFName = LLMODRIVER_SAVETREND & "履歴" & Format(Date, "yyyy") & "_" & Format(Date, "mm") & "_" & Format(Date, "dd") & ".xls"' Set xlApp = CreateObject("Excel.Sheet.8") 'sheet名設定
' xlApp.Worksheets(1).name = "全履歴データ" '/** Excelをセット **/
Set xlApp = Nothing
Set xlApp = New Excel.Application
If FileExists(FILE_MO) = True Then
Else
'履歴保存が存在しません
Form0005.Label1.Caption = FILE_MO_NO_EXIST
Form0005.Label1.ForeColor = KRED
Form0005.Show 1
Exit Sub
End If
Call DispWaitMsg(True)
'/** 印字フォーマットを選択しEXCELで起動 **/
xlApp.Workbooks.Open FILE_MO
'データ設定
For i = 0 To DCount - 1
DoEvents
If gCancel = True Then
Exit For
End If
xlApp.Cells(i + 5, 1).Value = "'" & RTrim(DSPDAT(4999 - DCount + 1 + i).Data_Date)
xlApp.Cells(i + 5, 2).Value = "'" & RTrim(DSPDAT(4999 - DCount + 1 + i).Data_Time)
Select Case DSPDAT(4999 - DCount + 1 + i).Data_Gamen
Case 1
xlApp.Cells(i + 5, 3).Value = "機器状態変化"
Case 2
xlApp.Cells(i + 5, 3).Value = "故障警報"
Case 3
xlApp.Cells(i + 5, 3).Value = "制御操作"
Case 4
xlApp.Cells(i + 5, 3).Value = "上下限YITUO"
Case 5
xlApp.Cells(i + 5, 3).Value = "設定値変更"
Case 6
xlApp.Cells(i + 5, 3).Value = "システム状態変化"
End Select
xlApp.Cells(i + 5, 4).Value = "'" & RTrim(DSPDAT(4999 - DCount + 1 + i).Data_Basyo)
xlApp.Cells(i + 5, 5).Value = "'" & RTrim(DSPDAT(4999 - DCount + 1 + i).Data_Mishou)
xlApp.Cells(i + 5, 6).Value = "'" & RTrim(DSPDAT(4999 - DCount + 1 + i).Data_HHLL)
xlApp.Cells(i + 5, 7).Value = "'" & RTrim(DSPDAT(4999 - DCount + 1 + i).Data_Teigi)
xlApp.Cells(i + 5, 8).Value = "'" & RTrim(DSPDAT(4999 - DCount + 1 + i).Data_Dousa)
Next i If gCancel = True Then
GetTaskList
Else
' EXCEL文档保存
xlApp.Workbooks(1).Saved = True
xlApp.Workbooks(1).SaveAs strFName
xlApp.Application.Quit
Set xlApp = Nothing
End If
Case 1
End Select
'終了
Unload Me
Exit Sub
DiskErr:
If Err.Number = 1004 Then
Form0005.Label1.Caption = ERR_ALARMDATA_MO_002
Form0005.Label1.ForeColor = KRED
Form0005.Label2.Caption = ERR_ALARMDATA_MO_003
Form0005.Label2.ForeColor = KRED
Form0005.Show 1
Unload Form0007
Else
Form0005.Label1.Caption = ERR_ALARMDATA_MO_004
Form0005.Label1.ForeColor = KRED
Form0005.Show 1
Unload Form0007
End IfEnd Sub