Create a macro-enabled workbook through VB

[Oz8425]

I'm looking to create a new macro-enabled workbook (extension .xlsm) by using
a macro in a different workbook. I only know how to create a workbook with
extension .xls and when I attempt to create a .xlsm I get an error.

Any help would be appreciated.

 

[Ron de Bruin]

Hi Oz8425

Try this

Sub Test()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim WB As Workbook
Dim TempFilePath As String
Dim TempFileName As String

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set WB = Workbooks.Add
FileExtStr = ".xlsm": FileFormatNum = 52

'Save the new workbook and close it
TempFilePath = Application.DefaultFilePath & "\"
TempFileName = "NewWB " & Format(Now, "dd-mmm-yy h-mm-ss")

With WB
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
.Close SaveChanges:=False
End With

MsgBox "You can find the new file in " & Application.DefaultFilePath

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


See also
http://www.rondebruin.nl/saveas.htm