VBA - Can't get Save As dialog to work

[lneidorf]

Hi there.

I am developing a macro-enabled workbook for distribution to a variety of co-workers. I'm planning on distributing a macro-enabled template (XLTM). I'm struggling with code to do the following:

Upon opening the XLTM file (by simply double-clicking), I'd like the user to be forced to save the file locally as an macro-enabled workbook (.XLSM).

I've taken several stabs at code to bring up the Save As dialog, all of which appear to be functioning as expected, but none of them actually results in the file being saved.

Here's four strings of code I have tried, all placed in the ThisWorkbook object in the midst of a Workbook_Open subroutine:

'ATTEMPT 1:
Application.Dialogs(xlDialogSaveAs).Show "PROJECT NAME - Project Audit (YYYY-MM-DD).xlsm"

'ATTEMPT 2:
Application.GetSaveAsFilename _
InitialFileName:=PROJECT NAME - Project Audit (YYYY-MM-DD).xlsm

'ATTEMPT 3:
Dim bFileSaveAs As Boolean
bFileSaveAs = Application.Dialogs(xlDialogSaveAs).Show
If Not bFileSaveAs Then MsgBox "User cancelled", vbCritical

'ATTEMPT 4:
Dim fNameAndPath As Variant
fNameAndPath = Application.GetSaveAsFilename(InitialFileName:="PROJECT NAME - Project Audit (YYYY-MM-DD).xlsm", fileFilter:="Macro-Enabled Workbook (*.xlsm), *.xlsm", Title:="Save As")
If fNameAndPath = False Then Exit Sub
Me.SaveAs Filename:=fNameAndPath


Help!

I'm using Excel 2007, BTW (if it wasn't already obvious).

Thanks.

 

[bensonsearch]

Hi,
Do you want the user to select where to save?
otherwise you could just call

Thisworkbook.save
 
or
 
thisworkbook.saveas

I beleive if it is a new file it will prompt the user anyways

 

[lneidorf]

Thanks for your response.

I don't think thats going to give me what I want. I'm looking to do two things:
1. force users to save immediately upon opening a new workbook (which is spawned from an xltm template);
2. allow users to save only in xlsm format.

Any thoughts are welcome.

Thanks!

 

[bensonsearch]

HI,
you would need to use the before save event, something like the below two samples

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim fName As String
fName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
If fName = "False" Then
MsgBox "You pressed cancel", vbOKOnly
Cancel = True
End If
Application.EnableEvents = False
ThisWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True
End Sub

or

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim varWorkbookName As String
Dim FileFormatValue As Integer
On Error GoTo Quit
Application.EnableEvents = False
If SaveAsUI = True Then
    varWorkbookName = Application.GetSaveAsFilename( _
    fileFilter:="Excel Macro Enabled Workbook (*.xlsm), *.xlsm")
    Cancel = True
    If varWorkbookName <> "False" Then
        Select Case LCase(Right(varWorkbookName, Len(varWorkbookName) - InStrRev(varWorkbookName, ".", , 1)))
        Case "xlsm": FileFormatValue = 52
        End Select
        
     ActiveWorkbook.SaveAs varWorkbookName
Application.EnableEvents = True
    End If
End If
Quit:
If Err.Number > 0 Then
    If Err.Number <> 1004 Then
MsgBox "Error: " & Err.Number & Err.Description & vbCrLf & vbCrLf & vbCrLf & _
"Title", vbCritical
    End If
End If
End Sub

adjusting as needed of course.

 

[lneidorf]

bensonsearch,

I ended up stumbling upon a workable solution. My code had been very close at times, but not quite there. I think I was confused about the functionality of GetSaveAsFilename / SaveAs and wasn't getting the FileFilter syntax right.

This is what I ended up with:

If ActiveWorkbook.Path = "" Then
        MsgBox "You must save this file before proceeding.     " & vbNewLine & vbNewLine & _
        "This spreadhseet will not operate unless it has been saved.     ", _
        vbExclamation, _
        "Save now, please"
    
        Dim sFileName As String
        sFileName = Application.GetSaveAsFilename( _
            InitialFileName:="PROJECT NAME - Project Audit Report (YYYY-MM-DD).xlsm", _
            FileFilter:="Excel 2007 Macro-Enabled Workbook (*.xlsm), *.xlsm")
        If sFileName = "False" Then Exit Sub
        ThisWorkbook.SaveAs sFileName, FileFormat:=52
    End If

When included within a Workbook_Open() Sub in the ThisWorkbook object, it seems to do the trick.

Thanks for the suggestions. I will definitely experiment with them.

 

[bensonsearch]

HI,
great to see you got it going. no worries with the suggestions, always happy to help