Error with cmd.transferspreadsheet

[CivilUser]

I have 10 queries that I would like to export to an excel Spreadsheet called "Proposal_Report.xlsm" (with macros). Each query contains 3 columns of data: "Project Name", date, and amount (with data formats respective to their names).

My code (found below) executes 2 basic functions. The first is to see if the excel spreadsheet is open. There is a separate function for that which returns a boolean. Then the code starts transferring the files using Docmd.transferspreadsheet.

The code worked very smoothly just two days ago. It basically just stopped working. I get the following error on the first transferspreadsheet line:

Run-time error '3420':
Object invalid or no longer set.

Any help will be greatly appreciated. Thanks!

Private Sub Create_Report_Click()
    Dim FileOpen As Boolean

    FileOpen = isFileOpen("k:\Database\Proposal_Report.xlsm")
    
    If FileOpen = True Then
        MsgBox ("Please close the Excel Document titled 'Proposal_Report.xlsm' and try again.")
        Exit Sub
    End If
    
    DoCmd.TransferSpreadsheet acExport, 10, "Leads", "k:\Database\Proposal_Report.xlsm"
    DoCmd.TransferSpreadsheet acExport, 10, "Opportunities", "k:\Database\Proposal_Report.xlsm"
    DoCmd.TransferSpreadsheet acExport, 10, "Proposals", "k:\Database\Proposal_Report.xlsm"
    DoCmd.TransferSpreadsheet acExport, 10, "Shortlist", "k:\Database\Proposal_Report.xlsm"
    DoCmd.TransferSpreadsheet acExport, 10, "WINS", "k:\Database\Proposal_Report.xlsm"
    DoCmd.TransferSpreadsheet acExport, 10, "Inactive", "k:\Database\Proposal_Report.xlsm"
    
    DoCmd.TransferSpreadsheet acExport, 10, "LeadsIDIQ", "k:\Database\Proposal_Report.xlsm"
    DoCmd.TransferSpreadsheet acExport, 10, "OpportunitiesIDIQ", "k:\Database\Proposal_Report.xlsm"
    DoCmd.TransferSpreadsheet acExport, 10, "ProposalsIDIQ", "k:\Database\Proposal_Report.xlsm"
    DoCmd.TransferSpreadsheet acExport, 10, "ShortlistIDIQ", "k:\Database\Proposal_Report.xlsm"
    DoCmd.TransferSpreadsheet acExport, 10, "WINSIDIQ", "k:\Database\Proposal_Report.xlsm"
    DoCmd.TransferSpreadsheet acExport, 10, "InactiveIDIQ", "k:\Database\Proposal_Report.xlsm"
    
    

    Call OpenSpecific_xlFile

End Sub

Public Function isFileOpen(ByVal strDocFile As String) As Boolean
On Error GoTo ErrHandler
Dim intFree As Integer
   intFree = FreeFile()
   Open strDocFile For Input Lock Read As intFree
   isFileOpen = False
ExitHere:
   On Error Resume Next
   Close #intFree
   Exit Function
ErrHandler:
   isFileOpen = True
   Resume ExitHere
End Function

 

[CivilUser]

Is there at least an alternative way to transfer a table to Excel?

 

[Rx_]

A while back, I was maintaining someone's code after updating it from Access 2000 to 2007 and this link helped.
http://www.access-programmers.co.uk/forums/showthread.php?t=167250


One of the great things about this site is the object programming examples.
This thread is not one of the better ones (I can say that because it is my code), but it might give you some ideas of alternatives to search for. It is a little more coding, but provides more options all around. http://www.access-programmers.co.uk/forums/showthread.php?t=199386&highlight=objxl

 

[CivilUser]

Thanks for the reply. The links were helpful. I might end up completely changing my method here because the code works fine now and I don't want a piece of code that works only half the time.

I did replace the Excel Spreadsheet I was exporting to to an older backup version and it works fine now. Even if the spreadsheet was the source of the problem I don't know any specifics about the problem. Any Ideas?

 

[Rx_]

Sorry my last attempt to post while you were still around failed.
Please read through this post:
http://www.access-programmers.co.uk/forums/showthread.php?t=191184
This might just apply to your situation.