Macro SaveAs From Macro Enable Template

Question

Hi 

I have the following macro that "Save As" from a normal Macro Enabled Workbook. The problem is that when I use from a Macro Enabled Template Workbook the below Macro does not work, an error always comes up where the bold part is within the below macro

Thanks & Regards 

Owen Burman

Sub savefile()

Dim sFile As String

Dim name As String

sFile = Range("N4")

name = Application.GetSaveAsFilename(sFile, "Excel Macro-Enabled workbook(*.xlsm),*xlsm")

If name = "false" Then

Exit Sub

End If

ActiveWorkbook.saveas (name)

ActiveWorkbook.Close

End Sub

Answer 1

The problem is that your default file format in Excel is XLSX and a SaveAs with no given file format uses that. Please read the help to SaveAs in VBA for details on the file format argument.

So if you create a new file from this template you have a VBProject, which can not be saved in a XLSX file, hence the error.

If you want to suppress this error execute

  Application.DisplayAlerts = False

before you save the file as XLSX

Otherwise use

  ActiveWorkbook.SaveAs name, xlOpenXMLWorkbookMacroEnabled

BTW, "Name" also a VBA method, I recommend to use

  Dim FName As String

instead.

Andreas.

Answer 2

The pop-up which you are showing here gives a bit of a surprise!

Ideally this error message pop-up appears when an excel file containing a macro is NOT saved as a macro file ( I mean .xlsm ). I doubt you might have not done this.

However, pls have a double check if by any chance the file has been saved as a .xlsx

(by the way - what excel version are you using?)

Secondly, as far as your run-time error is concerned, Pls check if you are giving a complete file name (inclusive of folder path and the file name) while using saveas.

I follow the following practice in all my Excel macros:

OutFileNm = "Sales Master FY201718"                        ------ a file name without extension

OutFileNmwe = OutFileNm & ".xlsx"                           ------ patch file extension

OutFilePath = "D:\folderlevel1\folderlevel2\folderlevel3"     --------- complete folder path

OutFullFileName = OutFilePath & "" & OutFileNmwe    ------ Full file name (with path) for file opening,

                                                                                                    saving, closing, etc.

Hope this Helps.

Answer 3

What is the error that you are getting ?

Answer 4

Hi 

First the above pop-ups appears then the following error appears as well.

"Run-Time Error 1004"

"Application Defined Or Object Defined Error"

Thanks & Regards

Owen