Object invalid or no longer set

[sk88]

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

[John_G]

Are you able to tell which line of code is causing the error? (Click "Debug" at the error message, and the line of code causing the error should be highlighted).

[sk88]

Are you able to tell which line of code is causing the error? (Click "Debug" at the error message, and the line of code causing the error should be highlighted).

I did not get any Debug option (see attached image). I also tried to use 'Debug --> step into' in the visual basic and it did not give me any error as well *confused*

[John_G]

It's because you have the On Error Go To statement.

Comment out that line, and run the code again - you should be able to see the debug then.

[sk88]

It's because you have the On Error Go To statement.

Comment out that line, and run the code again - you should be able to see the debug then.

Thanks again. I got an error on this:

Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:=conOBJECT_TO_EXPORT, _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")

also see attached image

[John_G]

The only thing I can think of at the moment is that "Export" is not the name of a table in your database.

If you comment out the first TransferToSpreadsheet command, do you still get the error when you run it?

Also, I have just noticed that both your TransferToSpreadsheet commands have the same destination -
FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls"


Surely that's not right?

[sk88]

The only thing I can think of at the moment is that "Export" is not the name of a table in your database.

If you comment out the first TransferToSpreadsheet command, do you still get the error when you run it?

Also, I have just noticed that both your TransferToSpreadsheet commands have the same destination -
FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls"


Surely that's not right?

Yes, you are right. Both my TransferToSpreadsheet commands have same destination because they are two different queries and I would like them to be exported in one excel file but different spreadsheet. The codes used to work. I am not sure what happened today that it stop working and giving me an error.

I tried to remove the line call docmd. I do get an option to export but nothing gets exported even when it says my file has been exported to the location selected. Nothing as in I do not see any Excel file in my folder.

[John_G]

I'm not familiar with Call, but try running it without "Call", i.e. just use docmd.transferspreadsheet .....

[sk88]

I'm not familiar with Call, but try running it without "Call", i.e. just use docmd.transferspreadsheet .....

Thanks again John_G.

I tried using docmd and it won't work

[John_G]

Remove the brackets from the parameter list:

DoCmd.TransferSpreadsheet TransferType:=acExport, _
TableName:=conOBJECT_TO_EXPORT, _
FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls"

[sk88]

Remove the brackets from the parameter list:

DoCmd.TransferSpreadsheet TransferType:=acExport, _
TableName:=conOBJECT_TO_EXPORT, _
FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls"

Thank you so much for your help. I am not sure why the codes seem to be working fine now.