Form vba: use "save as" dialog box to save query to Excel file

  • Thread starter Thread starter wessman
  • Start date Start date
W

wessman

I have a form in Access that allows users to query the database based
on the chooses they select and print reports on the fly ... pretty
standard stuff.

I would like to add a button to the form that brings up a "Save As" or
"Export" dialog box to save the query results to the file name and
location of the user's choice. The query is not a stored/saved query,
but a query string in the vba that has variables defined by the form
and the user's selections on said form.

Anybody?
 
Douglas J. Steele said:
One approach would be to loop through the recordset, and write the data out
to a file yourself.

You can use the code at http://www.mvps.org/access/api0001.htm to open up
the Save As dialog, and then use FreeFile to get a file number, the Open
statement to open the file for output, the Print # statement to write the
data, and the Close statement to close the file when you're done.
Click to expand...

Doug:

The link you supplied gave me a 404/DNS error. But this is not really
the solution I'm looking for anyway. I guess the first step is, what
is the Access VBA command to open a "Save As" dialog box for a table
or query?

-Wes
 
wessman said:
"Douglas J. Steele" <[email protected]> wrote in message

Doug:

The link you supplied gave me a 404/DNS error. But this is not really
the solution I'm looking for anyway. I guess the first step is, what
is the Access VBA command to open a "Save As" dialog box for a table
or query?
Click to expand...

AFAIK, there isn't one: you have to write your own.

Sorry about the link: I was going from memory, and missed a part. It's
http://www.mvps.org/access/api/api0001.htm That's the code to let you bring
up the standard Windows File Save dialog so that your user can pick the file
they want to save to.

Once you have the file name, then you can call the TransferText method in
code and pass it the name of the file.
 
wessman said:
AFAIK, there isn't one: you have to write your own.

Sorry about the link: I was going from memory, and missed a part. It's
http://www.mvps.org/access/api/api0001.htm That's the code to let you bring
up the standard Windows File Save dialog so that your user can pick the file
they want to save to.

Once you have the file name, then you can call the TransferText method in
code and pass it the name of the file.
Click to expand...

Son of a gun. I just discovered that you can use DoCmd.OutputTo and have it
prompt you for the file name. Try it, though: I doubt you'll be happy with
the results
 
Son of a gun. I just discovered that you can use DoCmd.OutputTo and have it
prompt you for the file name. Try it, though: I doubt you'll be happy with
the results
Click to expand...

I'm kind of having the reverse problem -- a database a colleague has written
is using DoCmd.OutputTo and is being prompted for the filename, but we have
no visibility of the filename in the code so cannot do any post-processing
on the output file.

I'll suggest she looks at the link you posted -- that sounds like it may be
the simplest solution...
 
Michael.Worsley said:
I'm kind of having the reverse problem -- a database a colleague has written
is using DoCmd.OutputTo and is being prompted for the filename, but we have
no visibility of the filename in the code so cannot do any post-processing
on the output file.

I'll suggest she looks at the link you posted -- that sounds like it may be
the simplest solution...
Click to expand...

*WHAM* Read all the posts *before* replying...

Just found your reply to my original post -- thank you.
 
Back
Top