Exporting a query so user can choose filename and directory

[Guest]

I want to export a query as an Excel file, then give the user the option to
choose a name and location for the file.

Excel and Word have a FileSave dialog, but I can't find one for Access. In
looking through this group I noticed that another couple of users were
referred to:
http://www.mvps.org/access/api/api0001.htm

This url uses the api open file dialog. It instructs that by designating
the FileOpen feature as false a file save dialog is produced. But I still
haven't figured out how to actually save a file with this feature. One
possibility I explored is the following:

--From the dialog, pass a file name and path to a function such as
TransferSpreadsheet. But how does the dialog pass a value?

Any assistance with this will be greatly appreciated.

 

[David Lloyd]

One alternative is to use the FileDialog object in Access and pass the
selected path/filename to the TransferSpreadsheet method.

The following KB article gives more specifics on the FileDialog object.
Just for reference the SelectedItems collection is a one-based collection.

http://support.microsoft.com/default.aspx?scid=kb;en-us;279508

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I want to export a query as an Excel file, then give the user the option to
choose a name and location for the file.

Excel and Word have a FileSave dialog, but I can't find one for Access. In
looking through this group I noticed that another couple of users were
referred to:
http://www.mvps.org/access/api/api0001.htm

This url uses the api open file dialog. It instructs that by designating
the FileOpen feature as false a file save dialog is produced. But I still
haven't figured out how to actually save a file with this feature. One
possibility I explored is the following:

--From the dialog, pass a file name and path to a function such as
TransferSpreadsheet. But how does the dialog pass a value?

Any assistance with this will be greatly appreciated.

 

[David C. Holley]

You'll want to investigate the Windows Common Dialog control. It
provides you will access to the typical save/save as... window of the
Office family of products. If memory serves, INSERT>TOOLS>ACTIVE X Control

Documentation on programming the control is available here...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cmctl198/html/vbobjtreeview.asp

 

[John Nurick]

Starting with the sample code from the Access Web page

'Ask for SaveFileName
strFilter = ahtAddFilterItem(myStrFilter, _
"Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

you simply add something like

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MyQuery", strSaveFileName

 

[Guest]

I appreciate your reply, but I was not able to get the FileDialogue object to
work as shown in the sample code.

 

[Guest]

Treeview is very interesting and I will certainly do some research on it for
furture reference. But it required a little more programming than I wanted
to get into for this exercise.

Thank you for your assistance.

 

[Guest]

Thanks very much. Your answer is right on the money. It does exactly what I
wanted to do.

 

[David C. Holley]

Its the WINDOWS COMMON DIALOG that I was referring to.

 

[Guest]

The link took me to Treeview. I did a search but was unable to find WINDOWS
COMMON DIAL0G. Do you have link?