Create a Save as dialogue box

[Armadillos]

Hi

I am looking to create a Save As dialogue box in VBA. I simply want to recreate the window that you get if your choose Save As from the File Menu, i.e. it has the the same file name as the existing file in File name:' box (so it can be slightly altered manually) and the same file type in the 'Save as type:' box.

Thanks in advance

 

[Domenic]

Try the following...

Sub test()

    Dim fileFilter As String
    Dim fileFormat As String
    Dim saveAsFileName As Variant
    
    Select Case ActiveWorkbook.fileFormat
        Case 51
            fileFilter = "Excel Workbook (*.xlsx), *.xlsx"
            fileFormat = xlOpenXMLWorkbook
        Case 52
            fileFilter = "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm"
            fileFormat = xlOpenXMLWorkbookMacroEnabled
    End Select
    
    saveAsFileName = Application.GetSaveAsFilename( _
        InitialFileName:=ActiveWorkbook.Name, _
        fileFilter:=fileFilter, _
        Title:="Save As", _
        ButtonText:="Save")
        
    If saveAsFileName = False Then Exit Sub
    
    ActiveWorkbook.SaveAs Filename:=saveAsFileName, fileFormat:=fileFormat

Note that you can amend the Select Case statement to include other file formats.

Hope this helps!

 

[Armadillos]

Perfect. Thank you.

 

[Armadillos]

Hi again

Sorry, I've noticed one issue. Whilst it is saving the new file (lets say Rev 2) as required, the workbook that is still open in excel, is the earlier version (Rev 1). Is it possible for the open workbook to be the latest version that has been saved.

 

[Domenic]

Sorry, my mistake, while we prompted the user for the SaveAs filename, we didn't actually save the workbook. I've edited my post to make this correction.

Hope this helps!

 

[Carl Stephens]

Hi

I am looking to create a Save As dialogue box in VBA. I simply want to recreate the window that you get if your choose Save As from the File Menu, i.e. it has the the same file name as the existing file in File name:' box (so it can be slightly altered manually) and the same file type in the 'Save as type:' box.

Thanks in advance

 

[Armadillos]

Sorry, my mistake, while we prompted the user for the SaveAs filename, we didn't actually save the workbook. I've edited my post to make this correction.

Hope this helps!

Thanks Domenic

 

[Domenic]

Thanks Domenic

You're very welcome, cheers!