Runtime error 91

[aman]

Hi guys

I am getting an annoying error "Runtime error 91 ,object variable or with block variable not defined" in the following code. When I change the name of excel file e.g from bb1 to cc1 then it works fine but after mutilple uses of cc1 the error again appears.

Private Sub Command15_Click()
Dim strsql As String
strsql = "SELECT Date1,Location,Department,Barcodevalue,Confirm FROM completed_table WHERE barcodevalue <>' ' and Location = Forms!form3!combo9 AND Department = Forms!form3!combo11 and [Date1]= date() and signature='No';"
If DCount("Name", "MSysobjects", "Name='qrytemp' and type=5") > 0 Then
DoCmd.DeleteObject acQuery, "qrytemp"
End If
Set qdf = CurrentDb.CreateQueryDef("qrytemp", strsql)
    Dim objXls As Excel.Application
    Dim objWrkBk As Excel.Workbook
    Dim xprtFile As String
  
   xprtFile = "C:\Documents and Settings\Amanpreet Kaur\Desktop\cc1.xls"  '  [COLOR=red]if i change the excel file name then works fine for few times
[/COLOR]    'On Error Resume Next
    DoCmd.OutputTo acOutputQuery, "qrytemp", acFormatXLS, xprtFile, False
    'On Error GoTo 0
     Set objXls = New Excel.Application
     objXls.Visible = False
     Const xlLandscape = 2
     Set objWrkBk = objXls.Workbooks.Open(xprtFile)
     objWrkBk.Sheets("qrytemp").Select
  [COLOR=magenta]  [/COLOR][COLOR=red] J = ActiveSheet.UsedRange.Rows.Count[/COLOR]
      With objWrkBk.Sheets("qrytemp")
           .Application.Rows("1:1").Select
        .Application.Selection.Font.Bold = True
         End With
           With objWrkBk.Sheets("qrytemp").PageSetup
        .RightHeader = "Quantity=" & J - 1
        .LeftHeader = "ReferenceID " 
        .CenterHeader = "&""Arial,Bold""&14" & "Daily Summary Report for Location " & Forms!form3!Combo9 & "  Department  " & Forms!form3!Combo11
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With 
         objWrkBk.PrintOut
    objWrkBk.Close SaveChanges:=False
    
    Set objWrkBk = Nothing
    objXls.Quit
    
    Set objXls = Nothing
Set qdf = Nothing
MsgBox "The report has been printed"
Combo9.Value = ""
Combo11.Value = ""
Command16.SetFocus

could anyone please sort out this error.

Regards
Aman

 

[Kafrin]

I'd recommend adding a line near the beginning like:

Dim J As Integer

Won't guarantee it'll solve your problem, but it may be where Access is getting in a twist and even if not it's good practice to define your variables.

 

[Kafrin]

Actually, having looked through again I think I've spotted your problem. The red line should be:

J = objWrkBk.ActiveSheet.UsedRange.Rows.Count

I think. Possibly:

J = objxls.ActiveSheet.UsedRange.Rows.Count

...can't remember if ActiveSheet is a method/property of the application or the workbook. Try both and see which works!

 

[Rabbie]

I'd recommend adding a line near the beginning like:

Dim J As Integer

Won't guarantee it'll solve your problem, but it may be where Access is getting in a twist and even if not it's good practice to define your variables.

I agree 100% with Kafrin. You should always declare all variables. Also make sure you have

Option Explicit

at the top of each of your VBA modules. It is amazing how many typos this will catch. Thus preventing many errors.