Results 1 to 7 of 7
  1. #1
    Xarkath is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Location
    Millcreek WA
    Posts
    92

    TransferSpreadsheet issue

    Hello,


    I looked this up online but all the stuff I found on the error referred to code strings that are not apart of the code listed here and I was unable to relate any of the answers to the code I am using.

    I have been using the following code in 2 Access databases for months now without any problems. Just used it yesterday afternoon even. This is on front end databases with each user having thier own copies. Today it is throwing an error on both database and all users. I have ran through the whole process manually and it works at each stage so I am fairly certain that has something to do with the code but since there hasn't been any changes to it I am not sure why.

    The code calculates billing adjustments then exports the final 2 queries into a single Excel document with the 2nd query being applied to the same sheet just under the results of the first query.

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

    When I use debug it highlights this line: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SummaryBEACrossTab_qry", pth, True, "Admin_Adjustments"

    If anyone has some ideas as to why this would suddenly start throwing errors and/or how I might fix it I would appreciate hearing from you.

    Thanks

    Code:
     Private Sub Command4_Click()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "AdjustStep6_qry"
    DoCmd.OpenQuery "AdjustStep3_qry"
    DoCmd.OpenQuery "AdjustStep3_VBB_qry"
    DoCmd.OpenQuery "AdjustStep5_qry"
    DoCmd.OpenQuery "AdjustStep7_qry"
    DoCmd.OpenQuery "AdjustStep8_qry"
    Dim rstName As Recordset
    Dim objApp As Object, objMyWorkbook As Object, objMySheet As Object, objMyRange As Object
    Dim pth As String
    Dim grp As String
    Dim msgbR As Integer
    grp = Me.GrpSelect
    pth = ("R:\") & grp & ("\hma\billing_int\AdminAdjustmentList.xlsx")
     
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SummaryBEACrossTab_qry", pth, True, "Admin_Adjustments"
    Set rstName = CurrentDb.OpenRecordset("BEATypeList_qry")
    
    Set objApp = CreateObject("Excel.Application")
    Set objMyWorkbook = objApp.Workbooks.Open(pth)
    Set objMySheet = objMyWorkbook.Worksheets("Admin_Adjustments")
    Set objMyRange = objMySheet.Cells(objApp.ActiveSheet.UsedRange.Rows.Count + 2, 1)
    With objMyRange
     rstName.MoveFirst 'Rewind to the first record
     .Clear
     .CopyFromRecordset rstName
    End With
        objMyWorkbook.Save
        objApp.Quit
        
        Set ojbMyWorkbook = Nothing
        Set objApp = Nothing
        
         msgbR = MsgBox("The report has been exported to: " & pth & ".  Do you wish to open the report?", vbYesNo)
      If msgbR = vbYes Then
      Application.FollowHyperlink pth, , True
        End If
    DoCmd.SetWarnings True
    End Sub

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    What does this do?
    Code:
    DoCmd.OpenQuery "AdjustStep6_qry"
    DoCmd.OpenQuery "AdjustStep3_qry"
    DoCmd.OpenQuery "AdjustStep3_VBB_qry"
    DoCmd.OpenQuery "AdjustStep5_qry"
    DoCmd.OpenQuery "AdjustStep7_qry"
    DoCmd.OpenQuery "AdjustStep8_qry"
    If these are append queries, I would do
    currentdb.execute "AdjustStep3_qry", dbfailonerror

    for each append.

    The issue you are having seems to be with the fact that the Application object is busy with the queries. Maybe even place the append or update stuff in a separate procedure and call that procedure before the Transferspreadsheet.

  3. #3
    Xarkath is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Location
    Millcreek WA
    Posts
    92
    Quote Originally Posted by ItsMe View Post
    What does this do?
    Code:
    DoCmd.OpenQuery "AdjustStep6_qry"
    DoCmd.OpenQuery "AdjustStep3_qry"
    DoCmd.OpenQuery "AdjustStep3_VBB_qry"
    DoCmd.OpenQuery "AdjustStep5_qry"
    DoCmd.OpenQuery "AdjustStep7_qry"
    DoCmd.OpenQuery "AdjustStep8_qry"
    If these are append queries, I would do
    currentdb.execute "AdjustStep3_qry", dbfailonerror

    for each append.

    The issue you are having seems to be with the fact that the Application object is busy with the queries. Maybe even place the append or update stuff in a separate procedure and call that procedure before the Transferspreadsheet.
    The first 1 clears out old data, the following 2 appends new data and the final 3 makes updates to the data. "SummaryBEACrossTab_qry" followed by "BEATypeList_qry" uses the resulting data to calculate the adjustments and are exported to Excel.

    Everything is working until it gets to the point of the export.

    Thanks

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    OK, so the issue persists? If you use the dbfailonerror argument of the Execute method, you might be able to slow down your code so the Docmd object is available. I am going to guess the only issue is that you need to slow down your code. I am not positive the code will wait for the result of the Execute. You might have to put in a timer.

    Some might argue DoEvents is the solution, however, I do not know if I agree with that.

    Another thing that might help would be to create an object that represents the currentdb and then set it to = nothing before reassigning it.

    Code:
    dim db as current dao.database
    set db = currentdb
    
    db.execute "MyQuery", dbfailonerror
    set db = nothing
    set db = currentdb
    db.execute "MyOtherQuery", dbfailonerror
    set db = nothing
    
    ...
    Last edited by ItsMe; 12-03-2015 at 01:33 PM. Reason: corrected: dim db as current db

  5. #5
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    perhaps your it have reset the mapping?

    pth = ("R:\") & grp & ("\hma\billing_int\AdminAdjustmentList.xlsx")

  6. #6
    Xarkath is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Location
    Millcreek WA
    Posts
    92
    Thanks for the help, I will keep the advice for future production. However it turns out that this was not a problem with the code. A file of the same name was already in the folder. It was corrupt and for some reason did not display right away but once found and removed the code stopped throwing the error. What I need to do is write an error handler to deal with existing files.

    Thanks again. I learned something new or at least had my eyes opened to alternatives so I will count this as a positive interaction.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Glad you were able to find the issue so quickly and thanks for posting the solution.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Transferspreadsheet Method
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 11
    Last Post: 06-30-2011, 11:40 AM
  2. Transferspreadsheet Help Please
    By graviz in forum Programming
    Replies: 0
    Last Post: 11-30-2009, 02:56 PM
  3. Can't get TransferSpreadsheet to work
    By Valeda in forum Import/Export Data
    Replies: 0
    Last Post: 07-19-2007, 08:04 AM
  4. transferSpreadsheet
    By rabbit in forum Import/Export Data
    Replies: 1
    Last Post: 06-09-2006, 04:01 PM
  5. transferspreadsheet function
    By jeffj in forum Import/Export Data
    Replies: 3
    Last Post: 03-13-2006, 11:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums