Error Object Invalid or No Longer Set while executing DoCmd.TransferSpreadsheet

[sjlevine34]

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.

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

[ranman256]

i dont see any use for:
Set dbCurrent = CurrentDb

just remove it

docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12Xml,strQuery, strPath, true, sSheetName

dim XL As Excel.Application
Set XL = CreateObject("excel.application")
With XL
    .Visible = False   
    .Workbooks.Open strPath

    'work data here
end with

AND Excel object must in loaded in the vbe,tools,references

[sjlevine34]

The set dbCurrent was left over from when I was trying to see if adding it would fix the problem. I saw that I had inadvertently left it in my posted code.

Microsoft Excel 16.0 Object Library was already referenced at time the error first occurred.

Anyway, after turning computer off last night and on this morning, error seems to be gone with no change in code. That seems to be in line with what others have reported when they have encountered this error on docmd.transferspreadsheet.

I should note that the error did not occur until I added the following code to freeze panes.

'Set pane freeze.Range("G2").Select
 excel.ActiveWindow.FreezePanes = True

On subsequent reading, I found out that I probably should not have invoked the excel object directly. Anyway, immediate inactivation did not fix the problem once it occurred. Nor did transferring the database to another computer fix it. Nor recreating the database and importing all objects from the old database. However, again, the error seems to have disappeared with no change in the affected code.

[ssanfu]

You might also check out Ken Snell's site at http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm


I don't understand opening the Excel WB hidden, then closing the WB without doing anything with the WB. Was there other code that was deleted?

I export queries to Excel, but I use the "DoCmd.OutputTo" command. But I export to spreadsheet type acFormatXLS (.xls). 10+ years with no problems......

[sjlevine34]

No coding changes between time it didn’t work and time it did.

[CJ_London]

have you checked your data to ensure it is valid? (strQuery and strPath) and that the query works and the strPath specifies .xlsx which is required for acSpreadsheetTypeExcel12Xml and a valid path that the user has read/write rights to.

other things to clarify - is the excel file an existing file or a new one? If the former, could it have been opened by another user?

Incidentally, hasfieldnames is irrelevant for exports - headers will be exported regardless

[sjlevine34]

The error is back. In fact, I may have executed the routine in the wrong database when I reported it was gone. The database I may have used builds the exported file by other means than using docmd.transferspreadsheet.

I can bring up the query that I am trying to export without problem. I can bring up the queries it uses (which includes one union query) without problem. I can manually export the query without problem. So I don't think it is data in the tables from where the query-to-be-exported draws its data (they are linked tables to excel spreadsheets which are exported from SSRS reports that are not mine). So I don't think it is data validity. I wrote the Access application in question for my own use, and am the only one using it. It and the spreadsheets it uses exist only on my laptop, so opening by another user as a cause can be dismissed.

As I alluded to earlier, since I encountered this problem, I have used another approach to creating the spreadsheet from the data and it works beautifully. My other apps that use docmd.transferspreadsheet continue to work without problems, so I am in no urgent hurry to solve this problem. However, I really appreciate the help. Thanks.

[CJ_London]

well I'm out of ideas - did you check that your code has valid values for strQuery and strPath?

[sjlevine34]

Yes I did check, using the debugger (it and I go back a long way), the values for strQuery and strPath and they are valid and what I want.

Again, I am in no hurry to figure this one out, as I took another approach to exporting the data from the query, and the end result is that I have totally automated turning an exported-to-Excel SSRS report into an Excel Workbook containing a correctly formatted worksheet with additional data summary cells and named ranges, ready to use in another Access application that tremendously assists me in completing the tasks I need to perform. I am thus one step closer to automating the whole process so that a second person doing my tasks can also use the applications with ease. In this way, we both end up doing things the same way.