Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002

    Unanswered: Not creating spreadsheet from Access!!

    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!!?

    Is there any other more stable way to export the 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") & "/" & Format(dt, "dd") & "/" & Format(dt, "yy")
    sql1 = "SELECT [Date Received], [Approval Received], [Design_Time_Calculation] FROM [Tool Order Log] WHERE [Die No] <> 0 AND [Tool ordered] = #" & dtString & "#"
    sql2 = "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] = #" & dtString & "#"
    Set rs = db.OpenRecordset(sql1, dbOpenDynaset)
    If rs.EOF = False And rs.BOF = False Then
    With rs
    Do Until .EOF
    'avgDay = IIf((DateDiff("ww", rs("[Approval received]"), rs("[Date Received]"), 2) < 1) And (DateDiff("ww", rs("[Approval received]"), rs("[Date Received]"), 2) > -1), rs("[Date Received]") - rs("[Approval received]"), (rs("[Date Received]") - rs("[Approval received]")) - DateDiff("ww", rs("[Approval received]"), rs("[Date Received]"), 2) * 2)
    !Design_Time_Calculation = avgDay
    End With
    'DoCmd.RunMacro "Tooling Requisition"
    Else: MsgBox "empty"
    End If

    Set sheet = GetObject("c:\tolog\TOL.xls")
    sheet.Application.DisplayAlerts = False
    sheet.Close vbYes
    Set qry = db.CreateQueryDef("qryXL", sql2)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryXL", "c:\tolog\TOL.xls", True
    Set sheet = GetObject("c:\tolog\TOL.xls")
    sheet.Application.DisplayAlerts = False
    sheet.Windows("TOL.xls").Visible = True
    sheet.Close vbYes

  2. #2
    Join Date
    Jul 2002
    your hand must be hurting. First thing I would try is to create an actual error on the on error statement. If the program errors out at least you can get a description and and error number

    on error goto errorsection


    exit sub
    msgbox(err.description & " " & err.number)
    end sub

    The next thing I would do is to take the sql statements and run them thru the view window get the variable for sql 1 copy this text and then go into a query and create the sql statement and run the query. Make sure your statement is set up correctly. I would do this for the sql 2 line item also.
    Also when you are writing to the drive make sure the xls file does not exist already. I know when I have done exports thru macros I would get a prompt to delete the file if it exists. I have not done this thru code that much . My suspicion is there is a flag that checks this. If it is not se tup correctly to the proper value this could be failing without you knowing.

    good luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts