1,我个人编写一段东西在工作中用,主要功能就是导出access数据,新建excel文档方便查询,但是目前发现有如下问题,各位大神请帮忙看看:
Private Sub CB1_Click()
Dim cnn As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim sql As String
cnn.Open "Provider=Microsoft.Ace.OleDB.12.0;data Source=" & App.Path & "\123.mdb"
Dim X As String
X = Combo1.Text
sql = "select * from x where date between # " & Calendar1.Value & "# and #" & Calendar2.Value & "#" ' 利用sql查询选择的日期数据"
rs1.Open sql, cnn, 1, 3
Dim xlApp As New Excel.Application
Set xlApp = CreateObject("excel.application")
xlApp.Visible = ture
Dim xlBook As New Excel.Workbook
Set xlBook = xlApp.Workbooks.Add
Dim xlSheet As New Excel.Worksheet
Set xlSheet = xlBook.Worksheets(1)
If rs1.RecordCount < 1 Then
MsgBox "没有数据导出", vbOKOnly + vbCritical, "错误提示"
Else
Dim r As Integer, c As Integer, cols As Integer
cols = rs1.Fields.Count
Do Until rs1.EOF
r = r + 1
For c = 1 To cols
xlSheet.Cells(r, c) = rs1(c - 1) & ""
Next
rs1.MoveNext
Loop
rs1.Close
MsgBox "导出成功"
Set rs1 = Nothing
Set cnn = Nothing
End If
xlApp.Quit
Set xlApp = Nothing
End Sub
遇到的问题有2个 :
1,combo1 显示需要导出的表名,但是 sql 语句内好像不支持???
2,导出 excel 不能自动打开,必须要开一个excel文件才能出现?
3,如何设置导出的excel文件首行为表的字段名啊?
4,是否可以支持自定义excel存储地址?
非常感谢!