试环境: 赛扬600+128M+Win2k Prof.(English.)+SQL Server 2000+Excel 2000+VB6(sp4)+ADO2.5 测试表记录数：10322，字段数：9 返回表中所有的纪录(select * from table1),每种方法连续测试5次，在VB中使用MsgBox (DateDiff("s", t1, Now()))计时(秒) 方法1。使用CopyFromRecordset(适用于Access,SQL) 第一次：49第二次：45第三次：43第四次：43第五次：42 方法2：使用QueryTable(适用于Access,SQL) 第一次：10第二次：6第三次：3第四次：4第五次：4 方法2：使用bcp(适用于SQL) 从命令行直接运行时间为701毫秒，从VB中返回时间为0测试代码如下： 方法1： Option Explicit Private Sub Command1_Click()    Dim t1 As Date    t1 = Now()            Dim strConn As String    strConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=mlog;Data Source=SZ09"        Dim cn As ADODB.Connection    Dim rs As ADODB.Recordset            Set cn = CreateObject("ADODB.Connection")    cn.Open strConn    cn.CursorLocation = adUseServer    Set rs = cn.Execute("table1", , adCmdTable)        Dim oExcel As Excel.Application    Dim oBook As Excel.Workbook    Dim oSheet As Object    Set oExcel = CreateObject("Excel.Application")    Set oBook = oExcel.Workbooks.Add    Set oSheet = oBook.Worksheets(1)        oSheet.Range("A1").CopyFromRecordset rs        oBook.SaveAs "d:\1.xls"    oExcel.Quit    Set oSheet = Nothing    Set oBook = Nothing    Set oExcel = Nothing        rs.Close    Set rs = Nothing        cn.Close    Set cn = Nothing        MsgBox (DateDiff("s", t1, Now()))    End Sub 方法 2： Option Explicit Private Sub Command1_Click()    Dim t1 As Date    t1 = Now()        'Create a new workbook in Excel  Dim oExcel As Object  Dim oBook As Object  Dim oSheet As Object  Set oExcel = CreateObject("Excel.Application")  Set oBook = oExcel.Workbooks.Add  Set oSheet = oBook.Worksheets(1)    Dim strConn As String    strConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=mlog;Data Source=SZ09"      'Create the QueryTable    Dim oQryTable As Object  Set oQryTable = oSheet.QueryTables.Add( _  "OLEDB;" & strConn & ";", oSheet.Range("A1"), "Select * from table1")  oQryTable.RefreshStyle = xlInsertEntireRows  oQryTable.Refresh False    'Save the Workbook and Quit Excel  oBook.SaveAs "d:\1.xls"  oExcel.Quit  Set oSheet = Nothing    Set oBook = Nothing    Set oExcel = Nothing        MsgBox (DateDiff("s", t1, Now()))End Sub 方法3： Private Sub Command1_Click()    Dim t1 As Date    t1 = Now()        Dim sCmd As String    sCmd = "bcp mlog..table1 out d:\1.csv -w -t , -r \n -S sz09 -P kenfil"    Dim WSH As Object    Set WSH = CreateObject("WScript.Shell")    WSH.Run sCmd, True        MsgBox (DateDiff("s", t1, Now()))End Sub Note: cvs本身是一个可以被excel使用的文件（你可以直接在excel中打开这个文 件),如果你希望将这个文件转换成xls文件，很简单：   Dim oExcel As Object  Dim oBook As Object  Dim oSheet As Object  Set oExcel = CreateObject("Excel.Application")        Set oBook = oExcel.Workbooks.Open("d:\1.csv")    'Save as Excel workbook and Quit Excel  oBook.SaveAs "d:\1.xls", xlWorkbookNormal  oExcel.Quit      

评论