导出记录至S preadsheet [英] Exporting Recordset to Spreadsheet

问题描述

只需掌握一些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_     ＆放大器; WHE​​RE（（（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_     ＆放大器; WHE​​RE（（（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_
＆放大器; WHE​​RE（（（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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！