Send query result to a user's computer as an Excel file but allow them to select the location and name the file

I want to, with a command button on a form, send a query result to a user's computer as an Excel file but allow them to select the location it is to go to on their computer and name the file.

How can I do this?  Right now I have:

DoCmd.TransferSpreadsheet acExport, 10, "qryShipments", Environ("userprofile") & "\Desktop\Inventory.xlsx", True

But that sends it to their desktop by default and replaces the file that was there already if there was one.
SteveL13Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
first,   place this function in a regular module
Function SaveExcelFileAs()
Dim fd As Object, strLocation As String
strLocation = Environ("UserProfile") & "\Desktop\"
Set fd = Application.FileDialog(2)
With fd
    .Title = "Save File As"
    .ButtonName = "Save As"
    .InitialFileName = strLocation & "Inventory.xlsx"
    If .Show Then
        SaveExcelFileAs = .SelectedItems(1)
    End If
End With
End Function

Open in new window


change the codes in the command button to this

Dim strFileName As String
strFileName = SaveExcelFileAs()

If strFileName & "" <> "" Then
    DoCmd.TransferSpreadsheet acExport, 10, "qryShipments", strFileName, True
End If

Open in new window

0
 
Helen FeddemaCommented:
How are you going to send the file to the other user's computer?  By email, or what?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.