ms access - Exporting Recordset to Spreadsheet -

just getting grips vba (this stuff's new me bear us!)

from query contactdetails_surveysoftoutcomes, i'm trying first find list of unique values in deptname field in query, hence rsgroup dim storing grouped query on deptname field.

i'm going use grouped list way of cycling through same query again, passing through each unique entry filter on whole recordset , export each filtered recordset own excel spreadsheet... see do while not loop.

my code's tripping on docmd.transferspreadsheet ... rsexport part. i'm bit new this, guess dim name rsexport recordset isn't accepted in method..?

is there easy fix code i've started or should using different approach achieve this?

code:

public sub exportsoftoutcomes()  dim rsgroup dao.recordset dim dept string dim mypath string  mypath = "c:\myfolder\"  set rsgroup = currentdb.openrecordset("select contactdetails_surveysoftoutcomes.deptname " _ & "from contactdetails_surveysoftoutcomes group contactdetails_surveysoftoutcomes.deptname", dbopendynaset)  while not rsgroup.eof      dept = rsgroup!deptname      dim rsexport dao.recordset     set rsexport = currentdb.openrecordset("select * 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 dao.recordset dim dept string dim mypath string  mypath = "c:\myfolder\"  set rsgroup = currentdb.openrecordset("select contactdetails_surveysoftoutcomes.deptname " _ & "from contactdetails_surveysoftoutcomes group contactdetails_surveysoftoutcomes.deptname", dbopendynaset)  while not rsgroup.eof     dept = rsgroup!deptname      dim rsexportsql string     rsexportsql = "select * contactdetails_surveysoftoutcomes " _     & "where (((contactdetails_surveysoftoutcomes.deptname)='" & dept & "'))"      dim rsexport 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 rsgroup parameter wrong, access expects table name or select query.

try code:

strexport = "select * 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 works