prompt user for file name upon export (1 Viewer)

[datacontrol]

I searched the forum, but no luck on this topic.

Is there any existing code to prompt the user for a filename upon exporting to excel?

I am already familiar with the process to export to excel via VBA, however I need to procure code for the prompting bit, so the user can enter a unique filename. This code should also check for overwriting files. Basically what I need is what the access does when you choose file>export from a query window. I know it sounds stupid but I build interfaces for non access wannabes.

 

[Treason]

try this

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

 

[datacontrol]

thanks

Looks like strong code. Thanks for the link.

How exactly do I call this from a command button? Should I save this as a module?

I only need a save as dialog and I am unclear on the instructions on that webpage.

What is the "TestIt" function all about?

I am using Access 2000

Hopefully you will be able to help since you are somewhat familiar with this code.

 

[Treason]

paste that really long code at the bottom into a new module and compile and save it...

Then OnClick of the button put in this code

Dim strFilter As String
Dim strSaveFileName as string

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

'here you would probaly want your Transfertext code
'for example
DoCmd.TransferText acExportFixed, "QryUpload Export Specification", "QryUpload", strSaveFileName, False, ""

Good Luck to ya

 

[datacontrol]

error

I am getting an error on this line:

strFilter = ahtAddFilterItem(myStrFilter, "Excel Files (*.xls)", "*.xls")

More explicitly, "myStrFilter"

I saved the other code in a module called "save". Don't I have to call it somehow?

 

[Treason]

change myStrFilter to StrFilter

 

[datacontrol]

filename and path?

thanks!

The save as dialog box opens now. I have an issue with my transfer spread sheet command.

DoCmd.TransferSpreadsheet acExport, 3, "same agent ref with different dollar amt", "N:\Agent File Reconciliation\archive\", True, ""

I get errors related to file path and name

First off, I don't want to specify a path or file name because that defeats the purpose of the save as dialog box.

Any suggestions?

 

[Treason]

replace "N:\Agent File Reconciliation\archive\" with strSaveFileName

 

[datacontrol]

okay, it saves now but it is being saved as strSaveFileName.xls.


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "same agent ref with different dollar amt", "strSaveFileName", True, ""

Thanks a lot man...I really appreciate it!

 

[Treason]

I thought you had some VBA experiance..

strSaveFileName not "strSaveFileName"

you need to reference the path string named strSaveFileName. If you put "strSaveFileName" it reads it as a string value not a reference

 

[datacontrol]

Thank you oh great VB masta...

seriously this works awesome now! I know little VB, I am learning as I go thanks to those like you!

 

[datacontrol]

more issues

Two issues at this point:

1. Is there a way I can declare everything to be formatted as text in my Do.Cmd statement? Some fields are displaying scientific notation when I open my exported file in excel. I made certain all field properties are set correctly in my table and query and the data displays fine there.

2. If I cancel out of the save as dialog box, I get an error.


one other thing....can I use this to export a report? I keyed in the name of the report but I still get errors.

 

[datacontrol]

error when I cancel out of save as

Run Time error '2522' This action requires a file name argument.

Here is the problem code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "dump Without Matching after", strSaveFileName, True, ""

 

[Treason]

search help for error handling for more in depth methods...

The simplliest way to do it I guess would be to put this after the Dim statements...

On Error Resume Next

or

On Error Exit Function

 

[datacontrol]

So simple...I am really tired today.

Thanks a lot!

Happy Thanksgiving!

 

[datacontrol]

How are you today?

I am looking into how to incorporate this code in browsing for files and importing them. I have already made some progress. Current code:


Private Sub Command190_Click()

Dim strFilter As String
Dim strSaveFileName As String
On Error GoTo Error_exit
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

'here you would probaly want your Transfertext code
'for example
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "same agent ref with different dollar amt1", strSaveFileName, True, ""

'("D:\" & strMyFile & ".vp")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MicrosoftExcel(*.xls)", strSaveFileName, True, ""

Error_exit:



End Sub

 

[datacontrol]

I figured it out.

This is my code for importing using the same module.

Private Sub Command190_Click()

Dim strFilter As String
Dim strSaveFileName As String
On Error GoTo Error_exit
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

'here you would probaly want your Transfertext code
'for example
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "same agent ref with different dollar amt1", strSaveFileName, True, ""

'("D:\" & strMyFile & ".vp")
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MicrosoftExcel(*.xls)", strSaveFileName, True, ""
DoCmd.TransferSpreadsheet acImport, 8, "after", strSaveFileName, True, ""
Beep
MsgBox "Your AFTER data has been imported", vbOKOnly, ""

Error_exit:



End Sub