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).Colu mn
'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

Make sure that your module starts with
Option Explicit
Seems to me that variables are used that are not dimensioned properly.

HTH:D

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

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 :-)

Thanks Leigh.

The problem appears to be fixed :)

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

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

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

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

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 (http://www.utteraccess.com/forums/showflat.php?Cat=&Board=84&Number=1720104)

Cheers.