Solved

How do I export an MS Access Report to a pdf format via VBA to a slected path

Posted on 2014-09-15
4
6,408 Views
Last Modified: 2014-09-28
I have a few reports I want to export to pdf, but only after being prompted to select the path.  How would I in the below example be prompted each time I export a report to select the path/file for MyPath & Myfilename

DoCmd.OutputTo acOutputReport, "VolunteerRoster", acFormatPDF, Mypath & Myfilename, False, , , acExportQualityPrint...

Would I need to use the DIR command or some form of the Windows scripting file ?
0
Comment
Question by:upobDaPlaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40324988
I use a function to retrieve the path name:
Public Function GetPath(Optional Message As Variant = Null, Optional ByRef Cancel As Boolean = False) As String

    'Uses the FileDialog object of the Application to select a folder
    With Application.FileDialog(4) 'msoFileDialogFolderPicker
        .AllowMultiSelect = False
        .Title = Nz(Message, "Select a folder")
        .Show

        If .SelectedItems.Count > 0 Then
            GetPath = .SelectedItems(1)
            Cancel = False
        Else
            Cancel = True
        End If

    End With

End Function

Open in new window

Then you can do something like the following in your code:
Dim Cancel as boolean
Dim strMsg as string

strMsg = "Select the folder where you want the file to be saved
myPath = GetPath(strMsg, Cancel)
if Cancel = false then 
    DoCmd.OutputTo acOutputReport, "VolunteerRoster", acFormatPDF, Mypath & Myfilename, False, , , acExportQualityPrint...
End IF

Open in new window

0
 

Author Closing Comment

by:upobDaPlaya
ID: 40335915
Thanks this works ....
0
 

Author Comment

by:upobDaPlaya
ID: 40348089
Actually I do have 1 issue..where/how do I reference myfilename
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40348451
Not sure what you mean?  In the doCmd.OutputTo line, you need to concatenate the path and file name.  That could be something like:

MyPath & "YourFileName.pdf"

Or you could create a string (MyFileName) and assign that a value and use the syntax that I used in the code above.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question