Exporting Queries to Excel File?

[Guest]

Hello

i was wondering how i could export queries to a new excel file. I would like to export multiple queries to one excel file (one sheet for each query). I would like the user to be able to select the location and name of the excel file. I would also like to name each sheet to what ever i like. right now i am getting by, by using "docmd.outputto" then using "docmd.transfersheet" however the file name has to be static otherwise "transfersheet" will ask the user for the file location again. i think the answer can be found in the Excel.application object but i am not that good at automation

please hel

 

[[MVP] S.Clark]

If you issue multiple transferspreadsheets to the same filename, it will
send each export to a different tab.


--
Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.fmsinc.com/consulting/

Hello,

i was wondering how i could export queries to a new excel file. I would

like to export multiple queries to one excel file (one sheet for each
query). I would like the user to be able to select the location and name of
the excel file. I would also like to name each sheet to what ever i like.
right now i am getting by, by using "docmd.outputto" then using
"docmd.transfersheet" however the file name has to be static otherwise
"transfersheet" will ask the user for the file location again. i think the
answer can be found in the Excel.application object but i am not that good
at automation.

 

[Guest]

i can use transferspreadsheets, however this action needs to dump the query output into a new excel file. transferspreadsheets requires the user to select an existing spreadsheet.

docmd.outputto allows the user to name a new file to output the query to, however how can i store the that new filename and path into memory so that i can use the information later

 

[Ken Snell]

TransferSpreadsheet will create the EXCEL file if the file doesn't already
exist.

--

Ken Snell
<MS ACCESS MVP>

i can use transferspreadsheets, however this action needs to dump the

query output into a new excel file. transferspreadsheets requires the user
to select an existing spreadsheet.

docmd.outputto allows the user to name a new file to output the query to,

however how can i store the that new filename and path into memory so that i
can use the information later?

 

[Guest]

TransferSpreadsheet only creates the EXCEL file only if i've entered a file name into to the code, however i need the user to create a new file and filename everytime the transferspreadsheet is used. I also need to transfer several spreadsheets to that one EXCEL file.

 

[Ken Snell]

Couple of ideas:

(1) Use the InputBox function in the filename argument for the
TransferSpreadsheet action. Then ACCESS will ask the person to type in the
full path and filename (which the person may not want to do).

(2) Use the InputBox function along with a hardcoded path string in the
filename argument (as noted above). This way, the user just needs to type in
a filename.

(3) Create and use a form that allows the user to type in path and/or
filename and then use that control's value in the code as the filename
argument.

(4) Use the API to call the "GetSaveAsFilename" type navigation window to
allow the user to browse to the final folder and to enter the filename. See
The ACCESS Web for one way to do this (it's a bit complicated if you've not
used VBA code before):
http://www.mvps.org/access/api/api0001.htm


--

Ken Snell
<MS ACCESS MVP>

TransferSpreadsheet only creates the EXCEL file only if i've entered a

file name into to the code, however i need the user to create a new file and
filename everytime the transferspreadsheet is used. I also need to transfer
several spreadsheets to that one EXCEL file.