Saving Access Report

Question

I have the following which opens the SaveAs Dialog box, however it's not actually saving the file when I click save.

Dim SaveBox As Object
Set SaveBox = Application.FileDialog(msoFileDialogSaveAs)

With SaveBox
.AllowMultiSelect = False
.InitialFileName = "WeeklyLog " & Format(Now, "yyyy_mm_dd")
SaveBox.Show
End With

Are you trying to save as a text file? Excel spreadsheet? Word doc? — Johnny Bones, Jan 09 '14 at 18:04
As pdf. I asked it below before refreshing the page and seeing your question. — mrbungle, Jan 09 '14 at 18:07

score 2 · Accepted Answer · answered Jan 09 '14 at 17:40

"... opens the SaveAs Dialog box, however it's not actually saving the file when I click save"

FileDialog can give you a string which contains a file path. But it does not actually perform a "Save As" operation. It's up to you, the developer, to use that file path in your code to save something somewhere.

Dim SaveBox As Object
Dim strFilePath As String

Set SaveBox = Application.FileDialog(2) ' msoFileDialogSaveAs
With SaveBox
    .InitialFileName = "WeeklyLog " & Format(Date, "yyyy_mm_dd")
    If .Show = True Then
        strFilePath = .SelectedItems(1)
    End If
End With

' now do something with strFilePath ...
If Len(strFilePath) > 0 Then
    MsgBox "File path: " & strFilePath
Else
    MsgBox "Selection cancelled."
End If

Got it, good to know. I added DoCmd.OutputTo acOutputReport, "WeeklyLogReport", acFormatPDF, strFilePath, False and it saves the file just not a a pdf. Just a file without an extension. — mrbungle, Jan 09 '14 at 18:05
der...just need to add ".pdf" to .InitialFileName = "WeeklyLog " & Format(Date, "yyyy_mm_dd") & ".pdf". Thanks for help. — mrbungle, Jan 09 '14 at 18:18

score 0 · Answer 2 · answered Jan 09 '14 at 18:15

This will save an Excel file, I think you only need a tiny bit of tweaking to get it to save a PDF:

Sub GetFileName()
    Dim fd As FileDialog
    Dim fname As String

    Do
        Set fd = Application.FileDialog(msoFileDialogSaveAs)
        With fd
            .AllowMultiSelect = False
            .InitialFileName = "New To Do.xls"
            If .Show = -1 Then fname = .SelectedItems(1)

            If fname = fd.InitialFileName Then _
                MsgBox "Please enter a new filename", vbOKOnly, "Filename Needed!"
        End With
    Loop Until fname <> fd.InitialFileName

    If IsEmpty(fname) Or fname = vbNullString Then Exit Sub
    ThisWorkbook.SaveAs fname

End Sub

actually jsut needed .InitialFileName = "WeeklyLog " & Format(Date, "yyyy_mm_dd") **& ".pdf"** thanks though — mrbungle, Jan 09 '14 at 18:19

Saving Access Report

2 Answers2