using VBA to create the path and table name for pdf output files

[DBinPhilly]

I have a procedure that creates about 10 printed reports. The reports are for reference only and we put them in a directory created for that date's output in pdf format. Since every report has to be pathed and named, it is convenient for the procedure to create the path (if it's not already there) and then generate the name. I use a program called PDF995 to achieve this. Actually PDF995 puts a report in its own output directory. One can then grab it, rename it, and copy it to wherever you want, programmatically.

I wondered if anyone knew an alternative way to do this using VBA. I want the VBA programming to tell the system where to put each pdf-formatted report. Just askin'

[Bob Phillips]

Use the built-in PDF facility

Const Filepath = "C:\Users\bob\Documents\"

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                    Filename:=Filepath & "myFile.pdf"

[DBinPhilly]

Us the built-in PDF facility

Const Filepath = "C:\Users\bob\Documents\"

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                    Filename:=Filepath & "myFile.pdf"

Thank you. Just what I needed.

[arnelgp]

i thought this thread is about MS Access?
if it is, you need FileDialog so user can pick (or create new folder).

on VBA, you need to add Reference to Microsoft Office X.XX Object Library:

Public Function DirPicker(Optional ByVal strWindowTitle As String = "Select the folder where Backend is located") As String
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    fd.Title = strWindowTitle
    If fd.Show = -1 Then
        DirPicker = fd.SelectedItems(1)
    Else
        DirPicker = vbNullString
    End If
    Set fd = Nothing
End Function