FileDialogSaveAs not saving anything?

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
15,505
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Below is a trimmed down version of the code that I'm attempting to use, the code executes fine when attempting to save using Ctrl s up to the point of hitting the save button on the file dialog pop up. At this point the pop up closes and nothing happens, no file is saved.

Using save as from the file menu, the code runs and saves correctly so I'm guessing that the FileDialogSaveAs pop up is not the same.

Adding a break point to the code and stepping through manually, the code ends after clicking the save button on the pop up.

I tried with and without EnableEvents = False thinking that the code could be looping and cancelling itself but this made no difference either way.
Code:
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ThisWorkbook.Name = "original.xlsm" Then
        ' do other stuff
    If SaveAsUI = False Then
        Cancel = True
        With Application.FileDialog(msoFileDialogSaveAs)
            .InitialFileName = ThisWorkbook.Path & "\" & "Newname.xlsx"
            .Show
        End With
    End If
End If
End Sub
Am I missing something simple, or going about it the wrong way?

Thanks in advance for any suggestions.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
How about
Rich (BB code):
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ThisWorkbook.Name = "original.xlsm" Then
        ' do other stuff
    If SaveAsUI = False Then
        Cancel = True
        With Application.FileDialog(msoFileDialogSaveAs)
            .InitialFileName = ThisWorkbook.Path & "\" & "Newname.xlsx"
            If .Show = -1 Then
               Application.DisplayAlerts = False
               Application.EnableEvents = False
               Me.SaveAs .SelectedItems(1), 51
               Application.DisplayAlerts = False
               Application.EnableEvents = True
            End If
        End With
    End If
End If
End Sub
 
Last edited:
Upvote 0
Thanks, Fluff!

I'm getting RTE-1004 on the line
Code:
Me.SaveAs .SelectedItems(1), 51
This extension can not be used with the selected file type.

I tried changing the line above to
Code:
Me.SaveAs .SelectedItems(2), 52
and changing the filename to "Newname.xlsm" thinking it would fix the problem (not fully understanding it though), but that resulted in RTE-5, invalid procedure call or argument :confused:

Something that I forgot to mention was that I'm trying to save the copy as .xlsx instead of .xlsm

This is not critical as the code will only execute in the original file, so if it will be more stable keeping it as .xlsm then that would be preferable to users encountering errors every time they try to save.
 
Upvote 0
It should have worked unless you changed the file extension, try
Code:
               Me.SaveAs Left(.SelectedItems(1), InStrRev(.SelectedItems(1), ".") - 1), 51
 
Upvote 0
Solution
Following up on the above,
Code:
Me.SaveAs .SelectedItems(1), 52
works with a .xlsm file extension on the new file name.

When I tried .SelectedItems earlier to set the default file type it appeared to be (1) for macro free or (2) for macro enabled so I tried the same again without thinking logically :eek:
 
Upvote 0
It should have worked unless you changed the file extension, try
Code:
               Me.SaveAs Left(.SelectedItems(1), InStrRev(.SelectedItems(1), ".") - 1), 51
Thanks, Fluff!

I made no changes to your code for the first test, which resulted in the rte-1004 that I mentioned earlier, I thought it should have worked but don't fully understand the params used, going to dig around though and see if I can figure it out.

The new line that you suggested works perfectly so I'm off to disect that as well.
 
Upvote 0
Whilst the code is using the .xlsx extension, when you get the dialogue box that may still show .xlsm, so if you don't change it, you get the error.
That last piece, simply strips everything after the last . in the file name, thereby removing the extension.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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
Back
Top