What is the point of the FileDialog(msoFileDialogSaveAs)? Seems pointless to me.

Jamualson

Board Regular
Joined
Feb 17, 2021
Messages
128
Office Version
  1. 2019
Platform
  1. Windows
The FileDialog(msoFileDialogSaveAs) method doesn't save anything, it's just a window in which the user can
- select a folder
- type a file name
- choose a file extension

If I want to use the User's choice then I have to manually handle every combination of extensions... because if User selects pdf or txt file --> those have to be handled differently. (macro recorder records completely other code for exporting as pdf or as txt)

So I don't know what's the point of FileDialog(msoFileDialogSaveAs).

If I want to export the file in a specific format then I will maybe make the user to choose a folder and file name but not extension. (since then i have to handle 50 situations)

So I really don't get the use of FileDialog(msoFileDialogSaveAs) method. Why or when is this useful? Thank you very much for your idea.

_________________________________

Sub Save()

Dim x As Integer

dim Path As String

x = Application.FileDialog(msoFileDialogSaveAs).Show
If x> 0 Then
Path = Application.FileDialog(msoFileDialogSaveAs).SelectedItems(1)

MsgBox Path

End If

End Sub
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,508
The FileDialog object allows you to show the user a customize window, where the path, filename, and file extension are pre-determined.

In your example, though, you're not properly checking the user response. When a user clicks the Save button, the Show method returns -1. So If x > 0 Then will always evaluate to False, whether the user clicks Save or Cancel.

Also, you're not actually saving the file. In order to save the workbook, you'll need to use the Execute method of the FileDialog object.

Here's an example where the SaveAs dialog box is shown, prompting the user to save the workbook in a specified location, with the name sample, and with the extension .xlsm. Once the user clicks the Save button, the active workbook is saved. Otherwise, if the user clicks Cancel, it exits the sub.

VBA Code:
Sub Save()

    'Dim path As String

    With Application.FileDialog(msoFileDialogSaveAs)
        .ButtonName = "Save"
        .FilterIndex = 2 'specify .xlsm extension
        .InitialFileName = "C:\Users\Domenic\Desktop\sample.xlsm"
        .Title = "SaveAs"
        If .Show = 0 Then Exit Sub 'user cancelled
        .Execute
        'path = .SelectedItems(1)
    End With
    
    'MsgBox path, vbInformation

End Sub

Hope this helps!
 
Last edited:
Solution

Jamualson

Board Regular
Joined
Feb 17, 2021
Messages
128
Office Version
  1. 2019
Platform
  1. Windows
The FileDialog object allows you to show the user a customize window, where the path, filename, and file extension are pre-determined.

In your example, though, you're not properly checking the user response. When a user clicks the Save button, the Show method returns -1. So If x > 0 Then will always evaluate to False, whether the user clicks Save or Cancel.

Also, you're not actually saving the file. In order to save the workbook, you'll need to use the Execute method of the FileDialog object.

Here's an example where the SaveAs dialog box is shown, prompting the user to save the workbook in a specified location, with the name sample, and with the extension .xlsx. Once the user clicks the Save button, the active workbook is saved. Otherwise, if the user clicks Cancel, it exits the sub.

VBA Code:
Sub Save()

    'Dim path As String

    With Application.FileDialog(msoFileDialogSaveAs)
        .ButtonName = "Save"
        .FilterIndex = 1 'specify .xlsx extension
        .InitialFileName = "C:\Users\Domenic\Desktop\sample.xlsx"
        .Title = "SaveAs"
        If .Show = 0 Then Exit Sub 'user cancelled
        .Execute 'save active workbook
        'path = .SelectedItems(1)
    End With
  
    'MsgBox path, vbInformation

End Sub

Hope this helps!
thank you very much, great insights !! :) :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,931
Messages
5,639,061
Members
417,067
Latest member
rohitbabshet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top