VB中怎样通过ADO存取Excel数据

如果用VB直接调用Excel的话速度非常慢,从网上查到通过ADO调用Excel比较快,请教大家怎么用,最好给一个实例,先谢过了... 如果用VB直接调用Excel的话速度非常慢,从网上查到通过ADO调用Excel比较快,请教大家怎么用,最好给一个实例,先谢过了 展开
 我来答
yjtkgg321
推荐于2017-09-30 · TA获得超过3.4万个赞
知道大有可为答主
回答量:1.2万
采纳率:87%
帮助的人:2071万
展开全部
先来个临时存储,再删除,再添加修改好的。具体代码如下:
'//sql语句导出到excel
'//参数:strSQL-传入的sql语句,strTitle-对应sql语句中每列的标题(例如:"编号|名称|规格")
Public Function SQLToExcel(ByVal strSQL As String, ByVal strTitle As String)
Dim rsTemp As ADODB.Recordset
Dim strExcelPath As String '//导出的excel文件路径
Dim arrTemp() As Variant
Dim arrTitle As Variant
Dim lngRows As Long
Dim lngCols As Long
Dim objExcelApp As Object
Dim objExcelWorkBook As Object
Dim objExcelWorkSheet As Object
Dim i As Long
Dim j As Long
On Error GoTo errHandle
If CheckExcel = False Then objInterCont.Tips "请确定已正确安装了Excel软件!": Exit Function
If Trim(strSQL) = "" Then Exit Function
Set rsTemp = mDB.Execute(strSQL)
If rsTemp.BOF And rsTemp.EOF Then
Set rsTemp = Nothing
objInterCont.Tips "没有要导出的数据,请重新选择查询条件!"
Exit Function
End If
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = False
With frmReport.dlgExport '//打开保存对话框
.FileName = ""
.DialogTitle = "请输入Excel文件名称"
.Filter = "Excel Files(*.xls)|*.xls" '//文件类型过虑为excel
.ShowSave
If Trim(.FileName) = "" Then Exit Function
strExcelPath = Trim(.FileName)
If Dir(Trim(.FileName)) <> "" Then '如果存在文件则提示
If MsgBox("文件已存在,是否替换原文件?", vbYesNo + vbQuestion, "提示") = vbYes Then
Kill Trim(.FileName)
Else
objExcelApp.Quit
Set objExcelApp = Nothing
Set rsTemp = Nothing
Exit Function
End If
End If
End With
Screen.MousePointer = 11
DoEvents
Err.Clear
lngRows = rsTemp.RecordCount
lngCols = rsTemp.Fields.Count
ReDim arrTemp(lngRows - 1, lngCols - 1)
i = 0
rsTemp.MoveFirst
Do While Not rsTemp.EOF
For j = 0 To lngCols - 1
arrTemp(i, j) = rsTemp.Fields(j).Value '//保存数据到数组
Next
rsTemp.MoveNext
i = i + 1
Loop
arrTitle = Split(strTitle, "|") '//保存标题到数组
Set objExcelWorkBook = objExcelApp.Workbooks.Add
Set objExcelWorkSheet = objExcelWorkBook.Worksheets(1) '写入第一个工作簿
With objExcelWorkSheet
.Range(.cells(1, 1), .cells(UBound(arrTemp, 1) + 2, UBound(arrTemp, 2) + 1)).NumberFormatLocal = "@"
.Range(.cells(1, 1), .cells(1, UBound(arrTitle, 1) + 1)).Font.Bold = True '//标题加粗
.Range(.cells(1, 1), .cells(1, UBound(arrTitle, 1) + 1)) = arrTitle '写入excel标题
.Range(.cells(2, 1), .cells(UBound(arrTemp, 1) + 2, UBound(arrTemp, 2) + 1)) = arrTemp '写入excel列内容
.cells.EntireColumn.AutoFit '//自动改变列大小
End With
objExcelWorkBook.SaveAs FileName:= _
strExcelPath, FileFormat:= _
1, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
objExcelApp.Quit

Set objExcelApp = Nothing
Set objExcelWorkBook = Nothing
Set objExcelWorkSheet = Nothing
Set rsTemp = Nothing
Erase arrTemp
Erase arrTitle
SQLToExcel = True
Screen.MousePointer = 0
objInterCont.Tips "导出数据成功!"
Exit Function
errHandle:
SQLToExcel = False
Set objExcelApp = Nothing
Set objExcelWorkBook = Nothing
Set objExcelWorkSheet = Nothing
Set rsTemp = Nothing
Erase arrTemp
Erase arrTitle
Screen.MousePointer = 0
If Err.Number = 75 Then
objInterCont.Tips "所覆盖的Excel文件属性只读,导出失败!"
Exit Function
End If
If Err.Number = 70 Then
objInterCont.Tips "所覆盖的Excel文件已打开,导出失败!"
Exit Function
End If
mobjErrLog.Record Err.Number, Err.Description, "DataOperator.cls", "SQLToExcel"
End Function
已赞过 已踩过<
你对这个回答的评价是?
评论 收起
大圣美猴王2ROM4
2010-03-26 · TA获得超过1171个赞
知道大有可为答主
回答量:2386
采纳率:100%
帮助的人:2072万
展开全部
本回答被提问者采纳
已赞过 已踩过<
你对这个回答的评价是?
评论 收起
推荐律师服务: 若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询

为你推荐:

下载百度知道APP,抢鲜体验
使用百度知道APP,立即抢鲜体验。你的手机镜头里或许有别人想知道的答案。
扫描二维码下载
×

类别

我们会通过消息、邮箱等方式尽快将举报结果通知您。

说明

0/200

提交
取消

辅 助

模 式