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

[Jinzuku]

Hi,

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?

Thanks

Sunny

[MikeTheBike]

Hi

You could use something like this

    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = strTitle
        If strInitialPath = "" Then
            .InitialFileName = "M:\"
        Else
            .InitialFileName = strInitialPath
        End If
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add FilterText, strFilter, 1
        If .Show Then
            strFileName = .SelectedItems(1)
 
            FileName = Mid(strFileName, InStrRev(strFileName, "\") + 1)
 
            GetFileNameOK = True
        End If
    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?

MTB

[ADezii]

@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.

[ADezii]

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.

[Jinzuku]

Excellent, thanks for that ADezii.

I did have to think twice about which button i wanted to press :)

[ADezii]

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).