I just suddenly got this error Object Invalid or No Longer Set when executing DoCmd.TransferSpreadSheet acExport. The module that the code is part of was working and still is working in another database that I have been using a lot. I checked my database links and they are valid and I can read data in them and also in queries derived from them. This problem has occurred only with this database and on two different computers using the same versions of Access and Office. I tried a new database, importing all objects from the old, but that does not seem to solve the problem. I did some research on the web about the problem and it always seems to go away without anyone satisfactorily understanding why. When I comment out any error trapping, the error occurs on that statement.
The code of the function involved is below.
Code:
Public Function fExportToExcel(strQuery As String, strPath As String) As Integer 'changed from boolean to integer v.1.0.004 06 Aug 20 sjl' Reference: DMW Consultancy, "How to Export Microsoft® Access Data to Excel" http://www.consultdmw.com/export-access-data-to-excel.html, viewed 26 Jul 20
' Values rendered: 0 = not successful, 1 = successful
'Modifications:
'v.1.1.000 Change from boolean to integer, values 0 = not successful, 1 = successful. 06 Aug 20 sjl
Dim lxlApp As Object, lxlFile As Object
fExportToExcel = 0
'On Error GoTo ErrExportToExcel
Set dbCurrent = CurrentDb
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:=strQuery, _
FileName:=strPath, _
HasFieldNames:=True
Set lxlApp = CreateObject("Excel.Application")
With lxlApp
.Visible = False
Set lxlFile = .Workbooks.Open(strPath)
End With
fExportToExcel = 1
ExitExportToExcel:
On Error Resume Next
lxlFile.Close
Set lxlFile = Nothing
lxlApp.Close
Set lxlApp = Nothing
Exit Function
ErrExportToExcel:
MsgBox Err.Description, vbExclamation, "Error in fExportToExcel"
Resume ExitExportToExcel
End Function