User Input for Saved File Destination

[nakedbamboo]

I have a form that users use to run a search on the records depending on criteria they select on the form. The search button outputs the result to an excel file. I have all that working fine now. What I would like to know is if it is possible, when the user clicks the button, to have the generic windows save-file dialog popup so they can browse to a destination and supply a file name for the output? Any help would be greatly appreciated and if it involves code, I would need some examples because I am a novice at VB. Thanks.

 

[dhookom]

The code for doing this can be found at

http://www.mvps.org/access/api/api0001.htm

Duane
MS Access MVP

 

[nakedbamboo]

Ok, it sounds lie it will do what I want it to do, but it is a little over my head. Where do I incorporate all that code into the database? And I can't figure out from the site is telling me how to actually call the function. How would I incorporate it into my query? Below is what I have now. How would I make this ask the user for a save destination instead of defaulting to the F:\? Thanks for the help.

DoCmd.OutputTo acOutputQuery, "AlumniSearch", acFormatXLS, "F:\Test.xls"

 

[nakedbamboo]

Ok, I actually was able to get the dialog working using the FAQ faq705-2484 (How do select a file using the a dialog box.) Now I still have the question of how to make it work with my output command when I click the button. Thanks.

 

[dhookom]

You need to create a new module and then paste the code from the web page into the module. Save the module with a name like "modAPIs".

Create a form with at text box "txtFileName" and a command button. Edit the On Click event of the command button to something like:

    Dim strFilter As String
    Dim lngFlags As Long
    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
    strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
    Me.txtFileName= ahtCommonFileOpenSave(InitialDir:="C:\", _
        Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
        DialogTitle:="Hello! Open Me!")

Duane
MS Access MVP

 

[nakedbamboo]

Ok, you are not going to realize how simple a solution to this problem I just discovered. If I just leave the filename section of the following command blank, i.e. the "F:\Test.xls", the program automatically prompts for a save destination which is what I was going for all along. I discovered this completely by mistake. Anyway, thanks for the help. I learned a lot on the way.


DoCmd.OutputTo acOutputQuery, "AlumniSearch", acFormatXLS, "F:\Test.xls"