Object invalid or No longer set (1 Viewer)

[Gismo]

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

 

[Gasman]

Have you walked through the code?

 

[arnelgp]

try using .xlsx as extension of output.

acSpreadsheetTypeExcel12Xml = 10

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

 

[MarkK]

Is that a function call to strGetFileFolderName()? How do we know the error is not occurring in that subroutine?

 

[bastanu]

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]

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]

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.