Writing to Excel Error (1004)

[Paul]

Hello,

I posted my situation this morning, but I can't seem to find it so I am
posting it again. I am sorry if this is a dupe.

My situation is that I am receiving an Application-Defined error message
(error 1004) when I write values to an Excel workbook. I am using Access
2003, DAO, and have a reference set to Excel 11.0.

I am building say a Customer recordset (Set rs =
dbCurr.OpenRecordset(strSQL, dbOpenDynaset)) and validating each field from
each record by creating an instance to a Customer Class. I set the Customer
recordset to the Customer Class recordset property and bind each field to the
Class's property. The Class's properties have Property Let procedures that
validate the data. If the field fails the validation, I raise a custom error
number and call a procedure in a Standard Module to write the record to the
Excel error report. Otherwise, the valid record gets written to an Excel
profile report (no problems with that).

The code where the error occurs is:

Public Sub fPopulateErrorReportRow(ByVal rsErrorRec As DAO.Recordset, _
ByRef lngCurrErrRow As Long)

' Set an error trap.
On Error GoTo Err_fPopulateErrorReportRow

' Access the worksheet's properties.
With mobjErrorWb.Worksheets(mintCurrWs)
.Cells(lngCurrErrRow, 1) = rsErrorRec.Fields(0) ' -----> Error
occurs here.
.Cells(lngCurrErrRow, 2) = rsErrorRec.Fields(1)
.Cells(lngCurrErrRow, 3) = rsErrorRec.Fields(2)
End With ' End accessing the worksheet's properties.

The code where I declare my Excel.Application object is in a Form Module
where:

Dim objXlApp As Object ' Reference to Excel instance.

' Instantiate Excel object.
If fIsAppRunning("Excel") Then
Set objXlApp = GetObject(Class:="Excel.Application")
Else
Set objXlApp = CreateObject(Class:="Excel.Application")
End If

I pass objXlApp as a parameter to a procedure in a Standard Module. There I
instantiate the Excel workbook (Set gobjXlErrorWb =
objXlApp.Workbooks(gstrcErrorReportDestinationFile)), validate the record,
and write either to the Profile or Error Report.

I hope I explained this enough. Any help is appreciated. Thanks.

Paul

 

[Paul]

Sorry, I instantiate the Excel workbook as a module-level variable instead of
as a global variable (i.e. gobjErrorWb):

' Copy Excel file from XLT to XLS file type.
FileCopy Source:=gstrcFilePath & gstrcErrorReportSourceFile, _
Destination:=gstrcFilePath & gstrcErrorReportDestinationFile

' Open the XLS file in a new window.
Application.FollowHyperlink Address:=gstrcFilePath & _
gstrcErrorReportDestinationFile, NewWindow:=True

' Set a reference to the Excel workbook.
Set mobjErrorWb = objXlApp.Workbooks(gstrcErrorReportDestinationFile)

Thanks.