allow user to select save location for file my code creates

[tagteam]

I have created several variables and I then output those variables to a text file. However, I would like to allow the user to select the location and right now I just have it going to a fixed location. I have seen a lot of code on how to get the Windows save dialogue I am just not sure how to integrate it with my code so it will save my file.



The save part of my code:

Dim RS As DAO.Recordset
Dim objFile As Object, TextFile As Object
Dim TextRecord As NameofRecordP
Set RS = CurrentDb.OpenRecordset("q_MonthlyInvoices")
Set objFile = CreateObject("Scripting.FileSystemObject")
'Set TextFile = objFile.CreateTextFile("D:\MonthlyInvoice.txt", True)

[ranman256]

Add this function to pick files ....
be sure to add the reference: Microsoft Office xx.0 Object Library, in vbe menu, TOOLS, REFERENCES

usage:
vFile = UserPick1File("C:")
If vFile <> "" Then
'do something here
End If

Public Function UserPick1File(Optional pvPath)
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr  As String, sExt As String

'===================
'YOU MUST ADD REFERENCE : Microsoft Office xx.0 Object Library, in vbe menu, TOOLS, REFERENCES
'===================
With Application.FileDialog(msoFileDialogFilePicker)   
    .AllowMultiSelect = False
    .Title = "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls;*.xlsx"
    .Filters.Add "All Files", "*.*"
    .InitialFileName = "c:\"
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    
        If .Show = 0 Then
           'There is a problem
           Exit Function
        End If
    
    'Save the first file selected
    UserPick1File = Trim(.SelectedItems(1))
End With
End Function