Export Form To XLS With 'Select Folder' Window

[Madmart1gan]

I have a form that I'd like to allow users to export to .xlsx via a button on the form. However I'd like to make it so that once the button is pressed, they are prompted to select a folder location before the export takes place.

I thought I would find a lot more 'how to' on this specific issue, but haven't had much luck. Any help is greatly appreciated.

 

[CJ_London]

you need to investigate the use of filedialog which opens the various forms on folder navigation forms.

For example this function opens the navigation form, allows the user to navigate to a folder and then select a file or type a name

Function SaveAs() As String
Dim obj As Variant
 
    SaveAs = ""
    With Application.FileDialog(2) 'msoFileDialogSaveAs
        .AllowMultiSelect = False
        .Title = "Choose a location"
        .ButtonName = "Save"
     
        If .Show = 0 Then
            MsgBox "Operation cancelled by user"
            Exit Function
        End If
     
        For Each obj In .SelectedItems
            SaveAs = obj
        Next obj
   End With
 
End Function

 

[Madmart1gan]

Thank you for the response! I will certainly take that and see where I can get with it.

 

[Madmart1gan]

For anyone who has the same issue, the problem was much easier than I thought. I simply created a macro with an OutputTo Action and it works exactly as I wanted. Hope it helps any noobs like me.

 

[CJ_London]

Just a suggestion to help those noobs - why not post your macro?

 

[Madmart1gan]

Action: OutputTo
Arguments: Form, Form_Name, Excel Workbook (*.xlsx), , Yes, , 0, Print