Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    UK
    Posts
    1

    Unanswered: 91 - Object variable or with block variable not set

    Hi,

    I have some code written that creates and Excel Application, copies 3 recordsets (Each is a seperate report) into 3 different worksheets.

    The code works fine the first time i run it but if I try to run the code again without closing the database i get a error message "91 - Object variable or with block variable not set".

    The error happens at 4 locations within the code, the code is below and the line that causes the break is highlighted below

    I would be s grateful if someone could tell me what i have done wrong and how i could possily fix it.


    Many Thanks
    Hayden



    Public Sub Export_Reports()
    On Error GoTo errConfig
    'DELETE OLD DATA
    DoCmd.RunSQL "DELETE tbl_Cost_Per_Car.* FROM tbl_Cost_Per_Car;"
    DoCmd.RunSQL "DELETE tbl_Cost_Per_Country.* FROM tbl_Cost_Per_Country;"
    DoCmd.RunSQL "DELETE tbl_Cost_Per_Supplier.* FROM tbl_Cost_Per_Supplier;"
    'APPEND NEW DATA
    DoCmd.OpenQuery "qry_Cost_Per_Car"
    DoCmd.OpenQuery "qry_Cost_Per_Country_Append"
    DoCmd.OpenQuery "qry_Cost_Per_Supplier_Report_Append"

    'CHECK THAT ALL 3 TABLES HAVE INFORAMTION IN
    If DCount("*", "tbl_Cost_Per_Car") = 0 Or DCount("*", "tbl_Cost_Per_Supplier") = 0 _
    Or DCount("*", "tbl_Cost_Per_Country") = 0 Then
    MsgBox "not all tables have inforamtion in. Please check that you have not missed any processes"
    Exit Sub
    End If

    DoCmd.SetWarnings False

    Set db = CurrentDb
    Set rstSupplier = db.OpenRecordset("tbl_Cost_Per_Supplier")
    Set rstCar = db.OpenRecordset("tbl_Cost_Per_Car")
    Set rstCountry = db.OpenRecordset("tbl_Cost_Per_Country")

    'SET VARIABLES FOR EXPORT INTO SPREADSHEET WITH CALCUALTION DATA
    With Forms![Import BaaN PFEP]
    strDate = CStr(Format(Now, "dd-mm-yy"))
    strTime = CStr(Format(Now, "hh:nn"))
    intWeeklyBuild = !Weekly_Build
    intMonthlyBuild = !Monthly_Build
    strMinVol = CStr(Format(!Min_Trailer_Vol, "00.00%"))
    strMaxVol = CStr(Format(!Max_Trailer_Vol, "00.00%"))
    strProfit = CStr(Format((!ProffitCalc - 1), "00.00%"))
    strStart = CStr(Format(!calStart, "dd-mm-yy"))
    strEnd = CStr(Format(!calEnd, "dd-mm-yy"))
    strReference = !List32
    strUser = GetLoginUserName 'Get windows user name

    End With

    GetFileName:
    strFile = Trim(InputBox("Please enter the name you wish to save these reports as.", conAppName, strReference))

    If IsNull(strFile) Or strFile = "" Then
    msbbox "Please enter a reference for the file",,conAppName
    GoTo GetFileName
    End If
    Set appXL = CreateObject("Excel.Application")
    appXL.Workbooks.Add

    With appXL


    'COPY NEW COST PER CAR DATA INTO SHEET1
    .Range("B2").Value = "Start Date of Week"
    .Range("B3").Value = "End Date of Week"
    .Range("B4").Value = "Weekly Build"
    .Range("B5").Value = "Monthly Build"
    .Range("B6").Value = "Min Trailer Util (%)"
    .Range("B7").Value = "Max Trailer Util (%)"
    .Range("B8").Value = "Profit Margin"
    .Range("F2").Value = "Date Report Created"
    .Range("F3").Value = "Time Report Created"
    .Range("F4").Value = "Created By"
    .Range("D2").Value = strStart
    .Range("D3").Value = strEnd
    .Range("D4").Value = intWeeklyBuild
    .Range("D5").Value = intMonthlyBuild
    .Range("D6").Value = strMinVol
    .Range("D7").Value = strMaxVol
    .Range("D8").Value = strProfit
    .Range("F2").Value = strDate
    .Range("F3").Value = strTime
    .Range("F4").Value = strUser
    .Range("A11").Value = "VAT Region"
    .Range("B11").Value = "Material Cost"
    .Range("C11").Value = "Empties Cost"
    .Range("D11").Value = "Volvo Cost"
    .Range("E11").Value = "Total Cost"
    .Range("F11").Value = "CPC"
    .Range("A12").CopyFromRecordset rstCar
    ActiveSheet.Name = "Cost Per Car" '** CODE BREAKS HERE **

    'COPY NEW COST PER COUNTRY DATA INTO SHEET2
    .Worksheets("Sheet2").Activate
    .Range("A1").Value = "Country"
    .Range("B1").Value = "LTL Groupage"
    .Range("C1").Value = "FTLs"
    .Range("D1").Value = "Empty LTL Groupage"
    .Range("E1").Value = "Empty FTLs"
    .Range("F1").Value = "Total Mateiral Cost"
    .Range("G1").Value = "Total Empties Cost"
    .Range("H1").Value = "Volvo_Cost"
    .Range("I1").Value = "Total Cost"
    .Range("J1").Value = "CPC"
    .Range("A2").CopyFromRecordset rstCountry
    ActiveSheet.Name = "Cost Per Country" '** CODE BREAKS HERE **

    'COPY NEW COST PER SUPPLIER DATA INTO SHEET2
    .Worksheets("Sheet3").Activate
    .Range("A1").Value = "Country"
    .Range("B1").Value = "GSDB Code"
    .Range("C1").Value = "Supplier"
    .Range("D1").Value = "Country"
    .Range("E1").Value = "LTL Groupage"
    .Range("F1").Value = "FTLs"
    .Range("G1").Value = "Empty LTL Groupage"
    .Range("H1").Value = "Empty FTLs"
    .Range("I1").Value = "Total Mateiral Cost"
    .Range("J1").Value = "Total Empties Cost"
    .Range("K1").Value = "Volvo Cost"
    .Range("L1").Value = "Total Cost"
    .Range("M1").Value = "CPC"
    .Range("A2").CopyFromRecordset rstSupplier
    ActiveSheet.Name = "Cost Per Supplier" '** CODE BREAKS HERE **
    strFile = strFile & " - Baan.xls"
    ActiveSheet.SaveAs conAppFileLoc & strFile '** CODE BREAKS HERE **
    .Visible = True
    End With

    Set appXL = Nothing
    Set wsXL = Nothing

    DoCmd.SetWarnings True
    MsgBox "The export of the reports is now complete", vbInformation, conAppName
    Exit Sub

    errConfig:
    MsgBox Err.Number & " - " & Err.Description

    Resume Next
    End Sub

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    You have placed the ActiveSheet object within your With statement and it is not defined to be there.

Posting Permissions

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