Why can't i export an excel spreadsheet?

[simmohall]

Can someone explain to me why the below code doesn't work properly.

It doesn't create a spreasheet...sometimes. If i set a breakpoint and work through the code step by step in debug mode then the spreadsheet gets created perfectly. But the next day, the same code (without breakpoints or anything) doesn't create the file at all!!? This is with Excel 2002? does it matter?

Is there any other more stable way to export the code?

CODE:

Dim projtype As String
Dim qry As QueryDef
Dim avgDay As Integer
Dim rs As Recordset
Dim sql1, sql2, dtString As String
Dim dt As Date
Dim sheet As Object

Set db = CurrentDb()
On Error Resume Next
DoCmd.DeleteObject acQuery, "qryXL"
dt = Date
dtString = Format(dt, "mm&quot & "/" & Format(dt, "dd&quot & "/" & Format(dt, "yy&quot
sql1 = &quot;SELECT [Date Received], [Approval Received], [Design_Time_Calculation] FROM [Tool Order Log] WHERE [Die No] <> 0 AND [Tool ordered] = #&quot; & dtString & &quot;#&quot;
sql2 = &quot;SELECT [Tool ordered], [Section No], [Die No], [Press], [Apertures], [Weld plate], [Expansion], [Die], [Porthole], [Backer], [Bolster], [Designer].[Designer], [tblToolmaker].[Toolmaker], [Date due], [Held / Comments], [Backers], [Die Holder], [Bolsters], [Bolster Insert], [Cep / Exp], [Date Approved], [Layout Drawing Status], [CQI Design Status], [Special Requirements], [Speed], [Billet Temperature], [Design_Time_Calculation] FROM [Tool Order Log], [Designer], [tblToolmaker] WHERE [Designer].ID=[Tool Order Log].Designer AND [tblToolmaker].ID=[Tool Order Log].[Toolmaker] AND [Die No] <> 0 AND [Tool ordered] = #&quot; & dtString & &quot;#&quot;
Set rs = db.OpenRecordset(sql1, dbOpenDynaset)
If rs.EOF = False And rs.BOF = False Then
With rs
Do Until .EOF
.Edit
'avgDay = IIf((DateDiff(&quot;ww&quot;, rs(&quot;[Approval received]&quot, rs(&quot;[Date Received]&quot, 2) < 1) And (DateDiff(&quot;ww&quot;, rs(&quot;[Approval received]&quot, rs(&quot;[Date Received]&quot, 2) > -1), rs(&quot;[Date Received]&quot - rs(&quot;[Approval received]&quot, (rs(&quot;[Date Received]&quot - rs(&quot;[Approval received]&quot) - DateDiff(&quot;ww&quot;, rs(&quot;[Approval received]&quot, rs(&quot;[Date Received]&quot, 2) * 2)
!Design_Time_Calculation = avgDay
.Update
.MoveNext
Loop
End With
'DoCmd.RunMacro &quot;Tooling Requisition&quot;
Else: MsgBox &quot;empty&quot;
End If

Set sheet = GetObject(&quot;c:\tolog\TOL.xls&quot
sheet.Application.DisplayAlerts = False
sheet.worksheets.Add
sheet.worksheets(&quot;qryXL&quot.Delete
sheet.Close vbYes
Set qry = db.CreateQueryDef(&quot;qryXL&quot;, sql2)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, &quot;qryXL&quot;, &quot;c:\tolog\TOL.xls&quot;, True
Set sheet = GetObject(&quot;c:\tolog\TOL.xls&quot
sheet.Application.DisplayAlerts = False
sheet.worksheets(&quot;Sheet1&quot.Delete
sheet.Windows(&quot;TOL.xls&quot.Visible = True
sheet.Close vbYes