Saving report as a PDF with custom file name, VBA

Question

Hello,

On a form I have two list boxes called LstBox_ChooseDivision and ListBox_Venue and an unbound text field called FileName.

I would like to create a button on the same form called "Save Agenda as PDF".

When I click this button I would like VBA to perform the following...

Save an Access report called "rpt_Meeting Agenda" as a pdf to a folder called "Meeting Agenda Archives" dynamically which is located in the same folder as the Access Database resides with a file name as such

[LstBox_ChooseDivision] [ListBox_Venue.column(1) -this a date and time field] [ListBox_Venue.column(2) - this is a text field of the meeting location] [ListBox_Venue.column(3) this is a text field of attendee names] Now() - today's date and time

Example

Arts, YYYY-MM-DD HH-MM, Small Conference Room, Sarah John, YYYY-MM-DD HH-MM.pdf

Once done a pop appears saying "Agenda has been saved to the Meeting Agenda Archives folder"

Could you help me with this?

Iram/mcp

Answer 1

The code behind a button would look like this:

  Dim strFileName      As String

   Dim strReport        As String

   strReport = "rpt_Meeting Agenda"

   strFileName = Me.LstBox_ChooseDivision & ", " & _

                 Format(Me.ListBox_Venue.Column(1), "YYYY-MM-DD HH-NN") & ", " & _

                 Me.ListBox_Venue.Column(2) & ", " & _

                 Me.ListBox_Venue.Column(3) & ", " & _

                 Format(Now(), "YYYY-MM-DD HH-NN") & ".pdf"

   strFileName = CurrentProject.Path & "\Meeting Agenda Archives" & strFileName

   DoCmd.OpenReport strReport, acViewPreview

   DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName, False

   DoCmd.Close acReport, strReport

So the above is "air" code, but should be very close to what you require.

Regards

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 2

Beautiful!

Arts, 2016-10-01 13-00, Small Conference Room, Iram, 2016-09-15 14-14.pdf

Thank you Albert!!! :)

Iram

Answer 3

How do I modify the below code to add the word "Meeting" before Me.LstBox_ChooseDivision

AND

Include the words "Pdf Save Date" right before Now()

Example

Arts, Meeting 2016-10-01_13-00, Small Conference Room, Iram, Pdf Save Date 2016-09-15_14-33

Private Sub btn_SaveFile_Click()

Dim strFileName      As String

   Dim strReport        As String

   strReport = "rpt_Report_AgendaFinal"

   strFileName = Me.LstBox_ChooseDivision & ", " & _

                 Format(Me.ListBox_Venue.Column(0), "YYYY-MM-DD_HH-NN") & ", " & _

                 Me.ListBox_Venue.Column(1) & ", " & _

                 Me.ListBox_Venue.Column(2) & ", " & _

                 Format(Now(), "YYYY-MM-DD_HH-NN") & ".pdf"

   strFileName = CurrentProject.Path & "\Meeting Agenda Archives" & strFileName

   DoCmd.OpenReport strReport, acViewPreview

   DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName, False

   DoCmd.Close acReport, strReport

Answer 4

Change this one line of code:

   strFileName = Me.LstBox_ChooseDivision & ", " & _

To

   strFileName = Me.LstBox_ChooseDivision & ", Meeting " & _

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 5

Great.

What about the Pdf Save Date part?

On another note I have the below module. At the end of the "Private Sub btn_SaveFile_Click()" code how would I activate this module to open the folder?

I tried this, and it opens the folder but it gives me an error

DoCmd.OpenModule OpenMeetingArchivesFolder, openPaTH()

Option Compare Database

Public Function openPaTH()

Dim strPath As String

strPath = CurrentProject.Path & "\Meeting Agenda Archives"

If Len(Dir(strPath)) > 0 Then Shell "explorer.exe " & strPath, vbNormalFocus

End Function

Iram