469,267 Members | 1,059 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

How do I open the "Save As" window when I export my data from Access to Excel?


I'm trying to map an export function to a command button to my Access form (called Results). However, I would like it so that the "Save As" window appears when I try to export the table so that the user can select where they want to save the spreadsheet.

The VBA code forces you to extract to a certain place, which isn't practical for my data's intended use. I've tried using a macro but that automatically saves the data to "My Documents", which again is not fit for purpose.

Could anyone let me know how I get the "Save As" window to appear when I press the 'Export' command button please?


Feb 17 '11 #1
5 4991
637 Expert 512MB

You could use something like this
Expand|Select|Wrap|Line Numbers
  1.     With Application.FileDialog(msoFileDialogFilePicker)
  2.         .Title = strTitle
  3.         If strInitialPath = "" Then
  4.             .InitialFileName = "M:\"
  5.         Else
  6.             .InitialFileName = strInitialPath
  7.         End If
  8.         .AllowMultiSelect = False
  9.         .Filters.Clear
  10.         .Filters.Add FilterText, strFilter, 1
  11.         If .Show Then
  12.             strFileName = .SelectedItems(1)
  14.             FileName = Mid(strFileName, InStrRev(strFileName, "\") + 1)
  16.             GetFileNameOK = True
  17.         End If
  18.     End With
This is obvoiusly an extract of code for a specific App, but more info is avaialble in Access help for the FileDialog() function, or post back if more specific info required.

Hope that is the sort of thing you want?

Feb 17 '11 #2
8,800 Expert 8TB
@Jinzuku - I'm actually putting together some very nice Code for you from Microsoft, that will enable you to use the 'Official' Windows Save As Dialog Box. The great part of this Code is that everything is encapsulated in a single Class Module, along with minimal Code in the Click() Event of a Command Button. I'll make it available to you as an Attachment sometime tomorrow.
Feb 18 '11 #3
8,800 Expert 8TB
As promised, attached please find the Standard Window's Save As Dialog Box with complete functionality. I feel as though this would give any Application that 'Professional' look. There are several Parameters that you can customize to your own specific needs. I have indicated all these areas in the Code by the [UD] Marker. Simply perform a Search in any Code Window for the 'Current Project' for the String [UD], and these areas for customization will appear. Any questions, feel free to ask.
Attached Files
File Type: zip SaveAs.zip (19.0 KB, 452 views)
Feb 18 '11 #4
Excellent, thanks for that ADezii.

I did have to think twice about which button i wanted to press :)
Feb 18 '11 #5
8,800 Expert 8TB
I did have to think twice about which button i wanted to press
Just a stupid, immature little thing that I do for all Demos! (LOL).
Feb 18 '11 #6

Post your reply

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

Similar topics

4 posts views Thread by Richard | last post: by
1 post views Thread by Siu | last post: by
3 posts views Thread by B-Dog | last post: by
reply views Thread by johnb41 | last post: by
1 post views Thread by Tomas Martinez | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.