Need to create an Excel workbook with multiple spreadsheets (1 Viewer)

mdub72

New member
Local time
Today, 10:04
Joined
Oct 7, 2023
Messages
3
Hi, I need to loop through a recordset in Access to create an Excel report that has a new sheet for each record in the recordset. Any help is appreciated. I created similar code that creates a new workbook for each record, and I assume this code will be similar -

Private Sub cmdExport_Click()

Dim db As DAO.Database
Dim rsEmployees As DAO.Recordset
Dim strTitle As String
Dim strQry As String
Dim qdfTemp As DAO.QueryDef
Dim strQdf As String


If cboName.Value <> "" Then
MsgBox "Leave the criteria blank.", vbOKOnly
cboName.Value = ""
DoCmd.ShowAllRecords
Exit Sub
End If

If cboName.Value = "" Then

Set db = CurrentDb()
Set rsEmployees = db.OpenRecordset("select distinct * from tblEmployees")

strQdf = "Employees"

Do While Not rsEmployees.EOF
strTitle = rsEmployees.Fields(1).Value
strQry = "select * from tblEmployees where Name = '" & strTitle & "'"
Set qdfTemp = CurrentDb.CreateQueryDef(strQdf, strQry)
qdfTemp.Close
Set qdfTemp = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strQdf, "C:\Users\mdubi\OneDrive\Desktop\" & strTitle & ".xls"

CurrentDb.QueryDefs.Delete strQdf

rsEmployees.MoveNext
Loop

rsEmployees.Close

End If

MsgBox "Files Exported.", vbOKOnly

End Sub
 
This appears to be a duplicate of another thread you entered earlier.

Since the titles are different, I don't know why you posted twice unless you were trying to gain our attention. This is not a polite way to do that. Please give us time to look at things. We are literally scattered around the world, but a lot of our members are past midnight and into the wee hours of the morning.

I replied to your other thread with a question.
 

Users who are viewing this thread

Back
Top Bottom