I need to create a workbook with multiple sheets

[mdub72]

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

 

[The_Doc_Man]

Are you talking about multiple distinct files with one sheet per record (and one sheet in each file)?

Or did you want one workbook (one file) with one sheet in the single workbook for each record?

The method you showed us would be one sheet per file and one file / sheet per record.

 

[The_Doc_Man]

Please also note that you may have double-entered your question in this thread:

Need to create an Excel workbook with multiple spreadsheets

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...

www.access-programmers.co.uk

Since you are new, we can be forgiving, but I doubt you need both threads. Please try to not clutter the forum with duplicate questions.

 

[ebs17]

I assume this code will be similar

If you also understand your code, you will notice:
- A new file name will result in a new file when exporting. So you should keep this constant with your desire.
- The resulting Excel sheet gets the content and name from the query. In order to create several different Excel sheets, you would need queries with different names.

An alternative variant would be:
- Create a new workbook using automation
- transfer the individual recordsets into Excel sheets to be created using CopyFromRecordset

 

[Uncle Gizmo]

I did something similar a while back:-

You can see more information on my website here:-

Make Excel Sheets From Access Table – Nifty Access

www.niftyaccess.com