saveas dialog box on button click

[mpearce]

How can i generate a save as dialog box through VBA?

I have a form with a subform that is based on a table. I have a button on this form that is a save button. When it is clicked I need a save as dialog to appear so that the user can browse to a folder and give the file a name. I then need to use this complete path in a transfertext statement that exports the data in the subform.

Problem is i cannot find the code for the saveas dialog. I can find it for open dialog but not save as.

i have tried the examples that help has and i cant get those to work right either.

Can someone help me?

[hansup]

I first set up the following code using early binding, and later changed it to use late binding. As it stands now, you shouldn't need to set reference.

You didn't mention your Access version. I can tell you the code works with 2003 and 2007. However, I seem to recall something about FileDialog capability not existing in early Access versions. Trouble is I don't remember which Access versions.

Public Function FileSaveAs(Optional ByVal strFolder As String) As String
'------------------------------------------------------------------------------
' Purpose : return path for file based upon user's selection
' Comment : return empty string if selection cancelled; FileDialog requires
' reference to the "Microsoft Office <version> Object Library" for
' early binding
'------------------------------------------------------------------------------

'Dim dlgOpen As FileDialog
'late binding; no reference needed
Dim dlgOpen As Object

'Set dlgOpen = Application.FileDialog(msoFileDialogSaveAs)
Set dlgOpen = Application.FileDialog(2) 'msoFileDialogSaveAs
With dlgOpen
.AllowMultiSelect = False
.TITLE = "Please select file"
' if we were given a filesystem path (strFolder parameter), tell the
' file dialog to start from there
If Len(strFolder) > 0 Then
.InitialFileName = strFolder
End If
If .Show = True Then
' user made a selection (instead of cancelled)
FileSaveAs = .SelectedItems(1)
Else
' return empty string to caller when no selection made
FileSaveAs = ""
End If
End With
Set dlgOpen = Nothing
End Function

[mpearce]

this would be for access 2003. ill test this and see what happens.

[mpearce]

whats the syntax to add a filter to that so that the file extension is not needed in the file name?

[hansup]

whats the syntax to add a filter to that so that the file extension is not needed in the file name?I don't understand, Michael. The code I offered you does not require the user supply an extension with the file name. Here is a copy & paste from the Immediate Window:

? filesaveas()
C:\Users\hans\Documents\foo