刚开交手一些VBA（这个东西的新给我，让我们一起承担！）
从查询ContactDetails_SurveySoftOutcomes，我想先找到在该查询的DEPTNAME场，因此所有的唯一值的列表rsGroup昏暗存储在DEPTNAME字段分组查询。
然后我会用这个分组名单通过相同的查询再次骑自行车的方式，而是通过每一个唯一的入口路过的过滤器对整个记录和导出每个过滤记录到自己的Excel电子表格......看到Do While Not循环。
我的代码的跳脱了DoCmd.TransferSpreadsheet ... rsExport一部分。 我有点新本，但我想我的点心名字rsExport的记录不在此方法接受..？
有一个简单的办法来我已经开始或者我应该用完全不同的方法来实现这一切的代码？
代码 ：
Public Sub ExportSoftOutcomes()
Dim rsGroup As DAO.Recordset
Dim Dept As String
Dim myPath As String
myPath = "C:\MyFolder\"
Set rsGroup = CurrentDb.OpenRecordset("SELECT ContactDetails_SurveySoftOutcomes.DeptName " _
& "FROM ContactDetails_SurveySoftOutcomes GROUP BY ContactDetails_SurveySoftOutcomes.DeptName", dbOpenDynaset)
Do While Not rsGroup.EOF
Dept = rsGroup!DeptName
Dim rsExport As DAO.Recordset
Set rsExport = CurrentDb.OpenRecordset("SELECT * FROM ContactDetails_SurveySoftOutcomes " _
& "WHERE (((ContactDetails_SurveySoftOutcomes.DeptName)='" & Dept & "'))", dbOpenDynaset)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rsExport, myPath & Dept & "\" & Dept & " - Soft Outcomes Survey.xls", True
rsGroup.MoveNext
Loop
End Sub
固定码 ：
Public Sub ExportSoftOutcomes()
Dim rsGroup As DAO.Recordset
Dim Dept As String
Dim myPath As String
myPath = "C:\MyFolder\"
Set rsGroup = CurrentDb.OpenRecordset("SELECT ContactDetails_SurveySoftOutcomes.DeptName " _
& "FROM ContactDetails_SurveySoftOutcomes GROUP BY ContactDetails_SurveySoftOutcomes.DeptName", dbOpenDynaset)
Do While Not rsGroup.EOF
Dept = rsGroup!DeptName
Dim rsExportSQL As String
rsExportSQL = "SELECT * FROM ContactDetails_SurveySoftOutcomes " _
& "WHERE (((ContactDetails_SurveySoftOutcomes.DeptName)='" & Dept & "'))"
Dim rsExport As DAO.QueryDef
Set rsExport = CurrentDb.CreateQueryDef("myExportQueryDef", rsExportSQL)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "myExportQueryDef", myPath & Dept & "\" & Dept & " - Soft Outcomes Survey.xls", True
CurrentDb.QueryDefs.Delete rsExport.Name
rsGroup.MoveNext
Loop
End Sub
你说的没错，你rsGroup参数是错误的，希望访问的表名或选择查询。
试试这个代码：
strExport = "SELECT * FROM ContactDetails_SurveySoftOutcomes " _
& "WHERE (((ContactDetails_SurveySoftOutcomes.DeptName)='" & Dept & "'))"
Set qdfNew = CurrentDb.CreateQueryDef("myExportQueryDef", strExport)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "myExportQueryDef", myPath & Dept & "\" & Dept & " - Soft Outcomes Survey.xls", True
CurrentDb.QueryDefs.Delete qdfNew.Name 'cleanup
希望工程
DoCmd.TransferSpreadsheet预计其第三个参数是指定一个表或查询的名称的字符串（变量或文字）。 因此，而不是打开一个DAO.Recordset你可以创建一个DAO.QueryDef名为类似“forExportToExcel”具有相同的SQL代码，然后在TransferSpreadsheet的呼叫使用该名称。
试试这个，希望这将帮助你
Function Export2XLS(sQuery As String)
Dim oExcel As Object
Dim oExcelWrkBk As Object
Dim oExcelWrSht As Object
Dim bExcelOpened As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iCols As Integer
Const xlCenter = -4108
'Start Excel
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application") 'Bind to existing instance of Excel
If Err.Number <> 0 Then 'Could not get instance of Excel, so create a new one
Err.Clear
On Error GoTo Error_Handler
Set oExcel = CreateObject("excel.application")
bExcelOpened = False
Else 'Excel was already running
bExcelOpened = True
End If
On Error GoTo Error_Handler
oExcel.ScreenUpdating = False
oExcel.Visible = False 'Keep Excel hidden until we are done with our manipulation
Set oExcelWrkBk = oExcel.Workbooks.Add() 'Start a new workbook
Set oExcelWrSht = oExcelWrkBk.Sheets(1)
'Open our SQL Statement, Table, Query
Set db = CurrentDb
Set rs = db.OpenRecordset(sQuery, dbOpenSnapshot)
With rs
If .RecordCount <> 0 Then
'Build our Header
For iCols = 0 To rs.Fields.Count - 1
oExcelWrSht.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
With oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
oExcelWrSht.Cells(1, rs.Fields.Count))
.Font.Bold = True
.Font.ColorIndex = 2
.Interior.ColorIndex = 1
.HorizontalAlignment = xlCenter
End With
oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
oExcelWrSht.Cells(1, rs.Fields.Count)).Columns.AutoFit 'Resize our Columns based on the headings
'Copy the data from our query into Excel
oExcelWrSht.Range("A2").CopyFromRecordset rs
oExcelWrSht.Range("A1").Select 'Return to the top of the page
Else
MsgBox "There are no records returned by the specified queries/SQL statement.", vbCritical + vbOKOnly, "No data to generate an Excel spreadsheet with"
GoTo Error_Handler_Exit
End If
End With
' oExcelWrkBk.Close True, sFileName 'Save and close the generated workbook
' 'Close excel if is wasn't originally running
' If bExcelOpened = False Then
' oExcel.Quit
' End If
Error_Handler_Exit:
On Error Resume Next
oExcel.Visible = True 'Make excel visible to the user
rs.Close
Set rs = Nothing
Set db = Nothing
Set oExcelWrSht = Nothing
Set oExcelWrkBk = Nothing
oExcel.ScreenUpdating = True
Set oExcel = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Export2XLS" & vbCrLf & _
"Error Description: " & Err.Description _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
