You can't assume that there will be enough worksheets in the Excel workbook when you add one. It goes off of the USER'S option settings so if you have it set to only have two worksheets then it will only add two worksheets. You can modify your code to be like this:
Code:
Function ExportExcel_lot()
Dim xlworkbook As Excel.Workbook
Dim currpath As String
DoCmd.SetWarnings False
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlworkbook = xlApp.Workbooks.Add
Dim r1 As ADODB.Recordset
Set r1 = New ADODB.Recordset
r1.CursorLocation = adUseClient
With xlworkbook
Do Until .Worksheets.Count >= 4
.Worksheets.Add
Loop
.Worksheets(1).Name = "Doff"
.Worksheets(2).Name = "Creel"
.Worksheets(3).Name = "Sizing"
.Worksheets(4).Name = "Packing"
End With
DoCmd.SetWarnings True
End Function
And you incorrectly had the code
xlapp.Sheets.Add
which is not right. It is the workbook object which should be adding them.