"Save as..." dialog box in MSAccess vba: how?

Question

In MSAccess I've a mask with a button. When the user clicks on the button, the data in a table are exported on a .txt file:

Private Sub Command_Click()
Dim Rst As DAO.Recordset
Dim AField As DAO.Field
Dim TempStr As String
Dim FileNumber
FileNumber = FreeFile
Open "c:\table.txt" For Output As #FileNumber
Set Rst = CurrentDb.OpenRecordset("Tabella1", dbOpenForwardOnly)
Do While Not Rst.EOF
    For Each AField In Rst.Fields
        If (AField.Name <> "ID") Then
            TempStr = TempStr & AField.value & "    "
        End If
    Next
    Print #FileNumber, Left(TempStr, Len(TempStr) - 1)
    TempStr = ""
    Rst.MoveNext
Loop
Rst.Close
Set Rst = Nothing
Close #FileNumber
End Sub

It works, but I would display a "Save as..." dialog box by allowing the user to choose the file on which export the data.

Is it possible?

score 6 · Accepted Answer · edited May 23 '17 at 12:16

6

You can set a reference to the Microsoft Office x.x Object Library and use FileDialog.

FileDialog Properties

Sub ShowFileDialog()
    Dim dlgOpen As FileDialog
    Set dlgOpen = Application.FileDialog(msoFileDialogSaveAs)
    With dlgOpen
        .InitialFileName = "Z:\docs\"
        .Show
    End With
End Sub

Also: How do I get a single file name out of a File Dialog object in VBA (for MS Access 2007)?

edited May 23 '17 at 12:16

Community

1
1

answered Jul 31 '12 at 15:18

Fionnuala

90,370
7
114
152

Is there a different way (without using the object library)? – Sefran2 Jul 31 '12 at 15:22
You can present them with a form (best) or InputBox and then do all the checking yourself as to whether a file already exists with that name or not and whether the name is legal and suitable. – Fionnuala Jul 31 '12 at 15:25
1

msoFileDialogSaveAs is not supported in Access. The Filters Properties i.e. don't work - which is quiet substantial for SaveAs Dialog. – user3305711 Feb 17 '18 at 18:58

score 1 · Answer 2 · answered Aug 02 '12 at 19:12

1

For an API solution see here : http://access.mvps.org/access/api/api0001.htm

Works well for me. Simply copy and paste in a new module.

answered Aug 02 '12 at 19:12

Xilmiki

1,453
15
22

As an aside, the code in the link will not work with 64 bit MS Access. – Fionnuala Jan 18 '13 at 10:16

"Save as..." dialog box in MSAccess vba: how?

2 Answers2

Linked