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