======================================================== 'Excel对象的使用(将Access中的数据导入到一个新建的Excel文件中) '======================================================== Private Sub Form_Load() Dim I, J As Long Dim conn As ADODB.Connection Dim rst As ADODB.Recordset
Set conn = New ADODB.Connection Set rst = New ADODB.Recordset conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;Persist Security Info=False" conn.Open
Dim MyApp As Excel.Application Dim MyBook As Excel.Workbook Dim MySheet As Excel.Worksheet Set MyApp = CreateObject("Excel.Application") MyApp.Visible = False Set MyBook = MyApp.Workbooks.Add() Set MySheet = MyBook.Worksheets(1)
J = 1 Do Until rst.EOF For I = 1 To rst.Fields.Count MySheet.Cells(J, I) = rst.Fields(I - 1) Next rst.MoveNext J = J + 1 Loop
========================================================
'Excel对象的使用(将Access中的数据导入到一个新建的Excel文件中)
'========================================================
Private Sub Form_Load()
Dim I, J As Long
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set conn = New ADODB.Connection
Set rst = New ADODB.Recordset
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;Persist Security Info=False"
conn.Open
rst.CursorLocation = adUseClient
rst.Open "select * from tableabc", conn, adOpenDynamic, adLockOptimistic
Dim MyApp As Excel.Application
Dim MyBook As Excel.Workbook
Dim MySheet As Excel.Worksheet
Set MyApp = CreateObject("Excel.Application")
MyApp.Visible = False
Set MyBook = MyApp.Workbooks.Add()
Set MySheet = MyBook.Worksheets(1)
J = 1
Do Until rst.EOF
For I = 1 To rst.Fields.Count
MySheet.Cells(J, I) = rst.Fields(I - 1)
Next
rst.MoveNext
J = J + 1
Loop
MyBook.SaveAs "C:\11.xls"
MyApp.Quit
Set MyApp = Nothing
rst.Close
Set rst = Nothing
Set conn = Nothing
End Sub
http://dev.csdn.net/develop/article/14/14952.shtm
谢谢
我怎么运行你的程序后,机器马上死机,重起后点击桌面项以及其他运行项,不好使啊,在此前机器没有问题啊
这是宏代码,把它考到VB里加上excel对象就可以了
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=D:\db1_2.mdb;Mode=Share Deny Write;Extended Properties=""""" _
, _
";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Databa" _
, _
"se Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Je" _
, _
"t OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Com" _
, "pact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range _
("A1"))
.CommandType = xlCmdTable
.CommandText = Array("10111a损益表:主营业务利润")
.Name = "db1_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "D:\db1_2.mdb"
.Refresh BackgroundQuery:=False
End With