Object invalid or no longer set

[Gismo]

Hi all,

I am getting an object invalid or no longer set on the below code
I am no sure why as this has been working fine for months

Please could you lead me in the right direction to trouble shoot

The query seems to be working correctly

Private Sub ExcelNotIssued_Click()
On Error GoTo ExcelNotIssued_Click_Err

Dim StrFileName As String
Dim StrQryName As String
Dim StrSaveFile As String

StrFileName = strGetFileFolderName("Spares for Orders (Received, Not Issued)" & " - Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO], 2, "Excel")
'Debug.Print StrFileName
StrQryName = "Spares for Orders - Received"

If Len(StrFileName & "") < 1 Then
StrFileName = Application.CurrentProject.Path & "\Spares for Orders (Received, Not Issued)" & Format(Date, "yyyymmdd") & ".xls"
End If

'Debug.Print "My File is:" & StrFileName
DoCmd.TransferSpreadsheet acExport, 10, StrQryName, StrFileName, True, , True


ExcelNotIssued_Click_Exit:

Exit Sub

ExcelNotIssued_Click_Err:
MsgBox Error$
Resume ExcelNotIssued_Click_Exit

 

[CJ_London]

What line is generating the error?

 

[Gismo]

What line is generating the error?

I am not getting an error or any specific line, just the object invalid error when executing.
Error happens after I have selected the location to save the file.
So not sure where anything went wrong as tis was working well. No changes where made to the code
All I amended was setting I disabled name auto corrects and disabled show animations

 

[CJ_London]

set a breakpoint on the first line of executable code then step through a line at a time to identify the line.

 

[Gasman]

I am not getting an error or any specific line, just the object invalid error when executing.
Error happens after I have selected the location to save the file.
So not sure where anything went wrong as tis was working well. No changes where made to the code
All I amended was setting I disabled name auto corrects and disabled show animations

So how do you know it is that code?

What is strGetFileFolderName() meant to do?
Uncomment your debugs?

 

[Gismo]

So how do you know it is that code?

What is strGetFileFolderName() meant to do?
Uncomment your debugs?

strGetFileFolderName opens the file save as window to select the location

Debug.Print StrFileName gives the desired output

 

[Gasman]

So walk through the code and report back on what line raises the error?

 

[cheekybuddha]

Debug.Print StrFileName gives the desired output

Are you sure?

Might you actually need the file extension too ('.pdf' or '.xlsx')?

Looks like you have Windows Explorer still set to hide file extensions (an idiotic move by MS!)

 

[Gasman]

Well certainly your code is not picking up Excel files only, as you have All Files as Type?

 

[Gismo]

The code works when I run the same copy of the front end on our remote desktop directly from the server though

 

[Gismo]

Are you sure?

Might you actually need the file extension too ('.pdf' or '.xlsx')?

Looks like you have Windows Explorer still set to hide file extensions (an idiotic move by MS!)

it is is an extract to excel

If Len(StrFileName & "") < 1 Then
StrFileName = Application.CurrentProject.Path & "\Spares for Orders (Received, Not Issued)" & Format(Date, "yyyymmdd") & ".xls"
End If

Public Function strGetFileFolderName(Optional strInitialDir As String = "", Optional lngType As Long = 4, Optional strPattern As String = "All Files,*.*") As String
'StrInitialDir = where the filedialog starts browsing
'lngType e.g. 'msoFileDialogFilePicker = 3, msoFileDialogFolderPicker =4, msoFileDialogOpen=1,msoFileDialogSaveAs=2

Dim fDialog As Object
Dim vFile As Variant, varEntry As Variant


strGetFileFolderName = ""

Set fDialog = Application.FileDialog(lngType)

With fDialog
.Title = "Browse for "
Select Case lngType
Case 1 'msoFileDialogOpen
.Title = .Title & "File to open"
Case 2 'msoFileDialogSaveAs
.Title = .Title & "File to SaveAs"
Case 3 'msoFileDialogFilePicker
.Title = .Title & "File"
Case 4 'msoFileDialogFolderPicker
.Title = .Title & "Folder"
End Select

Select Case strPattern
Case "Excel"
strPattern = "MS Excel,*.XLSX; *.XLSM; *.XLS"
Case "Access"
strPattern = "MS Access,*.ACCDB"
Case "PPT"
strPattern = "MS Powerpoint,*.PPTX; *.PPTM"
End Select


If lngType <> 2 And lngType <> 4 Then
'Reset then add filter patterns separated by tildes (~) where
' multiple extensions are separated by semi-colons ( and the
' description is separated from them by a comma (,).
' Example strPattern :
' "MS Access,*.ACCDB; *.MDB~MS Excel,*.XLSX; *.XLSM; *.XLS"

'.filters.Clear
For Each varEntry In Split(strPattern, "~")
'.filters.Add Split(varEntry, ",")(0), Split(varEntry, ",")(1)

Next varEntry
End If
'Set some default settings
.InitialFileName = strInitialDir
.AllowMultiSelect = False
.InitialView = 2 'msoFileDialogViewDetails
'Only return a value from the FileDialog if not cancelled.
If .Show Then strGetFileFolderName = .SelectedItems(1)

End With

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
Resume ExitHere
End Function

 

[Gismo]

Well certainly your code is not picking up Excel files only, as you have All Files as Type?

I have the file extention set

If Len(StrFileName & "") < 1 Then
StrFileName = Application.CurrentProject.Path & "\Spares for Orders (Received, Not Issued)" & Format(Date, "yyyymmdd") & ".xls"
End If

 

[Gasman]

That is only if nothing comes back from your function?

 

[cheekybuddha]

I have the file extention set

Your screenshot of output in the Immediate Window in Post #6 doesn't seem to agree.

 

[NauticalGent]

I was having a similar issue with the NoseFilePicker as well. This thread may be helpful. Also See this article for some more in-depth information...

 

[The_Doc_Man]

After looking at the code for a while, I am with the others who are suggesting that you set a breakpoint and single-step through this code to find the offending line. I don't see anything obvious in that code to cause that error, which at least SUGGESTS to me that the error isn't there... it is in something else you called.

In the code you initially showed us, everything you DIM'd locally is a string, for which that error would make no sense. A locally defined string is ALWAYS "valid" and "set" (though it might be empty at times) so the error "object invalid or no longer set" doesn't seem to be possible to apply to them. The only other possibility is that an argument to one of your calls is wrong.

I have a question, though. In the past, when I attempted to use TRUE as though it were a constant, the context in which I used it made a very big difference. I recall having to actually declare a couple of global values for TRUE and FALSE because the correct constants were actually vbTRUE and vbFALSE. (But that was several versions ago...) Your TransferText activation uses just plain-old TRUE, not vbTRUE. I'm wondering if you are getting caught on an undefined constant. Do you have Option Explicit set for that module? If not, "TRUE" as a constant might not be TRUE (-1).

 

[Gismo]

My error is in

DoCmd.TransferSpreadsheet acExport, 10, StrQryName, StrFileName, True, , True

Which does not make sense as when I open the same file from remote desktop, there is no error

 

[Gismo]

So I found where the error is
When I select location downloads on the local pc the error occurs
When any other location is selected, there is no error
Very strange

There is no limit on the size of the download folder as per IT, but I just cleared my folder and it seemed to work

 

[The_Doc_Man]

When I select location downloads on the local pc the error occurs

For quite a long time now, the C:\ folder and several of its child folders have been write-restricted to local users, but coming across a network link (which includes RD), you can have different permissions for the same location. Consider the places you would save your file. Do this in the same way your program would... i.e. local connection or remote connection. One at a time, navigate to each folder and right-click. From the pop-up menu, select Properties >> Security ... then click the Advanced button. You can check the Effective Permissions for the folder to see if you have Write permission for each place.