Designate a save as path and file format for a workbook

[demonllama]

Im got a macro taking data from one document (stability file) and reformatting it into another document. It uses cells in the stability file to determine what the name should be. If that name is already taken, I want it to pop up a saveas box in the Source Path (stability file location) and allow us to name the file whatever we want. I also want it to save it in .xlsm format which is what im strugging with. Below are a few different ways I have tried to get it to work.

ActiveSheet.Name = runStr 'save a copy of the templet using test file name in current directory
    wbTarget.Worksheets(1).Range("A1").Select
    On Error Resume Next
        wbTarget.SaveAs wbSource.Path & "/" & saveAsName & ".xlsm", FileFormat:=52
    If Err.Number <> 0 Then
        wbTarget.Worksheets(1).Range("A1").Select ' everything before this works correctly
        wbTarget.SaveAs wbSource.Path
        wbTarget.SaveAs Application.Dialogs(xlDialogSaveAs).Show("*.xlsm")
'        Application.Dialogs(xlDialogSaveAs).Show ("*.xls")
'        wbTarget.SaveAs Application.GetSaveAsFilename 'This works but requires .xlsm to be typed in each time. 
    End If
    On Error GoTo 0

Any help provided would be much help

 

[MrKowz]

Welcome to the forums!

Instead of calling Application.GetSaveAsFilename in the same line as your saveas, you can pass it to a variable where you can do tests on it and manipulate as needed.

For example:

Dim strSaveAs       As Variant
strSaveAs = Application.GetSaveAsFilename
If Right(strSaveAs, 4) <> "xlsm" Then
    Select Case Right(strSaveAs, 1)
        Case "."
            strSaveAs = strSaveAs & "xlsm"
        Case Else
            strSaveAs = strSaveAs & ".xlsm"
    End Select
End If
ActiveWorkbook.SaveAs strSaveAs

 

[demonllama]

Thank you for your response.

I tried adding that to my code (too be honest, I am completely unsure of what that's doing since the previous person who wrote this code has nothing like that in there) but not it pops up the save as box twice and both times its format is "." instead of .xlsm. Anyway, this is how I tried to put it in.

Dim strSaveAs       As Variant
strSaveAs = Application.GetSaveAsFilename
    ActiveSheet.Name = runStr 'save a copy of the templet using test file name in current directory
    wbTarget.Worksheets(1).Range("A1").Select
    On Error Resume Next
        wbTarget.SaveAs wbSource.Path & "/" & saveAsName & ".xlsm", FileFormat:=52
    If Err.Number <> 0 Then
        wbTarget.Worksheets(1).Range("A1").Select
'        wbTarget.SaveAs wbSource.Path
'        wbTarget.SaveAs wbSource.Application.Dialogs(xlDialogSaveAs).Show("*.xlsm")
'        Application.Dialogs(xlDialogSaveAs).Show ("*.xls")
        If Right(strSaveAs, 4) <> "xlsm" Then
            Select Case Right(strSaveAs, 1)
                Case "."
                    strSaveAs = strSaveAs & "xlsm"
                Case Else
                    strSaveAs = strSaveAs & ".xlsm"
            End Select
        End If
'    wbTarget.SaveAs Application.GetSaveAsFilename & ".xlsm"
    End If
    On Error GoTo 0

Im not trying to do anything fancy with it, but if the file name that it is programmed to save it as is taken, I want a save as box to pop up in the same location as the Source document and in .xlsm format. I can both to happen but not at the same time and that's what im struggling with.

Michael