Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-01-24
2
Medium Priority
?
336 Views
Last Modified: 2014-01-29
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.
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39807959
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 39810773
How are you going to send the file to the other user's computer?  By email, or what?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question