Run time error 2501 outputto action canceled

Gerrit.B

Board Regular
Joined
Aug 10, 2004
Messages
234
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I use code below to create reports.
Now I get error 2501, after restarting my computer it is working again.
When I have this error I can change code below to output snp, or html and it works fine.

So this error only appears when exporting to pdf.

Does anybody has a working solution for this problem?

Code:
Option Compare Database
Option Explicit


Const sDefaultPath As String = "C:\Temp\"


Dim rs As DAO.Recordset
Dim str1SQL As String
Dim str2SQL As String
Dim strRptName As String
Dim rpt As Report
Dim strDBName As String
Dim strDBPath As String
Dim strFunctionName As String
Dim strModuleName As String
Dim strDescription As String
Dim Fldr As String
Dim strErrorNumber As Long


Public Function MyMkDir(sDefaultPath As String)


Dim iStart As Integer
Dim aDirs As Variant
Dim sCurDir As String
Dim I As Integer


If sDefaultPath <> "" Then
aDirs = Split(sDefaultPath, "\")
If Left(sDefaultPath, 2) = "\\" Then
iStart = 3


Else
iStart = 1
End If


sCurDir = Left(sDefaultPath, InStr(iStart, sDefaultPath, "\"))


For I = iStart To UBound(aDirs)
sCurDir = sCurDir & aDirs(I) & "\"
If Dir(sCurDir, vbDirectory) = vbNullString Then
MkDir sCurDir
End If
Next I
End If
End Function


Function ExportReportsPDF()


'DoCmd.Echo False 'suppress the screen updates = ON


  On Error GoTo errHandler


If Len(Dir(sDefaultPath, vbDirectory)) = 0 Then
MyMkDir sDefaultPath
End If


strDBName = Application.CurrentProject.Name
strDBPath = Application.CurrentProject.Path


strFunctionName = "ExportReportsPDF"


    str1SQL = "SELECT [MSysObjects]![Name] AS Report " & _
             "FROM MSysObjects " & _
             "WHERE (((MSysObjects.Name) Like ""exp*"") And ((MSysObjects.Type) = -32764))"
    Set rs = DBEngine(0)(0).OpenRecordset(str1SQL)


    Do While Not rs.EOF
        Debug.Print rs!Report
        strRptName = rs!Report
        
           
DoCmd.OpenReport strRptName, acViewDesign
Set rpt = Reports(strRptName)
rpt.Printer.PaperSize = acPRPSA4
DoCmd.Save
DoCmd.Close acReport, strRptName, acSaveNo
     
DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, sDefaultPath & Mid(rs!Report, 4, 55) & ".pdf", False, "", 0, acExportQualityPrint
 
        
ExportError:
        rs.MoveNext
    
    Loop


  Exit Function
 
errHandler:


  strDescription = Chr(34) & Err.Description & Chr(34)
  strErrorNumber = Err.Number
  
  Select Case strErrorNumber
  
    Case 29068
        Resume Next


    Case 3314, 2101, 2115    ' Can't save.
        Resume Next


    Case Else
   
    'str2SQL = "INSERT INTO tblErrorLog (ErrDate, ExportName, ErrFunction, ErrNumber, ErrDatabase, ErrPath, ErrDescription, ErrModule) VALUES(#" & Format(Now(), "yyyy-mm-dd hh:mm:ss") & "#, '" & strRptName _
    '& "', '" & strFunctionName & "', " & strErrorNumber & ", '" & strDBName & "' , '" & strDBPath & "' ," & strDescription & ", '" & VBE.ActiveCodePane.CodeModule & "');"
  
  'DoCmd.RunSQL str2SQL
  
 MsgBox "Error: ( " & Err.Number & " ) " & Err.Description, vbCritical
 
    End Select
  
  Resume ExportError
  
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
'DoCmd.Echo False 'suppress the screen updates = Off
  
End Function
 
Last edited:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,298
Office Version
  1. 365
Platform
  1. Windows

Gerrit.B

Board Regular
Joined
Aug 10, 2004
Messages
234
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Koen,

When I change the code to export snp instead of pdf it doesn't give an error.
So it only happens with pdf.
After restarting my computer the problem is also temporary solved.

Regards,

Gerrit
 

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,298
Office Version
  1. 365
Platform
  1. Windows
Hi Gerrit,
I did read your first post and am aware that the points I mentioned might not sound logical, but I've learned while programming that sometimes the solutions that do work do not make sense... So they could be worth a try. The basic difference between PDF and SNP is probably that the PDF export takes a bit longer, so the macro might be still busy if it passes by again? When exactly is the error happening? The first time in the loop? Only after a couple of exports?
Cheers,
Koen
 

Gerrit.B

Board Regular
Joined
Aug 10, 2004
Messages
234
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Koen,

The same code is running on Windows Server 2008 without any problem.
I exports daily 700 pdf reports

When I get this error and close the database and restart Access the problem isn't solved
I have to restart my PC to solve this problem.

So the loop isn't the problem.

Even code below gives the same error, just for send one pdf report.

Code:
DoCmd.SendObject acReport, [Report], "PDFFormat(*.pdf)", [email], nz([emailCC], ""), "", "Ordernumber " & [OrderNumber], Left([Contact], InStr(1, [Contact], " ") - 1), True, ""

Regards,

Gerrit
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,992
Office Version
  1. 365
Platform
  1. Windows
The error occurs with one particular report but not some, or it fails on the first attempt?
Suggest you debug.print
- the value of [Report] coming from the record set
- sDefaultPath
- Mid(rs!Report, 4, 55) & ".pdf"
and examine these values for something about them that might not be compatible with a pdf version. You might have an offending character in a report name that the Adobe converter doesn't like, or spaces where they cause problems (in an object name or network path).
I would also advise to ALWAYS MoveFirst when you've created a recordset, before the Do While Not rs.EOF
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,119,174
Messages
5,576,534
Members
412,730
Latest member
Thundereagle
Top