Object invalid or no longer set

[beginner_access]

I have been using this export function for the longest time and today it is giving me an error:

Object invalid or no longer set.

Export code:

On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "EXPORT"

Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)

With dlgOpen
  .ButtonName = "Export To"
  .InitialView = msoFileDialogViewLargeIcons
  .InitialFileName = CurrentProject.Path
     If .Show = -1 Then
       'Allow for Root Directory selection: C:\, D:\, etc.
       strExportPath = Replace(.SelectedItems(1) & "\", "\\", "\")
 Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:=conOBJECT_TO_EXPORT, _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
                                                               
  Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="EXPORT_CapacityBuilding_DATA", _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
    


       MsgBox "[" & conOBJECT_TO_EXPORT & "] has been Exported to " & strExportPath & _
               conOBJECT_TO_EXPORT & ".xls", vbInformation, "Export Complete"
     End If
End With

'Set the Object Variable to Nothing.
Set dlgOpen = Nothing

DoCmd.Close
Exit_cmdTest_Click:
  Exit Sub

Err_cmdTest_Click:
  MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
    Resume Exit_cmdTest_Click

any thoughts?

thanks in advance

 

[pbaldy]

On what line (temporarily comment out the "On Error..." line)? Offhand, you don't "Call" the DoCmd lines, so delete that.

 

[beginner_access]

On what line (temporarily comment out the "On Error..." line)? Offhand, you don't "Call" the DoCmd lines, so delete that.

Thank pbaldy. I tried what you suggested and nothing gets exported even after it says my file has been exported to the folder. When I go to my folder, I do not see any Excel file.

 

[pbaldy]

Can you post the db here?

 

[beginner_access]

Thank you so much everyone! Not sure what happened. The code seems to be working fine now.

 

[pbaldy]

Glad you got it working.