SaveAs with default Filename from cell, And setting file type

[High Plains Grifter]

Hi all,

I am having a bit of a problem with a bit of VBA. The context is that I have made a template which users will use as a request form for new reports. This template contains a macro. I will email a link to this template to users, but I want to stop them from saving their requests over the template. To this end, I figured I would run a macro on save to show the XLDialogSaveAs, but I have not been able to set the type as '.xlsm' (FileFormat number 52). If the default format is used, the macros are obviously not saved.

I have tried all sorts of variations, but to no avail; Excel either ignores my attempts to control the format, puts inverted commas around the filename, crashes or does nothing. So I come to you. The latest iteration is as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sDirectory As String
Dim sFilename As String
Dim instance As XlFileFormat

instance = ActiveWorkbook.FileFormat

sFilename = ActiveSheet.Range("C2").Value
sDirectory = "P:\GoTrex\Configuration Live\User Change Requests\Reports\"

ActiveWorkbook.SaveAs Filename:=sDirectory & sFilename, FileFormat:=instance
End Sub

This works... In a way - Excel saves the file with the right name, in the right place and with the right extension, but I have lost the dialog box (It caused problems as well so I tried without it) so the user cannot change the name (I guess I could bring up an input box to get around this, but it is messy) and worst of all, Excel crashes every time I save. Excel 2010 automatically reopens my newly saved-as file, but crashes are not part of the plan!

Any ideas?

By the way, this one works as well... in a way

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.Dialogs(xlDialogSaveAs).Show ("P:\GoTrex\Configuration Live\User Change Requests\Reports\" & ActiveSheet.Range("C2").Value & ".xslm")
End Sub

This one brings up the dialog, but the default filename is with the extension, in inverted commas. If I click OK, the file is saved with the right filetype, but again, Excel crashes. When I auto recover, I see a file called (for example) filename.xlsm.xlsm

Can't help thinking this should be easy...

 

[High Plains Grifter]

Ok, so this works, other than that it crashes Excel:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.Dialogs(xlDialogSaveAs).Show ActiveSheet.Range("C2").Value, 52
End Sub

It seems that Excel is trying to run the macro twice... maybe the first time is on save, and the second time is on the save that is instigated by the macro itself. The crash happens AFTER the worksheet is correctly saved, so it would seem that this second run is what kills it. The question then becomes "how do I stop the macro from running twice?".

Of course, this train of thought may be totally wrong, in which case the question is "Why does it crash and how do I stop it from doing so?"

 

[High Plains Grifter]

More Info I used F8 to check the steps this ran:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)   '1
Dim DefaultName As String                                                       '2
DefaultName = ActiveSheet.Range("C2").Value                                     '3
SaveMe DefaultName                                                              '4
Exit Sub                                                                        '5
End Sub                                                                         '6

Sub SaveMe(DefaultName As String)                                               '7
    Application.Dialogs(xlDialogSaveAs).Show DefaultName, 52                    '8
End Sub

The sequence of lines and actions was as follows, when the macro ran:
1
3
4
-----
7
8
-----
1
3
4
-----
7
8
9
-----
5
Dialog box appears
Click save in the dialog
-----
9
-----
5
then crash


Hopefully this may help...

 

[High Plains Grifter]

No one?