测试环境: 赛扬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
