This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hello,
I have the below vba and I need help fixing it because not sure what I am doing.
Below is a written out and a real example of how I need the .pdf file name created. Could you please help me with this. It would be much appreciated.
[Todays Date and Time] EPR Perf **** Completed On Time, Between [BeginDate] and [EndDate]
2017-03-03 17-00 EPR Perf **** Completed On Time, Between 01-01-2017 and 03-03-2017
Option Compare Database
Private Sub ExportAsPDF_EPRBySupervisorReport_Click()
If IsNull(Me.BeginDate) Then
MsgBox "Please type in a Begin Date"
Me.BeginDate.SetFocus
Exit Sub
ElseIf IsNull(Me.EndDate) Then
MsgBox "Please enter an End Date"
Me.EndDate.SetFocus
Exit Sub
End If
Dim strFileName As String
Dim strReport As String
strReport = "rpt_CSDPerfMeas-EPRsCompletedOnTime"
strFileName = "EPR Perf **** Completed On Time " & " Between " & _
Format(Me.BeginDate, "YYYY-MM-DD@HH-NN") & " and " & _
Format(Me.EndDate, "YYYY-MM-DD@HH-NN") & " and " & _PdfSaveDate " & _
Format(Now(), "YYYY-MM-DD@HH-NN") & ".pdf"
strFileName = CurrentProject.Path & "\EXPORTS of Employee Log Database\Excel Export" & strFileName
DoCmd.OpenReport strReport, acViewPreview
DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName, False
' And now set the filename of the .xls file
strFileName = Me.LstBox_ChooseDivision & " Agenda " & _
Me.AgendaMeetingLocation & ", " & _
Me.AgendaAttendees & ", ExcelSaveDate " & _
Format(Now(), "YYYY-MM-DD@HH-NN") & ".xls"
strFileName = CurrentProject.Path & "\EXPORTS of Employee Log Database\Excel Export" & strFileName
DoCmd.OutputTo acOutputReport, strReport, acFormatXLS, strFileName, False
DoCmd.Close acReport, strReport
Call OpenFolderPaTH
End Sub
A family of Microsoft relational database management systems designed for ease of use.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
You have an extra underscore prior to the PdfSaveDate. In that same spot there appears to be an ampersand and quotation that may be out of order or extra.
Answer accepted by question author
Hmm, I'm a little confused do you want one report or two? For the .PDF try *Oh, no asterisks allowed in File Names, so I removed them.)...
Dim strFileName As String
Dim strReport As String
'First let's open the report
strReport = "rpt_CSDPerfMeas-EPRsCompletedOnTime"
DoCmd.OpenReport strReport, acViewPreview
'Now let's save the open report to .PDF
strFileName = "EPR Perf Completed On Time " & " Between " & _
Format(Me.BeginDate, "YYYY-MM-DD@HH-NN") & " and " & _
Format(Me.EndDate, "YYYY-MM-DD@HH-NN") & " and " & _PdfSaveDate " & _
Format(Now(), "YYYY-MM-DD@HH-NN") & ".pdf"
strFileName = CurrentProject.Path & "\EXPORTS of Employee Log Database\Excel Export" & strFileName
DoCmd.SelectObject acReport, strReport
DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName, True
DoCmd.Close acReport, strReport
Ok I copied in your recommendation and now below is the entire VBA screen from top to bottom, but I don't know why the bold section below continues to be red in the VBA screen. When I debug the below I get an error that says "Compile Error: Duplicate declaration in current scope" in the Italic bold below.
Option Compare Database
Private Sub ExportAsPDF_EPRBySupervisorReport_Click()
If IsNull(Me.BeginDate) Then
MsgBox "Please type in a Begin Date"
Me.BeginDate.SetFocus
Exit Sub
ElseIf IsNull(Me.EndDate) Then
MsgBox "Please enter an End Date"
Me.EndDate.SetFocus
Exit Sub
End If
Dim strFileName As String
Dim strFileName As String
Dim strReport As String
'First let's open the report
strReport = "rpt_CSDPerfMeas-EPRsCompletedOnTime"
DoCmd.OpenReport strReport, acViewPreview
'Now let's save the open report to .PDF
strFileName = "EPR Perf Completed On Time " & " Between " & _
Format(Me.BeginDate, "YYYY-MM-DD@HH-NN") & " and " & _
Format(Me.EndDate, "YYYY-MM-DD@HH-NN") & " and " & _PdfSaveDate " & _
Format(Now(), "YYYY-MM-DD@HH-NN") & ".pdf"
strFileName = CurrentProject.Path & "\EXPORTS of Employee Log Database\Excel Export" & strFileName
DoCmd.SelectObject acReport, strReport
DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName, True
DoCmd.Close acReport, strReport
Call OpenFolderPaTH
End Sub
No problem... I'll be around!
Hello Gina,
I must have added the asterisks by mistake.
the VBA has two outputs, both PDF and Excel, but I figure if I can help with the PDF part I can wing the Excel part.
I'll try your suggestion on Monday and get back to you.
Iram