Automatically saving workbook containing macros as a macro-free workbook

[vanhunk]

Automatically saving workbook containing macros as a macro-free workbook:

I have a workbook that contains buttons and macros. I run a macro that populates this workbook with data from other workbooks and then save it without buttons and as a macro-free workbook.
However, when it gets to the “saving as” bit I get the following message box with options “Yes”, “No” and “Help”. I want to avoid this prompt and the option to save it in another format by always, automatically choosing the “Yes” option. Can this be done?

I get the following prompt:

The following features cannot be saved in macro-free workbooks:
· VB project
To save a file with these features, click No, and then choose a macro-enabled file type in the File Type list.
To continue saving as a macro-free workbook, click Yes.

   'FileFormat 51 = xlOpenXMLWorkbook( without macros in 2007-2013):
            ActiveWorkbook.SaveAs FileName:=NewBookName, _
              FileFormat:=51, _
              PassWord:="", _
              WriteResPassword:="", _
              ReadOnlyRecommended:=False, _
              CreateBackup:=False

Best regards,
vanhunk

[holycow]

sub test()
application.displayalerts = false
' your code here
application.displayalerts = true
end sub

[snb]

I'd prefer to use a copy of the workbook:

Sub M_snb()
    Application.DisplayAlerts = False
    
    ThisWorkbook.Sheets.Copy
    With Workbooks(Workbooks.Count)
        .SaveAs ThisWorkbook.Path & "\copy_001.xlsx", 51
        .Close
    End With
    
    Application.DisplayAlerts = True
End Sub

[vanhunk]

@holycow & snb,

Thank you for the replies. The very first thing I tried was to use "Application.DisplayAlerts = False" but for some or other reason the macro did not complete. Don't know why, but it is now working fine - well almost.

Is it possible to be selective, i.e. can the code be changed to only show the following alert?:

"A file named '*********************************************' already exists in this location. Do you want to replace it?
"Yes" "No" "Cancel"

Regards,
vanhunk

snb,

Why do you prefer the copy route?

Thanks,
vanhunk

[SamT]

If Not Dir(ThisWorkbbok.Path) = "" Then
Result = MsgBox "File exists" + buttons as needed
If Result = ? then
Whatever
End If
End If

[snb]

You can check the existence of a file using Dir(file.fullname).
But you'd better use an algoritm to produce a filename that doesn't exist, so the user won't be bothered with tasks that can better be automated.

Otherwise you could 'loose' the file containing the macro.

[vanhunk]

Thank you SamT & snb I appreciate your inputs.

Regards,
vanhunk

[ram.anim]

I'd prefer to use a copy of the workbook:

Sub M_snb()
    Application.DisplayAlerts = False
    
    ThisWorkbook.Sheets.Copy
    With Workbooks(Workbooks.Count)
        .SaveAs ThisWorkbook.Path & "\copy_001.xlsx", 51
        .Close
    End With
    
    Application.DisplayAlerts = True
End Sub

What if i want to save that file in particular location and with custom name like with current date prefix added with file name?

[SamT]

What if i want to save that file in particular location and with custom name like with current date prefix added with file name?

Something like:

Sub SamT_snb() 
    Application.DisplayAlerts = False 
    Dim Pre As String
Pre = Format(Now, "yyyy-mm-dd") & "_"
    ThisWorkbook.Sheets.Copy 
    With Workbooks(Workbooks.Count) 
        .SaveAs ThisWorkbook.Path & "\" & Pre & ThisWorkbook.Name & ".xlsx", 51 
        .Close 
    End With 
     
    Application.DisplayAlerts = True 
End Sub

[ram.anim]

Something like:

Sub SamT_snb() 
    Application.DisplayAlerts = False 
    Dim Pre As String
Pre = Format(Now, "yyyy-mm-dd") & "_"
    ThisWorkbook.Sheets.Copy 
    With Workbooks(Workbooks.Count) 
        .SaveAs ThisWorkbook.Path & "\" & Pre & ThisWorkbook.Name & ".xlsx", 51 
        .Close 
    End With 
     
    Application.DisplayAlerts = True 
End Sub

Thanks SamT. and if i want to have this workboook as readme only files then?

[snb]

Please do not quote.

Just check in the VBEditor, Help ( F1 ), lemma 'SaveAs'