Office中国论坛/Access中国论坛

标题: 请大家帮助解决access数据库导出到EXCEL中的数据按照ID排序 [打印本页]
作者: yanwei82123300    时间: 2010-9-15 11:24
标题: 请大家帮助解决access数据库导出到EXCEL中的数据按照ID排序
下面是由access数据库导出到EXCEL中的代码，但是现在有一个问题：无法按照字段名（ID）进行排序使导出数据按照ID进行排序，请大家帮助 谢谢
  1. Private Sub Combo11_AfterUpdate()
  2. Me.Combo13 = ""
  3. Me.Combo13.Requery
  4. End Sub

  6. Private Sub ToParts_Click()
  7. If IsNull(Me.Combo11) Or Me.Combo11 = "" Then
  8. MsgBox "请选择Team！", vbCritical
  9. Me.Combo11.SetFocus
  10. Exit Sub
  11. End If
  12. If IsNull(Me.Combo13) Or Me.Combo13 = "" Then
  13. MsgBox "请选择MPSDATE！", vbCritical
  14. Me.Combo13.SetFocus
  15. Exit Sub
  16. End If



  20. Dim msgTmp As VbMsgBoxResult
  21. msgTmp = MsgBox("确定要导出吗?", vbQuestion + vbYesNo, "Print")
  22. If msgTmp <> vbYes Then Exit Sub
  23. Dim i As Long
  24. Dim c, d As String

  26. Dim xlsAPP As Excel.Application
  27. Dim xlsBook As Excel.Workbook
  28. Dim xlsSheet As Excel.Worksheet
  29. Set xlsAPP = CreateObject("Excel.Application")
  30. xlsAPP.Visible = True
  31. Dim cur_r As Long
  32. Dim dbs As Database
  33. Dim Rst1 As Recordset
  34. Dim strSQL As String
  35. c = Me.Combo11
  36. d = Me.Combo13
  37. Set dbs = CurrentDb
  38. Set Rst1 = dbs.OpenRecordset("SELECT PaiChan_REMOTE.* FROM PaiChan_REMOTE WHERE (((PaiChan_REMOTE.Team)='" & c & "') AND ((PaiChan_REMOTE.Release_Date)=# " & d & " #)) order by PaiChan_REMOTE.RQST;")



  42. Set xlsBook = xlsAPP.Workbooks.OPEN("D:\散件生产计划系统\PaiChanforPI.XLT")

  44. Set xlsSheet = xlsBook.Worksheets("PAICHAN")
  45. xlsSheet.Activate
  46. cur_r = 4


  49. If Rst1.EOF = False Then
  50. xlsSheet.Cells(1, 1) = Rst1!Team

  52. Rst1.MoveFirst
  53. For i = 1 To Rst1.RecordCount
  54. cur_r = cur_r + 1
  55. xlsSheet.Cells(cur_r, 1) = Rst1!CUST_CLAS71
  56. 'xlsSheet.Cells(cur_r, 2) = Rst1!Remark
  57. xlsSheet.Cells(cur_r, 2) = Rst1!id
  58. xlsSheet.Cells(cur_r, 3) = Rst1!CO_NO
  59. xlsSheet.Cells(cur_r, 4) = Rst1!Line_No
  60. xlsSheet.Cells(cur_r, 5) = Rst1!CO_Item
  61. xlsSheet.Cells(cur_r, 6) = Rst1!QTY
  62. xlsSheet.Cells(cur_r, 7) = Rst1!Serial_No
  63. xlsSheet.Cells(cur_r, 8) = Rst1!Desc
  64. xlsSheet.Cells(cur_r, 9) = Rst1!RQST '
  65. xlsSheet.Cells(cur_r, 10) = Rst1!CustAkDt
  66. xlsSheet.Cells(cur_r, 12) = Rst1!Info11
  67. xlsSheet.Cells(1, 12) = Rst1!Release_Date


  70. '........后面自已加


  73. '画格子
  74. With xlsSheet.Range(xlsSheet.Cells(cur_r, 1), xlsSheet.Cells(cur_r, 12)).Borders(xlEdgeLeft)
  75. .LineStyle = xlContinuous
  76. .Weight = xlThin
  77. .ColorIndex = xlAutomatic
  78. End With
  79. With xlsSheet.Range(xlsSheet.Cells(cur_r, 1), xlsSheet.Cells(cur_r, 12)).Borders(xlEdgeTop)
  80. .LineStyle = xlContinuous
  81. .Weight = xlThin
  82. .ColorIndex = xlAutomatic
  83. End With
  84. With xlsSheet.Range(xlsSheet.Cells(cur_r, 1), xlsSheet.Cells(cur_r, 12)).Borders(xlEdgeBottom)
  85. .LineStyle = xlContinuous
  86. .Weight = xlThin
  87. .ColorIndex = xlAutomatic
  88. End With
  89. With xlsSheet.Range(xlsSheet.Cells(cur_r, 1), xlsSheet.Cells(cur_r, 12)).Borders(xlEdgeRight)
  90. .LineStyle = xlContinuous
  91. .Weight = xlThin
  92. .ColorIndex = xlAutomatic
  93. End With
  94. With xlsSheet.Range(xlsSheet.Cells(cur_r, 1), xlsSheet.Cells(cur_r, 12)).Borders(xlInsideVertical)
  95. .LineStyle = xlContinuous
  96. .Weight = xlThin
  97. .ColorIndex = xlAutomatic
  98. End With
  99. Rst1.MoveNext
  100. Next i


  103. End If





  109. Exit_cmdxls_Click:
  110. Exit Sub

  112. Err_cmdxls_Click:
  113. MsgBox err.Description
  114. Resume Exit_cmdxls_Click
  115. End Sub
复制代码

作者: todaynew    时间: 2010-9-15 11:24
把这句：
38.Set Rst1 = dbs.OpenRecordset("SELECT PaiChan_REMOTE.* FROM PaiChan_REMOTE WHERE (((PaiChan_REMOTE.Team)='" & c & "') AND ((PaiChan_REMOTE.Release_Date)=# " & d & " #)) order by PaiChan_REMOTE.RQST;")
改成：
38.Set Rst1 = dbs.OpenRecordset("SELECT * FROM PaiChan_REMOTE WHERE  Team='" & c & "'  AND Release_Date=# " & d & " #  order by  ID")
作者: 石三少    时间: 2010-9-15 11:45
钱太少，观望中，希望涨价！现在什么都涨，你怎么可以不涨？
作者: yanwei82123300    时间: 2010-9-15 15:52
todaynew 谢谢帮助我改试试！



欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/) Powered by Discuz! X3.3