Transfertext Prompt for file name (1 Viewer)

tsmithjr

Registered User.
Local time
Today, 09:02
Joined
Apr 25, 2017
Messages
12
Hi All,

I currently have a btn that exports my query to a text file but as of now I have it hard coded as to where to save and file name. Preferably I would like to allow the user to be prompted to name the file. I'm very new to this and was hoping to be pointed in the best direction.

Code:
Public Sub btn_export_Click()
    DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="NGExport", TableName:="qry_RecordsNG_Export", FileName:="V:\_Records\Notary\Name txt files\test.txt", hasfieldnames:=False
End Sub

Thank you
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:02
Joined
May 7, 2009
Messages
19,169
you need to add a FileDialog (SaveAs)
to your project. In VBA, Tools->Reference

add Microsoft Office XX.X Object Library

copy and paste this code:
Code:
Public Function FileSaveAs() As String

    Dim fd As Office.FileDialog
    
    Set fd = Application.FileDialog(msoFileDialogSaveAs)
    
    With fd
        .AllowMultiSelect = False
        If .Show = -1 Then
            FileSaveAs = (.SelectedItems(1))
        Else
            FileSaveAs = vbNullString
        End If
    End With
    Set fd = Nothing
End Function
then on your code:
Code:
Public Sub btn_export_Click()
	Dim strFilePath As String
	strFilePath = FileSaveAs()
	If strFilePath <> "" Then
		DoCmd.TransferText TransferType:=acExportDelim, SpecificationName:="NGExport",TableName:="qry_RecordsNG_Export", FileName:= strFilePath, HasFieldNames:=False
	End If
End Sub
 

tsmithjr

Registered User.
Local time
Today, 09:02
Joined
Apr 25, 2017
Messages
12
arnelgp,

That worked Perfect!

Thank You!
 

Users who are viewing this thread

Top Bottom