By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

SaveAs Dialogue box

P: 47
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.
Jul 18 '07 #1
Share this Question
Share on Google+
17 Replies


JKing
Expert 100+
P: 1,206
I think this is what you're looking for

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdYourButton_Click()
  2.  
  3. Dim dlgSaveAs As FileDialog
  4. Dim strFilePath As String
  5. Dim strFileName As String
  6.  
  7. Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
  8.  
  9. dlgSaveAs.Show
  10. strFilePath = dlgSaveAs.SelectedItems(1)
  11.  
  12. Me.yourTextBox = strFilePath
  13.  
  14. strFileName = Right(strFilePath, Len(strFilePath) - InStrRev(strFilePath, "\"))
  15. strFilePath = Left(strFilePath, InStrRev(strFilePath, "\"))
  16. End Sub
  17.  
Replace youTextBox with the name of your text box and of course code this for your command button's click() event.
Jul 18 '07 #2

P: 47
I think this is what you're looking for

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdYourButton_Click()
  2.  
  3. Dim dlgSaveAs As FileDialog
  4. Dim strFilePath As String
  5. Dim strFileName As String
  6.  
  7. Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
  8.  
  9. dlgSaveAs.Show
  10. strFilePath = dlgSaveAs.SelectedItems(1)
  11.  
  12. Me.yourTextBox = strFilePath
  13.  
  14. strFileName = Right(strFilePath, Len(strFilePath) - InStrRev(strFilePath, "\"))
  15. strFilePath = Left(strFilePath, InStrRev(strFilePath, "\"))
  16. End Sub
  17.  
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?
Jul 18 '07 #3

JKing
Expert 100+
P: 1,206
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.
Jul 18 '07 #4

P: 47
Tools > References in what application? I am using MS Access 11.6
Jul 18 '07 #5

P: 47
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.
Jul 18 '07 #6

JKing
Expert 100+
P: 1,206
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.
Jul 18 '07 #7

P: 47
It says

Comple Error -

User defined type is not declared.
Jul 18 '07 #8

JKing
Expert 100+
P: 1,206
What version of access are you using?
Jul 18 '07 #9

P: 47
What version of access are you using?
Microsoft Access (11.6566.6568) SP2
Jul 18 '07 #10

JKing
Expert 100+
P: 1,206
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.
Jul 18 '07 #11

P: 47
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!!!
Jul 18 '07 #12

P: 47
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"
Jul 18 '07 #13

JKing
Expert 100+
P: 1,206
You can trap this by using the show method. It returns false on cancel.

Expand|Select|Wrap|Line Numbers
  1. Dim dlgSaveAs As FileDialog
  2. Dim strPath As String
  3.  
  4. Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
  5.  
  6. If dlgSaveAs.Show = True Then
  7.     strPath = dlgSaveAs.SelectedItems(1)
  8. Else
  9.     MsgBox "Save was cancelled"
  10. End If
  11.  
Jul 18 '07 #14

P: 3
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.
Sep 4 '07 #15

P: 38
fd1
You can trap this by using the show method. It returns false on cancel.

Expand|Select|Wrap|Line Numbers
  1. Dim dlgSaveAs As FileDialog
  2. Dim strPath As String
  3.  
  4. Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
  5.  
  6. If dlgSaveAs.Show = True Then
  7.     strPath = dlgSaveAs.SelectedItems(1)
  8. Else
  9.     MsgBox "Save was cancelled"
  10. End If
  11.  
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?
Sep 15 '07 #16

JKing
Expert 100+
P: 1,206
Do you also have Microsoft Office 11.0 Library checked?
Sep 15 '07 #17

P: 38
fd1
Do you also have Microsoft Office 11.0 Library checked?
Now I do (office 12.0 library) , works fine now. Thanks JKing
Sep 16 '07 #18

Post your reply

Sign in to post your reply or Sign up for a free account.