将sql数据倒入excel表格

极速倒入sql记录到excel表格,19个子段5万条记录只需30秒http://expert.csdn.net/Expert/topic/1035/1035148.xml?temp=.9380762

在网上看到一段程序,没有使用vba编程将sql数据倒入excel表格,速度极快,贴出于大家共赏.

其主要思想是：将EXCEL作为一个数据库使用，它的名字就是数据库的名字，工作表就是一张数据库中的表。
建立一个工程,引用dao,添加command1,粘贴一下代码
'声明API函数
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
'定义变量

Private Sub Command1_Click()
'如果EXCEL文件已经打开,需要先关闭它.

Dim lpClassName As String
Dim lpCaption As String
Dim Handle As Long
lpClassName = "XLMAIN"
lpCaption = "Microsoft Excel - MyExcel.xls"
Handle = FindWindow(lpClassName$, lpCaption$)
If Handle <> 0 Then
MsgBox "请先关闭EXCEL文件!", vbOKOnly + vbInformation, "不能对已经打开的文件进行写操作!"
Exit Sub
End If
'检查EXCEL文件是否存在,如果存在则删除
 If Dir(App.Path & "/MyExcel.xls") <> "" Then Kill App.Path & "/MyExcel.xls"
'进行数据转换
Dim dbs As Database
'打开数据库
Set dbs = OpenDatabase("", False, False, "ODBC;DSN=idms;DATABASE=idms;UID=sa;PWD=;") '连接字符串,请根据自己的情况修改
 '把数据导入EXCEL
dbs.Execute "SELECT " & "PersonId as 住户编号, Name as 姓名, Sex as 性别," & _
             " Birthday as 出生日期, Nation as 民族,NativePlace as 籍贯," & _
             " Politics as 政治面貌, IdCard as 身份证号码,Study as 学历," & _
             " WorkPlace as 工作单位, WorkPhone as 单位电话, HomePhone as 家庭电话," & _
             " MobilePhone as 手机或BP机, CarCard as 车牌号码, StartDate as 入住日期," & _
             " Patch as 片区, DepartmentId as 公寓号, UnitNo as 单元号," & _
             " RoomId as 房间号, ContractId as 购房合同号 " & "  INTO [Excel 8.0;DATABASE=" & App.Path & "/MyExcel.xls].[WorkSheet1] FROM " & "tbl_Tenement"
'关闭数据库对象
dbs.Close
'释放数据库对象
Set dbs = Nothing
'调用EXCEL打开产生的EXCEL表格
Shell "d:/Program Files/Microsoft Office/Office10/EXCEL.EXE " & App.Path & "/MyExcel.xls", vbMaximizedFocus

End Sub

19个字段,5万条记录,只需30-60秒,而采用直接用vba写入cell的方法两万条记录就需 83分钟,提速何止百倍,但这个方法有些局限,小弟水平有限没能解决.希望大家讨论讨论,予以完善.

1、这段代码可能会随即出现“系统不支持选择的排序方式”错误，在增加resume next后解决，请问这是什么问题引发的，能不能排除掉。
2、问一下一张excel表格可以存储多少条记录，我在测试十万条记录的存入时出现“电子表格已满的错误”

欢迎大家讨论，让所有为导入数据到excel的速度困扰的朋友看到这段代码