Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Sep 2008
    Location
    Liverpool, Merseyside, United Kingdom
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    GetSaveAsFileName in Access? (2003)

    Is there an equivalent in Access to Excel's "Application.GetSaveAsFileName" which brings up the "Save As" dialogue box?

    I'm outputting a report to Excel and would like to prompt the user for a location to save to and suggest a file name.

    Using DoCmd.OutputTo works fine but uses the report name as the file name, which I don't want to do.

    Not sure if this all makes sense, but thanks for looking anyway!
    Irene

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: GetSaveAsFileName in Access? (2003)

    You can use Application.FileDialog with the msoFileDialogSaveAs option to display a Save As dialog. If you type FileDialog somewhere in the Visual Basic Editor and press F1, you'll get the built-in help.

  3. #3
    New Lounger
    Join Date
    Sep 2008
    Location
    Liverpool, Merseyside, United Kingdom
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetSaveAsFileName in Access? (2003)

    Thank you once again for your help Hans.
    Irene

  4. #4
    New Lounger
    Join Date
    Sep 2008
    Location
    Liverpool, Merseyside, United Kingdom
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetSaveAsFileName in Access? (2003)

    Sorry Hans, but I'm still having problems with this. I checked out the help which advised using:

    Private Sub cmdExportData_Click()

    Dim dlgSaveAs As FileDialog

    Set dlgSaveAs = Application.FileDialog(FileDialogType:=msoFileDial ogSaveAs)

    dlgSaveAs.Show

    End Sub

    Unfortunately it won't even compile, highlighting "dlgSaveAs As FileDialog" and reporting the error: Compile Error: User-defined type not defined

    Any ideas?
    Thanks
    Irene

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: GetSaveAsFileName in Access? (2003)

    Hi Irene,

    The code would have needed a reference to the Microsoft Office 11.0 Object Library, but I'm terribly sorry, I now see (in FileDialog Property) that msoFileDialogSaveAs is not supported in Access (it is in Word and Excel). <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    I have attached a database that shows how you can display a Save As dialog in Access; it will work in all versions.
    To use it in your own database, import the two modules CommonDialog and modGlobals from the sample database into yours.
    The code behind the command button on the form frmSave in the sample database demonstrates how to use CommonDialog to display a Save As dialog.
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Sep 2008
    Location
    Liverpool, Merseyside, United Kingdom
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetSaveAsFileName in Access? (2003)

    Thanks again Hans, will try this later on today.
    It always very frustrating when simple, easy-to-use functions in Excel are not supported in Access,
    but as usual with Access, the simplest things always seem to end up the most complicated! <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    We still love it though!
    Regards
    Irene

  7. #7
    New Lounger
    Join Date
    Sep 2008
    Location
    Liverpool, Merseyside, United Kingdom
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetSaveAsFileName in Access? (2003)

    Hi again Hans, the saga continues!
    I've imported your modules and almost everything works as you said it would, which is fantastic (although I'm not quite sure exactly what's going on - but I'll work on that).

    The one remaining problem I've got with it is when it comes to changing the path. This is quite long-winded I'm afraid, and I do apologise for taking up so much of your time.

    When the Save As dialog box appears the "Save In:" drop-down defaults to the db current location.

    If I then click on the drop-down arrow I get an error message: This operation has been cancelled due to restrictions in effect on this computer. Please contact your system adminstrator.
    On clicking on OK the Save As box goes and your "No filename specified" msgbox appears. Our IT department does operate quite a strict lock-down policy on the PCs here so I thought that was the problem.

    However, if instead of clicking on the down arrow, I click on the Desktop icon on the left sidebar, then double-click on the relevant network drive name in the main window, I can then use the down arrow on the drop-down to navigate with no restriction.

    Now it's no problem for me to advise users that they'll have to go via the Desktop icon etc (although they're bound to ignore this instruction because they're users), but I was wondering if this is just something I'll have to live with? Or is this something that your code is causing? Unfortunately, I'm not at all up to speed on Class modules so I can't work it out myself.

    Many many thanks once again, your help has been invaluable.
    Irene

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: GetSaveAsFileName in Access? (2003)

    Although the code looks a bit intimidating, all it does is invoke the Save As dialog built into Windows. When you click the dropdown arrow in the Save In box, Windows tries to display a list of all available drives; if your company has a policy that some drives are inaccessible to the average user, that would cause the error message you see.
    You may also be able to get around the problem by specifying the drive or folder you want to go to in the InitDir property of the CommonDialog object, e.g.

    With dlg
    .InitDir = "H:MyExcelFiles"
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •