问题描述
只需掌握一些VBA(这个东西的新给我,让我们一起承担!)
从查询 ContactDetails_SurveySoftOutcomes ,我想先找到在该查询所有的唯一值在 DEPTNAME 字段列表,因此 rsGroup
昏暗存储的 DEPTNAME 字段分组查询。
我然后将使用这个分组名单通过相同的查询再次骑自行车的方式,而是通过每一个独特的入口通过一个过滤器对整个记录每个过滤记录导出到自己的Excel的小号preadsheet ......看到做的,而不是
循环。
我的code的对 DoCmd.TransferS preadsheet
... rsExport
部分绊倒了。我有点新本,但我想我的点心的名字 rsExport
的记录不接受这种方法..?
有一个容易解决的code我已经开始或者我应该用一种完全不同的方式来实现这一切?
code
公用Sub ExportSoftOutcomes()昏暗rsGroup作为DAO.Recordset昏暗部作为字符串昏暗mypath中作为字符串mypath中=C:\ MyFolder的\设置rsGroup = CurrentDb.OpenRecordset(选择ContactDetails_SurveySoftOutcomes.DeptName_&放大器; 从ContactDetails_SurveySoftOutcomes GROUP BY ContactDetails_SurveySoftOutcomes.DeptName,dbOpenDynaset)做,而不是rsGroup.EOF 部= rsGroup!DEPTNAME 昏暗rsExport作为DAO.Recordset 设置rsExport = CurrentDb.OpenRecordset(SELECT * FROM ContactDetails_SurveySoftOutcomes_ &放大器; WHERE(((ContactDetails_SurveySoftOutcomes.DeptName)='&放大器;部门和放大器;)),dbOpenDynaset) DoCmd.TransferS preadsheet acExport,ACS preadsheetTypeExcel9,rsExport,mypath中和放大器;部及放大器; \&安培;部及放大器; - 软成果Survey.xls,真 rsGroup.MoveNext循环结束小组
固定code
公用Sub ExportSoftOutcomes()昏暗rsGroup作为DAO.Recordset昏暗部作为字符串昏暗mypath中作为字符串mypath中=C:\ MyFolder的\设置rsGroup = CurrentDb.OpenRecordset(选择ContactDetails_SurveySoftOutcomes.DeptName_&放大器; 从ContactDetails_SurveySoftOutcomes GROUP BY ContactDetails_SurveySoftOutcomes.DeptName,dbOpenDynaset)做,而不是rsGroup.EOF 部= rsGroup!DEPTNAME 昏暗rsExportSQL作为字符串 rsExportSQL =SELECT * FROM ContactDetails_SurveySoftOutcomes_ &放大器; WHERE(((ContactDetails_SurveySoftOutcomes.DeptName)='&放大器;部门和放大器;)) 昏暗rsExport作为DAO.QueryDef 设置rsExport = CurrentDb.CreateQueryDef(myExportQueryDef,rsExportSQL) DoCmd.TransferS preadsheet acExport,ACS preadsheetTypeExcel9,myExportQueryDef,mypath中和放大器;部及放大器; \&安培;部及放大器; - 软成果Survey.xls,真 CurrentDb.QueryDefs.Delete rsExport.Name rsGroup.MoveNext循环结束小组
您说的没错,你的 rsGroup
参数错误,获得预期的表名或SELECT查询。
试试这个code
strExport =SELECT * FROM ContactDetails_SurveySoftOutcomes_
&放大器; WHERE(((ContactDetails_SurveySoftOutcomes.DeptName)='&放大器;部门和放大器;))
设置qdfNew = CurrentDb.CreateQueryDef(myExportQueryDef,strExport)
DoCmd.TransferS preadsheet acExport,ACS preadsheetTypeExcel9,myExportQueryDef,mypath中和放大器;部及放大器; \&安培;部及放大器; - 软成果Survey.xls,真
CurrentDb.QueryDefs.Delete qdfNew.Name'清理
希望的作品
Just getting to grips some VBA (this stuff's new to me so bear with us!)
From query ContactDetails_SurveySoftOutcomes, I'm trying to first find a list of all the unique values in the DeptName field in that query, hence the rsGroup
Dim storing a Grouped query on the DeptName field.
I'm then going to use this grouped list as way of cycling through the same query again, but passing through each unique entry as a filter on the whole recordset and export each filtered recordset to its own Excel spreadsheet... see the Do While Not
loop.
My code's tripping up on the DoCmd.TransferSpreadsheet
... rsExport
part. I'm a bit new to this, but I guess my Dim name rsExport
for the recordset isn't accepted in this method..?
Is there an easy fix to the code I've already started or should I be using a completely different approach to achieve all this?
Code:
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
Fixed Code:
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
You're right that your rsGroup
parameter is wrong, access expects a table name or select query.
Try this code
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
Hope that works
这篇关于导出记录至S preadsheet的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!