PDA

View Full Version : [SOLVED] Automatically saving workbook containing macros as a macro-free workbook



vanhunk
09-05-2014, 07:29 AM
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
09-05-2014, 06:48 PM
sub test()
application.displayalerts = false
' your code here
application.displayalerts = true
end sub

snb
09-07-2014, 02:27 AM
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
09-07-2014, 11:48 PM
@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
09-08-2014, 01:52 AM
If Not Dir(ThisWorkbbok.Path) = "" Then
Result = MsgBox "File exists" + buttons as needed
If Result = ? then
Whatever
End If
End If

snb
09-08-2014, 01:56 AM
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
09-08-2014, 02:13 AM
Thank you SamT & snb I appreciate your inputs.

Regards,
vanhunk

ram.anim
06-15-2015, 08:54 PM
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
06-16-2015, 07:30 AM
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
06-16-2015, 07:36 PM
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
06-17-2015, 01:55 AM
Please do not quote.

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