Object invalid or No longer set (1 Viewer)

Gismo

Registered User.
Local time
Tomorrow, 01:32
Joined
Jun 12, 2017
Messages
1,298
Hi All,

I am getting an Object invalid or no longer set error in below code

Could you please advise

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
'DoCmd.OutputTo acOutputQuery, "Spares for Orders - Received", "Excel97-Excel2003Workbook(*.xls)", "Spares for Orders (Received, Not Issued)" & " - Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO] & ".xls", True, "", , acExportQualityPrint

ExcelNotIssued_Click_Exit:

Exit Sub

ExcelNotIssued_Click_Err:
MsgBox Error$
Resume ExcelNotIssued_Click_Exit

End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:32
Joined
May 7, 2009
Messages
19,169
try using .xlsx as extension of output.

acSpreadsheetTypeExcel12Xml = 10

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

MarkK

bit cruncher
Local time
Today, 16:32
Joined
Mar 17, 2004
Messages
8,178
Is that a function call to strGetFileFolderName()? How do we know the error is not occurring in that subroutine?
 

bastanu

AWF VIP
Local time
Today, 16:32
Joined
Apr 13, 2010
Messages
1,401
You export using acSpreadsheetTypeExcel12Xml (=10) yet you set the extension for file name as xls...

StrFileName = Application.CurrentProject.Path & "\Spares for Orders (Received, Not Issued)" & Format(Date, "yyyymmdd") & ".xlsx
 

Isaac

Lifelong Learner
Local time
Today, 16:32
Joined
Mar 14, 2017
Messages
8,738
Hi All,

I am getting an Object invalid or no longer set error in below code

Could you please advise
Any time you make a post like this, you need to say on which line the error occurs, specifically.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:32
Joined
Feb 28, 2001
Messages
27,001
Gasman's question is highly relevant. If you can put a breakpoint on

StrFileName = strGetFileFolderName("Spares for Orders (Received, Not Issued)" & " - Registration -" & ...

and then single-step, checking the results at each step, ONE of those assignments is going to return a blank. You have no objects on the left-hand side of any assignment statement, so everything will depend on your right-hand-side arguments or the arguments of your DoCmd and other imperative actions. One of them is not right. Single stepping will help you find which one.
 

Users who are viewing this thread

Top Bottom