作者：乔山办公网日期：
返回目录：excel表格制作
全部数据都导出来了，是酱紫吗？实现代码见附件（与你原来的数据库文件放在同一目录下）
问一下：“导入”按钮的vba代码 你要在哪里放置导入按钮？excel中？
你想在access中 把数据导出到excel中还是 在excel 导入 access的 数据！
Sub ttt()
Dim conn
Set conn = CreateObject("adodb.connection")
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Test.mdb"
conn.Open
conn.Execute "insert into 表名(A,B,C) select * from [Excel 8.0;DataBase=" & ActiveWorkbook.FullName & "].[表1$]"
conn.Execute "insert into 表名(B,D,E) select * from [Excel 8.0;DataBase=" & ActiveWorkbook.FullName & "].[表2$]"
conn.Execute "insert into 表名(A,D) select * from [Excel 8.0;DataBase=" & ActiveWorkbook.FullName & "].[表3$]"
conn.Close
Set conn = Nothing
End Sub
以上代码写在excel中，
E:\Test.mdb为access数据库完整路径，“表名”为access目标表名
代码执行3次sql命令将3个表导入e69da5e6ba90e79fa5e98193365
用ADO把查询表e799bee5baa6e997aee7ad94e4b893e5b19e361的数据导到Excel表上（记得引用ADO和 Microsoft Excell 11.0 Objec Library)，给一个例子的代码，供参考：
Private Sub Command1_Click()
On Error Resume Next
Dim oExcel As Object
Dim oBook As Object
Dim K As Integer
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add()
With oBook.Worksheets(1)
With oBook.Worksheets(1).PageSetup ’设定页面边距
.LeftMargin = 0.275590551181102
.RightMargin = 0.275590551181102
.TopMargin = 0.275590551181102
.BottomMargin = 0.275590551181102
.Columns("A:A").ColumnWidth = 6 '设定Excel各列的宽度
.Columns("B:B").ColumnWidth = 5.2
.Columns("C:C").ColumnWidth = 10.5
.Columns("D:D").ColumnWidth = 25.7
.Columns("E:E").ColumnWidth = 6
.Columns("F:F").ColumnWidth = 4
.Columns("G:G").ColumnWidth = 3
.Columns("H:H").ColumnWidth = 4
.Columns("I:I").ColumnWidth = 2
.Columns("J:J").ColumnWidth = 7
.Columns("K:K").ColumnWidth = 1
.Columns("L:L").ColumnWidth = 9.9
.Columns("M:M").ColumnWidth = 2.5
.Rows(8).RowHeight = 20.1 '设定Excel各行的高度
Rows(9).RowHeight = 18
.Cells(1, 4) = " 购 销 合 同"
.Cells(1, 4).Font.Size = 20
.Cells(1, 4).Font.Bold = True
.Cells(3, 8) = " 编号:"
.Cells(4, 8) = " 日期:"
.Cells(5, 8) = " 项目名称:"
.Cells(3, 1) = "卖方:"
.Cells(4, 1) = "地址:"
.Cells(6, 1) = " 买卖双方同意订立下列条款进行购销XXXXXXXXXXX"
.Cells(8, 1) = "数 量"
.Cells(8, 3) = " 货 号"
.Cells(8, 4) = " 品 名"
.Cells(8, 5) = " 颜 色"
.Cells(8, 6) = " 含 量"
.Cells(8, 8) = "箱 数"
.Cells(8, 10) = " 单 价"
.Cells(8, 12) = " 金 额"
.Range(.Cells(8, 1), .Cells(8, 13)).Borders(xlEdgeTop).LineStyle = xlContinuous '刬线
.Range(.Cells(8, 1), .Cells(8, 13)).Borders(xlEdgeTop).Weight = xlThick
.Range(.Cells(8, 1), .Cells(8, 13)).Borders(xlEdgeBottom).LineStyle = xlContinuous
.Range(.Cells(8, 1), .Cells(8, 13)).Borders(xlEdgeBottom).Weight = xlThin
Dim CN As New ADODB.Connection
Dim ST As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
CN.Open "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\表名.mdb"
ST = "select * from 查询表"
rs.Open ST, CN, adOpenKeyset, adLockOptimistic
rs.MoveFirst
For K = 1 To rs.RecordCount '把查询表的内容写到Excel表中指定位置
.Cells(K + 8, 1) = rs("QTY")
.Cells(K + 8, 2) = rs("OUN")
.Cells(K + 8, 3) = rs("ITEM")
.Cells(K + 8, 4) = rs("CDES")
.Cells(K + 8, 5) = rs("COL")
.Cells(K + 8, 6) = rs("OPACK")
.Cells(K + 8, 7) = rs("OUN")
.Cells(K + 8, 8) = rs("CTN")
.Cells(K + 8, 10) = Left(rs("PRICE"), 6)
.Cells(K + 8, 10).NumberFormatLocal = "0.00_ "
.Cells(K + 8, 12) = Left(rs("AMT"), 9)
.Cells(K + 8, 12).NumberFormatLocal = "0.00_ "
rs.MoveNext
Next K
.Cells(K + 9, 10) = "合计:"
.Cells(K + 9, 13) = "元"
End With
rs.Close
Set rs = Nothing
Set Cn=nothing
oExcel.Visible = True
Set oBook = Nothing
Set oExcel = Nothing
End Sub