access数据库中数据从access导出至excel

DoCmd.OutputTo acQuery, "查询1", "Microsoft Excel 97-2003(*.xls)", "", False, "", 0

以上是将ACCESS中的查询导出EXCEL，如果是表的话，就换成actable

不用写什么代码，直接自定义快捷按钮，将“文件”菜单项下的“导出”图标放在快捷键栏就 ok 了。以后一按这个键就可以进行导出操作。

把access库中的数据导出到excel中(VB)2007-12-12 16:02在vb中做了个“导出按钮”，把access库中的数据导出到excel中……
导出成功后打开C:\Excel\gift.xls文件，

“导出“代码如下

工作表存盘后加入以下二行就行了

xlBook.Close' 关闭工作表。
xlExcel.Quit'用 Quit 方法关闭 Microsoft Excel '释放对象

【zzyong00】:
Private Sub CmdOutput_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String

cn.Open "provider=microsoft.jet.oledb.4.0;data source=d:\gift\gift.mdb"

sql = "select * from [ly_gift]"

rs.Source = sql
Set rs.ActiveConnection = cn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Open sql, cn

If rs.RecordCount < 1 Then
MsgBox "没有数据导出", vbOKOnly + vbCritical, "错误提示"
Else

If Dir("C:\Excel", vbDirectory) = "" Then
MkDir ("C:\Excel")
End If

If Dir("C:\Excel\gift.xls") <> "" Then
Kill "C:\Excel\gift.xls"
End If
End If

Dim i As Integer
Dim j As Integer
Dim xlExcel As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlBook = xlExcel.Workbooks.Add
Set xlSheet = xlExcel.Worksheets.Add

xlSheet.Cells.Columns(5).ColumnWidth = 20
xlSheet.Cells(1, 1) = "联名卡号"
xlSheet.Cells(1, 2) = "领用"
xlSheet.Cells(1, 3) = "日期"
xlSheet.Cells(1, 4) = "时间"
xlSheet.Cells(1, 5) = "操作员"

For i = 2 To rs.RecordCount + 1
For j = 1 To rs.Fields.Count
xlSheet.Cells(i, j) = rs.Fields.Item(j - 1).Value
Next j
rs.MoveNext
Next i
xlBook.SaveAs FileFormat:=xlExcel9795
xlBook.SaveAs FileName:="C:\Excel\gift.xls"

rs.Close
cn.Close

Set xlSheet = nothing
set xlBook=nothing
xlExcel.quit
set xlExcel=nothing

End Sub

还有
Dim xlExcel As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
Workbook和Worksheet不要用New，而直接用对象集的add方法