2 u012914166 u012914166 于 2013.11.27 19:47 提问

VB 到出 Excel 的问题

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存储地址?

非常感谢!

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!