Exporting & opening the Save As Dialog Box

[paquer]

I'd like to provide an Export Function from my forms, where the User
can choose the File name & save location
Although I cannot get the "Save As" dialog box to open properly from
access.
What is the proper code to get Access to open the Windows Save As
dialog box?
Thanks

[Tom van Stiphout]

On 30 Apr 2007 05:29:49 -0700, pa****@gmail.com wrote:

Check this out:
http://www.mvps.org/access/api/api0001.htm

-Tom.

>I'd like to provide an Export Function from my forms, where the User
can choose the File name & save location
Although I cannot get the "Save As" dialog box to open properly from
access.
What is the proper code to get Access to open the Windows Save As
dialog box?
Thanks

[paquer]

I guess this is all way above me. I read through the code and remarks
regarding it all, although I dont see the method of actually prompting
for this to happen.

'''This can be done by either using the Common Dialog Control in
Access 97 or by using the APIs defined for this purpose. ???

On Apr 30, 10:03 am, Tom van Stiphout <[e-mail]:

On 30 Apr 2007 05:29:49 -0700, [e-mail] wrote:

Check this out:http://www.mvps.org/access/api/api0001.htm

-Tom.

I'd like to provide an Export Function from my forms, where the User
can choose the File name & save location
Although I cannot get the "Save As" dialog box to open properly from
access.
What is the proper code to get Access to open the Windows Save As
dialog box?
Thanks- Hide quoted text -

- Show quoted text -

[paquer]

When/Where do I actually pass the Data (datasheet) to this code?
I can run it as is, but It saves nothing

On May 2, 10:31 am, [e-mail] wrote:

I guess this is all way above me. I read through the code and remarks
regarding it all, although I dont see the method of actually prompting
for this to happen.

'''This can be done by either using the Common Dialog Control in
Access 97 or by using the APIs defined for this purpose. ???

On Apr 30, 10:03 am, Tom van Stiphout <[e-mail]:

On 30 Apr 2007 05:29:49 -0700, [e-mail] wrote:

Check this out:http://www.mvps.org/access/api/api0001.htm

-Tom.

>I'd like to provide an Export Function from my forms, where the User
>can choose the File name & save location
>Although I cannot get the "Save As" dialog box to open properly from
>access.
>What is the proper code to get Access to open the Windows Save As
>dialog box?
>Thanks- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

[Tom van Stiphout]

On 2 May 2007 08:23:17 -0700, pa****@gmail.com wrote:

You initially asked "What is the proper code to get Access to open the
Windows Save As dialog box?". I showed you the code. Sorry if it is
complicated; it's what's needed to get this dialog. There are a few
alternatives, but no appealing ones. Long story.

Now you're asking about exporting data. That's done with
DoCmd.TransferText or DoCmd.TransferSpreadsheet or a few other methods
such as OutputTo. Those methods take the destination filename as one
of their arguments. That filename is what you got when running the
Save As dialog, so you can just use it as the argument for the DoCmd
call:

strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
DoCmd.TransferText acExportDelim, , "someQuery", strSaveFileName, True

-Tom.

>When/Where do I actually pass the Data (datasheet) to this code?
I can run it as is, but It saves nothing

On May 2, 10:31 am, [e-mail] wrote:

>I guess this is all way above me. I read through the code and remarks
regarding it all, although I dont see the method of actually prompting
for this to happen.

'''This can be done by either using the Common Dialog Control in
Access 97 or by using the APIs defined for this purpose. ???

On Apr 30, 10:03 am, Tom van Stiphout <[e-mail]:

On 30 Apr 2007 05:29:49 -0700, [e-mail] wrote:

Check this out:http://www.mvps.org/access/api/api0001.htm

-Tom.

>I'd like to provide an Export Function from my forms, where the User
can choose the File name & save location
Although I cannot get the "Save As" dialog box to open properly from
access.
What is the proper code to get Access to open the Windows Save As
dialog box?
Thanks- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

[paquer]

Cool, I get it now (for the most part).
I just put the code into its own module. (didnt alter it at all). &
Used the transfer spreadsheet commands, which inherintly use said code
from the module.
I still have 3 issues to resolve though;

1. I cannot export as dBase, or CSV files... only XLS works. I added
the strFilters as such;
strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)",
"*.DBF")
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)",
"*.TXT")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)",
"*.XLS")
strFilter = ahtAddFilterItem(strFilter, "CSV Files (*.csv)",
"*.CSV")

I get the following error when trying to save as these other file
types;
Run-time error '3027' Cannot update. Database or object is read-
only.
I tried removing the spreadsheet type from the export code. It still
exports fine for XLS but didnt change the error for the other
filetypes.

2. I cannot get the overwrite prompt to work. I added the flags
included in the Save As Dialog module, but to no avail...it still
always overwrites. I tried adding it as lnflags (previously Dim'd _ As
long) & adding the ahtOverwriteprompt to the transfer cmd itself.

3. Is there a way I can get the export to export from the Current
Form's, current record only?
Do I need to transfer the form data to a temporary table to do
this?
(The transfer spreadsheet cmd only accepts queries & tables
right?

[paquer]

Ok, I got the Overwrite prompt to work (had previously placed the
Flags into the code improperly)
I still have the other 2 issues to resolve though;

1. I cannot export as dBase, or CSV files... only XLS works.

I added the strFilters as such;

strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)",
"*.DBF")
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)",
"*.TXT")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)",
"*.XLS")
strFilter = ahtAddFilterItem(strFilter, "CSV Files (*.csv)",
"*.CSV")

I get the following error when trying to save as these other file
types;
Run-time error '3027' Cannot update. Database or object is read-
only.
I tried removing the spreadsheet type from the export code. It still
exports fine for XLS but didnt change the error for the other
filetypes.

2.
Is there a way I can get the export to export from the Current
Form's, current record only?
Do I need to transfer the form data to a temporary table to do this?
(The transfer spreadsheet cmd only accepts queries & tables right)?

[paquer]

Ok, so apparently the OutputTo cmd is the required cmd to export data
in dbf/csv formats.
Now I know.

SO ive answered 2 of my 3 questions.
(kinda feel like someone with multi personalities here, replying to my
own posts)

3rd & final...

This export function (Cmd Button) is on a Form which is based on a
query.
I would like to export only the current record as opposed to all
records viewed with the query.
Can this be done within the Export Code? Here is my current export
code;

Private Sub cmdExport_Header_Click()
Dim strFilter As String

strDefaultDir = "c:\"
strDefaultFileName = "File_Name"

strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf",
"*.DBF")
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt",
"*.TXT")
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt",
"*.TXT")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*", "*.*")
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls",
"*.XLS")
strFilter = ahtAddFilterItem(strFilter, "CSV Files (*.csv",
"*.CSV")
strSaveFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT Or
ahtOFN_READONLY, strDefaultDir, strFilter, , , strDefaultFileName,
"Save Header Info", , False)
Me.Repaint
If strSaveFileName <"" Then

DoCmd.OutputTo acOutputForm, "Form_Name", "Microsoft Excel
(*.xls)", strSaveFileName, True
End If
End Sub

[paquer]

Instead of trying to export the form's current record, I instead
created a Make Table query, based on the table in which the form gets
it data from.
I then added to the query, the criteria which calls for the current
records PK value.
Criteria: "Forms!Formname.FieldName".
Then in the export code, just export the newly made table with the
single record.
The make table query will always overwrite it's current data so there
is no ongoing maintenance.
voila

DoCmd.Echo False
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrySpec_Record_Export", acViewNormal, acAdd
DoCmd.Echo True
DoCmd.SetWarnings True

....

DoCmd.OutputTo acOutputTable, "SpecRec_Temp", "Microsoft Excel
(*.xls)",
strSaveFileName, True