在VB中如何才能将ACCESS数据库的表导成excel,
解决方案 »
- VB中如何检查变量已经定义但从未使用过?
- 关于Excel宏编程的问题
- SQL SERVER 2000导出到Excel ,请看代码有什么错误?
- 问一个图象处理的问题.
- 我想把我的程序的字符串发送的指定窗口里去,用sendmessage
- 窗体上的右键弹出菜单怎么做?
- VB5的BUG????? 给出答案,马上给分!!
- 如何使隐藏的窗口SHOW后显示在最前面并获得焦点?
- 大哥们都帮帮忙呀,我打包时有个MDC_TYP.EXE 7兆多,它是个啥东西?我的程序才100K,打出来30兆!!!我用不用不把它打进去??
- 如何在程序中获取输入法的状态?又如何控制输入法的状态??
- 100分求助:在图片上有100个Image控件,如何让它们同时放大或者缩小?急…………
- 自己的控件如何在窗口中拖动
Dim cnAccess As New ADODB.Connection, cnExcel As New ADODB.Connection, rsAccess As New ADODB.Recordset, rsExcel As New ADODB.Recordset, i% '打开Access数据库的连接,具体的需要改一下
cnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False"
rsAccess.CursorLocation = adUseClient '获取Access里的Table1的所有记录,准备导出入Excel
rsAccess.Open "select * from table1", cnAccess, adOpenDynamic, adLockReadOnly '连接Excel
cnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties=Excel 8.0"
rsExcel.CursorLocation = adUseClient
'打开Excel的Sheet1表,准备导入数据
rsExcel.Open "select * from [Sheet1$]", cnExcel, adOpenDynamic, adLockPessimistic rsAccess.MoveFirst
While Not rsAccess.EOF
rsExcel.AddNew
For i = 0 To rsAccess.Fields.Count - 1
rsExcel(i) = rsAccess(i) '给Excel的记录集赋值
Next
rsAccess.MoveNext
Wend
rsExcel.UpdateBatch '批量更新记录集 Set rsAccess = Nothing
Set rsExcel = Nothing
cnAccess.Close
Set cnAccess = Nothing
cnExcel.Close
Set cnExcel = NothingEnd Sub
如:Dim Rst As New ADODB.Recordset
Dim Sql as string
sql="select * from table"
然後
Dim objExcel As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Add '新增檔
objExcel.Visible = True
Set objSheet = objBook.Worksheets(1)
objSheet.Cells.CopyFromRecordset Rst
這樣就檔以把Access中某Table的的資料全部導入Excel中