91 - Object variable or with block variable not set

[HaydenMB]

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 in [red]RED[/red].

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
[green]'DELETE OLD DATA[/green]
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;"
[green]'APPEND NEW DATA[/green]
DoCmd.OpenQuery "qry_Cost_Per_Car"
DoCmd.OpenQuery "qry_Cost_Per_Country_Append"
DoCmd.OpenQuery "qry_Cost_Per_Supplier_Report_Append"

[green]'CHECK THAT ALL 3 TABLES HAVE INFORAMTION IN[/green]
If DCount("*", "tbl_Cost_Per_Car&quot = 0 Or DCount("*", "tbl_Cost_Per_Supplier&quot = 0 _
Or DCount("*", "tbl_Cost_Per_Country&quot = 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&quot
Set rstCar = db.OpenRecordset("tbl_Cost_Per_Car&quot
Set rstCountry = db.OpenRecordset("tbl_Cost_Per_Country&quot

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

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&quot
appXL.Workbooks.Add

With appXL


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

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

[green]'COPY NEW COST PER SUPPLIER DATA INTO SHEET2[/green]
.Worksheets("Sheet3&quot.Activate
.Range("A1&quot.Value = "Country"
.Range("B1&quot.Value = "GSDB Code"
.Range("C1&quot.Value = "Supplier"
.Range("D1&quot.Value = "Country"
.Range("E1&quot.Value = "LTL Groupage"
.Range("F1&quot.Value = "FTLs"
.Range("G1&quot.Value = "Empty LTL Groupage"
.Range("H1&quot.Value = "Empty FTLs"
.Range("I1&quot.Value = "Total Mateiral Cost"
.Range("J1&quot.Value = "Total Empties Cost"
.Range("K1&quot.Value = "Volvo Cost"
.Range("L1&quot.Value = "Total Cost"
.Range("M1&quot.Value = "CPC"
.Range("A2&quot.CopyFromRecordset rstSupplier
[red]ActiveSheet.Name = "Cost Per Supplier"[/red][green] '** CODE BREAKS HERE ** [/green]
strFile = strFile & " - Baan.xls"
[red]ActiveSheet.SaveAs conAppFileLoc & strFile[/red][green] '** CODE BREAKS HERE ** [/green]
.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

 

[MakeItSo]

It's because your within a "With" block, so you must add a dot before ActiveSheet:

.ActiveSheet.Name

(It's the simple things that make us curse...) ;-)
Greetings,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[HaydenMB]

Thanx soooooo much, you are a God!!!!

cheers,
Hayden

 

[MakeItSo]

Thank God, I'm not!

CU