FileDialogSaveAs not saving anything?

[jasonb75]

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.

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.

 

[Fluff]

How about

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

 

[jasonb75]

Thanks, Fluff!

I'm getting RTE-1004 on the line

Me.SaveAs .SelectedItems(1), 51

This extension can not be used with the selected file type.

I tried changing the line above to

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

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.

 

[Fluff]

It should have worked unless you changed the file extension, try

               Me.SaveAs Left(.SelectedItems(1), InStrRev(.SelectedItems(1), ".") - 1), 51

 

[jasonb75]

Following up on the above,

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

 

[jasonb75]

It should have worked unless you changed the file extension, try

               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.

 

[Fluff]

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.

 

[jasonb75]

Great, thanks for the explanation

 

[Fluff]

You're welcome & thanks for the feedback