Intermittent "Object Variable or With Block Variable not set" Error

[Jamesss]

I've been trying to debug a piece of VBA code which opens an Excel file from Access and selects certain data to record into an table.

I have been receiving an intermittent "Object Variable or With Block Variable not set" Error for quite some time now. It was difficult to find where the error originated from since when I execute the code from a FORM it yields the error yet when I go to debug in VBA Editor with breakpoints the error vanishes.

However I have isolated the problem to the lines:

With ActiveSheet.UsedRange
    iCol = .Cells(1, 1).Column + .Columns.Count - 1
    iLastRow = .Cells(1, 1).Row + .Rows.Count - 1
End With

within the function:

Function LoadTaskDefInitialisation(xlfilename1)
 'On Error GoTo Err_LoadTaskDefInitialisation
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(xlfilename1)
Set oSheet = oBook.Worksheets(1)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblEOTaskDefInitStatus")
iRow = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
iCol = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
'DoCmd.SetWarnings False
With ActiveSheet.UsedRange
    iCol = .Cells(1, 1).Column + .Columns.Count - 1
    iLastRow = .Cells(1, 1).Row + .Rows.Count - 1
End With
'DoCmd.SetWarnings True
'iRow is the last row in the worksheet that contains data
For nRow = 1 To iRow
    cGroupValue = oSheet.Range("D" & nRow).Value ''' Column where "Task Code" resides
    If InStr(1, cGroupValue, "Task Code :") = 1 Then
        '''Extract Task Code as a string for values following colon ": "
        cTaskCode = Right(cGroupValue, Len(cGroupValue) - InStr(cGroupValue, "EO-") + 1)
    ''' MsgBox (cTaskCode)
        cEONumber = Mid(cTaskCode, 4, 6) ' Extracts only the EO Number from cTaskCode assuming it is 6 numerals long
    
    End If
    
    cValue = oSheet.Range("G" & nRow).Value ''' "Initialized" data field is never empty
    ''' "Initialized" is the field heading in the MXi file
    
    If cValue <> "" And cValue <> ("Initialized") Then
        rs.AddNew
        rs("Task Code") = cTaskCode
        rs("EO Number") = cEONumber
        rs("Aircraft") = oSheet.Range("A" & nRow).Value
        rs("Rego") = oSheet.Range("B" & nRow).Value
        rs("EngineAPU_PN") = oSheet.Range("C" & nRow).Value
        rs("EngineAPU_SN") = oSheet.Range("D" & nRow).Value
        rs("Component_PN") = oSheet.Range("E" & nRow).Value
        rs("Component_SN") = oSheet.Range("F" & nRow).Value
        rs("Initialised") = oSheet.Range("G" & nRow).Value
        rs.Update
       
        
    End If
Next
rs.Close
oExcel.Quit
Set oExcel = Nothing
Set oBook = Nothing
Set oSheet = Nothing
Set rs = Nothing
    Exit Function
Err_LoadTaskDefInitialisation:
    MsgBox Err.Description, , , , vbExclamation
     
End Function

I find it strange that the error comes and goes even though I am opening the same Excel Files.

James

 

[Guus2005]

Make sure that your module starts with

Option Explicit

Seems to me that variables are used that are not dimensioned properly.

HTH

 

[Jamesss]

Checked. That has already been done, and have checked that all variables are dimensioned.

 

[LPurvis]

It's an old error (and I'm afraid I mean user error ;-)
You need to avoid implicit object creation by properly referencing your objects.
i.e.
With oExcel.ActiveSheet.UsedRange
or probably more ideally
With oSheet.UsedRange


Using Automation objects you need to be careful.
As you had it VBA will create the implicit Sheet object once, but not twice in the same code instance.
(I imagine you copied the code from an Excel macro - nothing wrong with that, as long as you catch these instances of object referencing

 

[Jamesss]

Thanks Leigh.

The problem appears to be fixed

I'll continue to try and break it to see how it goes.

 

[gemma-the-husky]

Set rs = CurrentDb.OpenRecordset("tblEOTaskDefInitStatus")

i often get the sort of error you report, when i use currentdb, as you have done

if instead i use

dim dbs as database
set dbs=currentdb

and then use

Set rs = dbs.OpenRecordset("tblEOTaskDefInitStatus")

i never seem to get the same problem

 

[LPurvis]

Gemma, how sure are you that the behaviour you describe is with recordsets - as opposed to using different DAO objects (TableDefs for example)?

 

[gemma-the-husky]

possibly you are right

i use recordsets often and tabledefs regularly but less often, obviously - having observed the behaviour (and i'm not sure which scenario it occurred with, now you mention it), i have got into the habit of always setting a dbs variable to hold the currentdb

 

[LPurvis]

Yep that's fair enough and I agree it's a good practice to get into.
(Or using a single persisted DB object throughout).

FWIW the behaviour you describe is (thankfully) consistent
As we all know, CurrentDb isn't an object in itself - but merely a method of the Application object which returns an object of type DAO.Database (which is a copy of the currently running DAO database instance - in theory the same as DBEngine(0)(0).

Because it isn't an object in itself - each time it's referenced it creates a new object (they just all happen to point to the same physical database).
Recordset objects self-persist the database parent - even though it isn't a persisted object itself. i.e. you can open a recordset as
Set rst = CurrentDb.OpenRecordset("TableName")
And further reference the recordset and its child objects.

However
Set tdf = CurrentDb.TableDefs("TableName")
will not persist the parent database object - and hence the tabledef object itself won't persist (as it requires a parent object).
We discussed this a while back over at UA if anyone's interested. (In a rare feat of restraint I actually brought the thread to a halt rather than go on and on as normal. But there's plenty of worthwhile topic there - someday we'll get back to it and finish off a bit of a more simple walkthrough.
When to use CurrentDb and when to set a variable

Cheers.