SQL to Excel 三種方法

excel 方法1。使用CopyFromRecordset(適用於Access,SQL)

第一次：49
第二次：45
第三次：43
第四次：43
第五次：42

方法2：使用QueryTable(適用於Access,SQL)

第一次：10
第二次：6
第三次：3
第四次：4
第五次：4

方法3：使用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