Further to...This is what i have written.  It fails on trying to open the 2nd
recordset

Public Sub CreateWorkBooks()
'-----------------------------------------------------------------
'Create MS Excel Workbooks with account sheets
'
'-----------------------------------------------------------------
   'Declare connection
   Dim cnn1 As ADODB.Connection
   
   'Declare recordsets
   Dim rsResp As ADODB.Recordset
   Dim rsAcctData As ADODB.Recordset
   
   'Declare SQL strings
   Dim strRespSQL As String, strAcctDataSQL As String
   
   'Declare Period titles
   Dim strPer1 As String, strPer2 As String
   Dim strPer3 As String, strPer4 As String
   
   'Declare Excel objects
   Dim xlApp As Excel.Application
   Dim xlWorkbook As Excel.Workbook
   Dim xlWorkSheet As Excel.Worksheet
   
   'Declare other
   Dim strDate As Date, strResp As String
       
On Error GoTo ErrorHandler

    Set cnn1 = CurrentProject.Connection
   Set rsResp = New ADODB.Recordset
   Set rsAcctData = New ADODB.Recordset

   'Assign Period Titles
   strPer1 = DLookup("[Period1]", "tblPeriodTitles", "[id] = 1")
   strPer2 = DLookup("[Period2]", "tblPeriodTitles", "[id] = 1")
   strPer3 = DLookup("[Period3]", "tblPeriodTitles", "[id] = 1")
   strPer4 = DLookup("[Period4]", "tblPeriodTitles", "[id] = 1")

   'Create Excel application
   Set xlApp = New Excel.Application
   'Make Excel visible
   xlApp.Visible = True
   
   'Assign Date
   strDate = Forms!frmCreateWorksheets!txtBalDate
   
   strRespSQL = "SELECT tblResp.RespId, tblResp.RespDescr,
tblResp.RespActive, " _
               & "tblResp.RespFileName " _
               & "FROM tblResp " _
               & "WHERE (((tblResp.RespActive)=True)); "
               
   'Open rsResp recordset
   With rsResp
       .Open strRespSQL, cnn1
       .CursorType = adOpenKeyset
   End With
   
   strResp = rsResp.Fields(0).Value

   strAcctDataSQL = "SELECT tblSections.Section, tblSections.SectionDescr,
" _
                   & "tblAccounts.Account, tblAccounts.AcctDescr,
tblAccounts.AcctResp, tblGLData.Period1, " _
                   & "tblGLData.Period2, tblGLData.Period3,
tblGLData.Period4 " _
                   & "FROM (tblAccounts INNER JOIN tblSections ON
tblAccounts.Section = tblSections.Section) INNER JOIN tblGLData ON
tblAccounts.Account = tblGLData.Account " _
                   & "WHERE (((tblAccounts.AcctResp)=strResp));"
               
   'Open rsAcctData recordset (FAILS WHEN TRYING TO OPEN THIS)
   With rsAcctData
       .Open strAcctDataSQL, cnn1
       .CursorType = adOpenKeyset
   End With
   
   'Loop through rsResp recordset, creating workbooks, sheets and saving
   Do Until rsResp.EOF
'        Debug.Print rsResp.Fields(1).Value
       'Open template
       Set xlWorkbook = xlApp.Workbooks.Open("c:\path\bsrtemplate.xls")
       rsAcctData.Requery
           Do Until rsAcctData.EOF
               xlApp.ActiveSheet.Name = rsAcctData.Fields(2).Value
               xlApp.Range("C4").Value = rsAcctData.Fields(3)
           
           Loop
   Loop
       
   'Clean up objects
   rsResp.Close
   cnn1.Close
   Set rsResp = Nothing
   Set cnn1 = Nothing
     
   Exit Sub

ErrorHandler:
   ' Display error information.
   MsgBox "Error number " & Err.Number & ": " & Err.Description
   ' Resume with statement following occurrence of error.
   Resume Next
End Sub

I tend to avoid automation unless a) the user REALLY can't put things in
bold for themselves and b) they have some decent blackmail material that
they are threatening to send to my wife.  (Simplistic, but generally true.)

Why not just a couple of loops with a TransferSpreadsheet in the middle?