Office中国论坛/Access中国论坛

标题: 请教一个从access向excle导入查询结果的问题 [打印本页]

作者: hawkyang 时间: 2006-7-10 05:13
标题: 请教一个从access向excle导入查询结果的问题
我在程序设计中遇到了一个问题，请高手指导一下我应该如何做，谢谢

我想在窗体上的一个按钮,点击触发一个查询,并将查询结果输出到excle中，但是我不知道如何将查询结果输出到excle次表中

  Dim comsql As String
  Dim stfilter1 As String
  Dim stlist1
  Dim stfilterName1 As String

  stfilter1 = Me.combo99
  stlist1 = Me.Text87
  stfilterName1 = stfilter1 & "= '" & stlist1 & "'"
  MsgBox stfilterName1

  If stfilter1 = "全体员工" Then
stfilterName1 = "form 职员基本信息表"
MsgBox "全体员工" & stfilterName1
  Else
stfilterName1 = " from 职员基本信息表 where " & stfilterName1
MsgBox stfilterName1
  End If

  If Me.Check23 = True Then comsql = comsql & "出生年月,"
  If Me.Check25 = True Then comsql = comsql & "身份证号码,"
  If Me.Check27 = True Then comsql = comsql & "联系电话,"
  If Me.Check29 = True Then comsql = comsql & "移动电话,"
  If Me.Check31 = True Then comsql = comsql & "电子邮件地址,"
  If Me.Check37 = True Then comsql = comsql & "婚姻状况,"
  If Me.Check39 = True Then comsql = comsql & "居住地,"
  If Me.Check41 = True Then comsql = comsql & "户口性质,"
  If Me.Check43 = True Then comsql = comsql & "学历,"
  If Me.Check45 = True Then comsql = comsql & "毕业学校,"
  If Me.Check47 = True Then comsql = comsql & "紧急联系人,"
  If Me.Check49 = True Then comsql = comsql & "紧急联系电话,"
  If Me.Check51 = True Then comsql = comsql & "部门,"
  If Me.Check53 = True Then comsql = comsql & "职务,"
  If Me.Check55 = True Then comsql = comsql & "入职时间,"
  If Me.Check57 = True Then comsql = comsql & "薪金,"
  If Me.Check59 = True Then comsql = comsql & "医疗保险基数,"
  If Me.Check53 = True Then comsql = comsql & "社会保险基数,"
  MsgBox comsql
  comsql = Left(comsql, Len(comsql) - 1)
  MsgBox comsql
  comsql = "select " & comsql & stfilterName1
  MsgBox comsql

以上部分是组成sql查询语句，下面我就不知道如何使用OutputTo宏，将这个查询结果导出到excle

DoCmd.OutputTo acOutputQuery, comsql,acFormatXLS, True

谢谢，指教，在线等答案

[此贴子已经被作者于2006-7-9 22:47:20编辑过]

作者: hawkyang 时间: 2006-7-10 05:57
谢谢了，谁帮助解答一下

作者: hawkyang 时间: 2006-7-10 07:16
我已经找到相关的方法了,标红处为借鉴刘小军(Alex)的代码,感谢刘小军的无私奉献.黄颜色的是一个已建立好的名为“查询结果”的查询，

Private Sub Command83_Click()
Dim qdf As DAO.QueryDef
  Dim comsql As String
  Dim stfilter1 As String
  Dim stlist1
  Dim stfilterName1 As String

  stfilter1 = Me.combo99
  stlist1 = Me.Text87
  stfilterName1 = stfilter1 & "= '" & stlist1 & "'"
  MsgBox stfilterName1

  If stfilter1 = "全体员工" Then
stfilterName1 = "form 职员基本信息表"
MsgBox "全体员工" & stfilterName1
  Else
stfilterName1 = " from 职员基本信息表 where " & stfilterName1
MsgBox stfilterName1
  End If

  If Me.Check23 = True Then comsql = comsql & "出生年月,"
  If Me.Check25 = True Then comsql = comsql & "身份证号码,"
  If Me.Check27 = True Then comsql = comsql & "联系电话,"
  If Me.Check29 = True Then comsql = comsql & "移动电话,"
  If Me.Check31 = True Then comsql = comsql & "电子邮件地址,"
  If Me.Check37 = True Then comsql = comsql & "婚姻状况,"
  If Me.Check39 = True Then comsql = comsql & "居住地,"
  If Me.Check41 = True Then comsql = comsql & "户口性质,"
  If Me.Check43 = True Then comsql = comsql & "学历,"
  If Me.Check45 = True Then comsql = comsql & "毕业学校,"
  If Me.Check47 = True Then comsql = comsql & "紧急联系人,"
  If Me.Check49 = True Then comsql = comsql & "紧急联系电话,"
  If Me.Check51 = True Then comsql = comsql & "部门,"
  If Me.Check53 = True Then comsql = comsql & "职务,"
  If Me.Check55 = True Then comsql = comsql & "入职时间,"
  If Me.Check57 = True Then comsql = comsql & "薪金,"
  If Me.Check59 = True Then comsql = comsql & "医疗保险基数,"
  If Me.Check53 = True Then comsql = comsql & "社会保险基数,"
  MsgBox comsql
  comsql = Left(comsql, Len(comsql) - 1)
  MsgBox comsql
  comsql = "select " & comsql & stfilterName1
  MsgBox comsql

  Set qdf = CurrentDb.QueryDefs("查询结果")
  qdf.SQL = comsql
  qdf.Close

  Set qdf = Nothing

  DoCmd.OutputTo acOutputQuery, "查询结果", acFormatXLS, , True

End Sub

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)