Create a macro-enabled workbook through VB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Back
Top