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
334 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 500 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

Industry Leaders: 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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

751 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