给一段VB程序参考,速度很快: Dim ExcelApp As Excel.Application
Dim book As Excel.Workbook
Dim sheet As Excel.Worksheet ' Object On Error GoTo ErrGetObj:
Set ExcelApp = GetObject(, "Excel.Application")
Set book = ExcelApp.ActiveWorkbook
Set sheet = book.Sheets.Add With sheet.QueryTables.Add(connection:= "ODBC;DRIVER=SQL Server;SERVER=serverName;UID=sa;PWD=;DATABASE=Test"_
, Destination:=sheet.Range("A1"))
.sql = array("select * from Test")
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With exit sub
ErrGetObj:
Err.Clear
On Error Resume Next
Set ExcelApp = New Excel.Application If Err.Number <> 0 Then
Err.Clear
Set ExcelApp = CreateObject("Excel.Application")
If Err.Number <> 0 Then
GoTo Err0
End If
End If NewWorkBook = True
Resume Next
Dim book As Excel.Workbook
Dim sheet As Excel.Worksheet ' Object On Error GoTo ErrGetObj:
Set ExcelApp = GetObject(, "Excel.Application")
Set book = ExcelApp.ActiveWorkbook
Set sheet = book.Sheets.Add With sheet.QueryTables.Add(connection:= "ODBC;DRIVER=SQL Server;SERVER=serverName;UID=sa;PWD=;DATABASE=Test"_
, Destination:=sheet.Range("A1"))
.sql = array("select * from Test")
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With exit sub
ErrGetObj:
Err.Clear
On Error Resume Next
Set ExcelApp = New Excel.Application If Err.Number <> 0 Then
Err.Clear
Set ExcelApp = CreateObject("Excel.Application")
If Err.Number <> 0 Then
GoTo Err0
End If
End If NewWorkBook = True
Resume Next
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货