Hello, I am trying to use the DoCmd.TransferSpreadsheet command to export a query to Excel multiple times based on a field value from another table. My code is attached as well as a sample DB. I have the VBA code attached to the on click property of the button on Form1. It is not working for me. I get an error message "Run-time error '31532': Microsoft Access was unable to export the data". Any help in solving would be appreciated! Thanks, Jim
Code:
Private Sub TJC1_Click()
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim qdfCurr As DAO.QueryDef
Dim strFolder As String
Dim strSQL As String
strFolder = "C:\Users\jpkeller\Documents\Roster\"
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT SVCBR_ID FROM SB")
Set qdfCurr = dbCurr.CreateQueryDef("qryTemp")
Do While rsCurr.EOF = False
strSQL = "SELECT SVCBR_ID, CITY, STATE FROM RosterDetail WHERE SVCBR_ID = '" & rsCurr!SVCBR_ID & "'"
qdfCurr.SQL = strSQL
qdfCurr.Close
DoCmd.TransferSpreadsheet acExport, , "qryTemp", strFolder & "Details for " & rsCurr!SVCBR_ID & ".xlsx", True
rsCurr.MoveNext
Loop
rsCurr.Close
Set rsCurr = Nothing
Set qdfCurr = Nothing
Set dbCurr = Nothing
End Sub