SaveAs Dialogue box

[jschmidt]

Hello everyone,

I have a button on a form that calls a function to populate some textboxes with where criteria that is used in a parameterized query this is then used as the table source for a macro which is calling the transfertext function.

What I want to be able to do is have a saveas dialog box open when they click the button and populate a textbox with the path and filename the user enters. Then I want to use the variable with the stored path as the filename argument in the transfertext function.

So how do I get the saveas dialogue box to open and also, how do I save the path and filename in a variable?

Any Suggestions? Is this possible?

Thanks to everyone for the help.

[JKing]

I think this is what you're looking for

Private Sub cmdYourButton_Click()
 
Dim dlgSaveAs As FileDialog
Dim strFilePath As String
Dim strFileName As String
 
Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
 
dlgSaveAs.Show
strFilePath = dlgSaveAs.SelectedItems(1)
 
Me.yourTextBox = strFilePath
 
strFileName = Right(strFilePath, Len(strFilePath) - InStrRev(strFilePath, "\"))
strFilePath = Left(strFilePath, InStrRev(strFilePath, "\"))
End Sub
 

Replace youTextBox with the name of your text box and of course code this for your command button's click() event.

[jschmidt]

I think this is what you're looking for

Private Sub cmdYourButton_Click()
 
Dim dlgSaveAs As FileDialog
Dim strFilePath As String
Dim strFileName As String
 
Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
 
dlgSaveAs.Show
strFilePath = dlgSaveAs.SelectedItems(1)
 
Me.yourTextBox = strFilePath
 
strFileName = Right(strFilePath, Len(strFilePath) - InStrRev(strFilePath, "\"))
strFilePath = Left(strFilePath, InStrRev(strFilePath, "\"))
End Sub
 

Replace youTextBox with the name of your text box and of course code this for your command button's click() event.

First of all thanks for the prompt response.
There seems to be a problem with the FileDialog type. Is this a user defined type?

[JKing]

No, it is a member of the Microsoft Office 11.0 Object Library. Check to make sure you have a reference to that by going to tools > references. If it's not checked just find it and check that off.

[jschmidt]

Tools > References in what application? I am using MS Access 11.6

[jschmidt]

Tools > References in what application? I am using MS Access 11.6

I found it. It was selected and I still get the same error.

[JKing]

Sorry I should have be more clear. In the Microsft Visual Basic Editor. There should be a menu item Tools from there navigate to references.

[jschmidt]

It says

Comple Error -

User defined type is not declared.

[JKing]

What version of access are you using?

[jschmidt]

What version of access are you using?

Microsoft Access (11.6566.6568) SP2

[JKing]

Check the references once more and make sure you haven't mistaken Microsoft Access 11.0 Object Library for Microsoft Office 11.0 Object Library. Both should be checked.

[jschmidt]

Check the references once more and make sure you haven't mistaken Microsoft Access 11.0 Object Library for Microsoft Office 11.0 Object Library. Both should be checked.

That was the problem. Thanks!!!

[jschmidt]

strFilePath = dlgSaveAs.SelectedItems(1)

When the user clicks on cancel after the save as dialog box is opened I get an error on this piece of code. Is there a way to avoid this?

Error: "Invalid procedure call or argument"

[JKing]

You can trap this by using the show method. It returns false on cancel.

Dim dlgSaveAs As FileDialog
Dim strPath As String
 
Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
 
If dlgSaveAs.Show = True Then
    strPath = dlgSaveAs.SelectedItems(1)
Else
    MsgBox "Save was cancelled"
End If
 

[newwin]

Hi,I am using Microsoft Access 12.0 Object Library and Microsoft Office 12.0 Access Database Engine Object Library then what should i do because its not running. Both are checked. Please reply. I am waiting.

[fd1]

You can trap this by using the show method. It returns false on cancel.

Dim dlgSaveAs As FileDialog
Dim strPath As String
 
Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
 
If dlgSaveAs.Show = True Then
    strPath = dlgSaveAs.SelectedItems(1)
Else
    MsgBox "Save was cancelled"
End If
 

JKing, I'm still getting a Compile error "User defined type not defined"
I have both Microsoft Access/Excel 11.0 Object library checked in References and I'm using Access 2003. Any idea why?

[JKing]

Do you also have Microsoft Office 11.0 Library checked?

[fd1]

Do you also have Microsoft Office 11.0 Library checked?

Now I do (office 12.0 library) , works fine now. Thanks JKing