Cannot export file using DoCmd.TransferSpreadsheet (1 Viewer)

[sharonng]

Hello, I have encountered a problem when trying to export an xls file in Access VBA.
My code is as follows:

Private Sub btn_exportnormal_Click()
    Dim outputFileName As String
    
    Dim nrange1, nrange2 As Integer
    
    If tb_Year <> "" And cmb_Quarter <> "" Then
    
        Select Case Me.FrameNormal
    
            Case 1
            DoCmd.Close acTable, "dbo_RP_STRATUM_002"
            DoCmd.Close acTable, "dbo_STRATUM"
            DoCmd.Close acQuery, "Combined_ST_BINDGID&ST_MPS_GROUP"
            On Error Resume Next
            DoCmd.RunSQL ("SELECT * INTO Normal FROM dbo_RP_STRATUM_002")

            Case 2
            If (crsnrange1 >= 0 And crsnrange1 <= 9999999) And (crsnrange2 >= 0 And crsnrange2 <= 9999999) Then
                DoCmd.Close acTable, "dbo_RP_STRATUM_002"
                DoCmd.Close acTable, "dbo_STRATUM"
                DoCmd.Close acQuery, "Combined_ST_BINDGID&ST_MPS_GROUP"
                On Error Resume Next
                DoCmd.RunSQL ("SELECT * INTO Normal FROM dbo_RP_STRATUM_002")

            Else: MsgBox "Please enter the CRSN range.", vbOKOnly
                Exit Sub
            End If
        End Select
        
        If cmb_Quarter.Value = "Q1" Then
            nquarter = "03"
            nquartert = "FIRST QUARTER"
        ElseIf cmb_Quarter.Value = "Q2" Then
            nquarter = "06"
            nquartert = "SECOND QUARTER"
        ElseIf cmb_Quarter.Value = "Q3" Then
            nquarter = "09"
            nquartert = "THIRD QUARTER"
        ElseIf cmb_Quarter.Value = "Q4" Then
            nquarter = "12"
            nquartert = "FOURTH QUARTER"
        End If
        
        
        DoCmd.OpenTable ("Normal")

        outputFileName = CurrentProject.Path & "\MailMerge_" & nquarter & tb_Year.Value & "_Normal_" & Format(Now(), "yyyymmdd hhnnss") & ".xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Normal", outputFileName, True
        Application.FollowHyperlink outputFileName
        MsgBox "Exported!", vbOKOnly
        
    Else: MsgBox "Please enter both the Year and Quarter.", vbOKOnly
    End If
End Sub

The program is able to show the message box "Exported!" but no file was created. Can somebody guide me? Much thanks.

 

[arnelgp]

comment out all "On Error Resume Next", then you should be notified
if there are errors.

 

[sharonng]

comment out all "On Error Resume Next", then you should be notified
if there are errors.

I have tried it and it showed a new error: error 7874 (Microsoft Access can't find the object). I edited DoCmd.OpenTable ("Normal") to DoCmd.OpenTable ("dbo_RPS_STRATUM_002") and this error popped up, but I am sure that "dbo_RPS_STRATUM_002" exists, just that it has been closed in the above DoCmd.Close acTable, "dbo_RP_STRATUM_002". Please advise.

 

[Minty]

I'm puzzled by a few things - why do you have those other tables open, and why are you opening the "Normal" table.

The table doesn't need to be open to export it, in fact I wouldn't ever open a table directly other than to do some manual checking?