Excel
是一个非常优秀的报表制作软件,用VBA可以控制其生成优秀的报表,本文通过添加查询语句的方法,即用Excel中的获取外部数据的功能将数据很快地从一个查询语句中捕获到EXCEL中,比起往每个CELL里写数据的方法提高许多倍。
一、将ACCESS表中的数据导出至EXCEL:
将下文加入到一个模块中,屏幕中调用如下ExporToExcel("select * from
table")则实现将其导出到EXCEL中
添加一按钮,双击后输入:
Private Sub Command1_Click()
'导出至excel
ExporToExcel
("select * from People")
End Sub
以下为函数主体内容(由孟子E章提供源代码,稍有改动,下文中注明改动地方及原因)
Public Function ExporToExcel(strOpen As String)
'*********************************************************
'* 名称:ExporToExcel
'* 功能:导出数据到EXCEL
'* 用法:ExporToExcel(sql查询字符串)
'*********************************************************
Dim Rs_Data As New ADODB.Recordset
Dim Irowcount As Integer
Dim Icolcount As Integer
Dim xlApp As
New Excel.Application
Dim xlBook
As Excel.Workbook
Dim xlSheet
As Excel.Worksheet
Dim xlQuery
As Excel.QueryTable
'这里增加ACCESS表连接内容,建立CONN连接对象,by boyd
Dim data As
String
Dim strpath
As String
strpath =
App.Path
If
Right(strpath, 1) <> "/" Then
strpath =
strpath & "/"
End If
strpath =
strpath & "/MySmsBook.mdb"
Set conn =
New ADODB.Connection
conn.Open
"driver={Microsoft Access Driver (*.mdb)};pwd=;dbq=" &
strpath
With
Rs_Data
If .state = adStateOpen Then
.Close
End If
.ActiveConnection = conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Source = strOpen
.Open
End
With
With
Rs_Data
If .RecordCount < 1 Then
MsgBox ("没有记录!")
Exit Function
End If
'记录总数
Irowcount = .RecordCount
'字段总数
Icolcount = .Fields.Count
End
With
Set xlApp =
CreateObject("Excel.Application")
Set xlBook =
Nothing
Set xlSheet
= Nothing
Set xlBook =
xlApp.Workbooks().Add
Set xlSheet
= xlBook.Worksheets("sheet1")
'xlApp.Visible = True
'这里好象是设置excel表格程序为可视,去掉,by boyd
'添加查询语句,导入EXCEL数据
Set xlQuery
= xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range("a1"))
With
xlQuery
.FieldNames = True
.RowNumbers = False
|