Access 在VBA中实现数据导出到Excel
来源:互联网 发布:如何注册手机淘宝店铺 编辑:程序博客网 时间:2024/05/01 21:31
http://hi.baidu.com/ohmyidea/item/e2bf6819735b6a34b93180cc
1.添加引用Microsoft Excel 11.0 Object Library。(这里用的是Microsoft Excel 2003)
2.定义获取数据集通用函数。
Public Function GetRS(ByVal strQuery As String) As ADODB.Recordset
Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection
On Error GoTo GetRS_Error
Set conn = CurrentProject.Connection
rs.Open Trim$(strQuery), conn, adOpenKeyset, adLockOptimistic
Set GetRS = rs
GetRS_Exit:
Set rs = Nothing
Set conn = Nothing
Exit Function
GetRS_Error:
MsgBox Err.Description
Resume GetRS_Exit
End Function
Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection
On Error GoTo GetRS_Error
Set conn = CurrentProject.Connection
rs.Open Trim$(strQuery), conn, adOpenKeyset, adLockOptimistic
Set GetRS = rs
GetRS_Exit:
Set rs = Nothing
Set conn = Nothing
Exit Function
GetRS_Error:
MsgBox Err.Description
Resume GetRS_Exit
End Function
3.导出Excel代码。
Private Sub btnOutToExcel_Click()
Dim row As Integer
Dim col As Integer
Dim rs As New ADODB.Recordset
Dim ExcelApp As Excel.Application
Dim ExcelWst As Worksheet
Set rs = GetRS("SELECT * FROM PInfo") '获取数据集
Set ExcelApp = New Excel.Application
Set ExcelWst = ExcelApp.Workbooks.Add.Worksheets(1)
'导出字段名称
For col = 0 To rs.Fields.Count - 1
ExcelWst.Cells(1, col + 1) = rs.Fields(col).Name
Next col
'导出数据
row = 2
Do While Not rs.EOF
For col = 0 To rs.Fields.Count - 1
ExcelWst.Cells(row, col + 1) = rs.Fields(col)
Next col
row = row + 1
rs.MoveNext
Loop
rs.Close
ExcelWst.Columns.AutoFit '设置列宽
ExcelApp.Visible = True
End Sub
Dim row As Integer
Dim col As Integer
Dim rs As New ADODB.Recordset
Dim ExcelApp As Excel.Application
Dim ExcelWst As Worksheet
Set rs = GetRS("SELECT * FROM PInfo") '获取数据集
Set ExcelApp = New Excel.Application
Set ExcelWst = ExcelApp.Workbooks.Add.Worksheets(1)
'导出字段名称
For col = 0 To rs.Fields.Count - 1
ExcelWst.Cells(1, col + 1) = rs.Fields(col).Name
Next col
'导出数据
row = 2
Do While Not rs.EOF
For col = 0 To rs.Fields.Count - 1
ExcelWst.Cells(row, col + 1) = rs.Fields(col)
Next col
row = row + 1
rs.MoveNext
Loop
rs.Close
ExcelWst.Columns.AutoFit '设置列宽
ExcelApp.Visible = True
End Sub
0 0
- Access 在VBA中实现数据导出到Excel
- C#执行access中VBA,用VBA导出access表中数据到Excel中
- 在VBA中实现两个ACCESS数据库之间的数据导出与导入
- 在VBA中将不规则数据导出到Excel
- 在VB中怎样将ACCESS的数据导出导入到EXCEL中
- 利用vba 从excel到access中导入与导出表
- 在C#中导出数据到Excel
- Excel VBA数据导出
- Excel vba从excel中导出表格数据和图表到新word文档
- [Excel VBA] 在VBA中如何将SQL得到的数据直接赋值到数组?
- VB里面实现导出ACCESS到EXCEL
- Access导出到Excel
- Excel导出到Access
- 导出数据到excel中
- 导出数据到Excel中
- 导出数据到Excel中
- 如何使用VBA实现将多个Excel文件中的数据复制到某个Excel文件中
- 在java中使用FreeMark导出数据到excel表格
- 开通我的博客了
- ubuntu下 vi编辑器使用手册
- Matlab学术图表色彩选择
- 386计算机毕业设计
- 关于 Windows 中的时区信息
- Access 在VBA中实现数据导出到Excel
- C++构造函数初始化列表——笔记
- 百钱买百鸡问题
- 是男人就下100层【第一层】——高仿微信界面(2)
- Ubuntu 下Mysql常用命令
- 分数拆分
- C语言经典算法100例-009-输出国际象棋棋盘
- HTML5 Video
- 读懂diff