How to Create a Macro Enabled Excel File - Help!!!

[munderwood4GRE6]

I'm trying to export assembly info into a newly created excel file using a macro enabled excel template.

I get the following error.

The code I have seems to run just fine when I'm using a regular excel template.

myXLS_File = "C:\...FilePath.xls"

excelWorkbook =  excelApp.Workbooks.Add (Template: = "C:\Temp\Best_Excel_Template_Ever.xlt")

excelWorkbook.SaveAs (myXLS_File)

But when I use .xlsm and .xltm file extensions for macro enabled excel docs I get the error.

Any help is greatly appreciated!

[A.Acheson]

I think the problem lies in that you need to switch the file format to .xlsm and not simply put that in the file name. In testing manually  saving a macro from a macro template it defaults to .xlsx file type instead of .xlsm, user has to change the format type manually. So changing the format through code is required also. Here is a link to retrieve the integer for  file format.

Change the template path in the below code

Imports System.Windows.Forms
Imports System.IO
Imports Microsoft.Office.Interop.Excel        'To use excel

Sub Main
	Dim oMacroTemplate as String ="Enter fullfilename.xltm"
        Dim fName As String = ThisDoc.PathAndFileName 	Dim xlwb As Workbook
	Dim xlws As Worksheet
	
	'Start a new instance of Excel, make visible and disable alerts
	Dim excelApp As Object
	excelApp = CreateObject("Excel.Application")
	excelApp.Visible = True
	excelApp.DisplayAlerts = True
	

	'Open Excel template
	Try
	
 	xlwb = excelApp.Workbooks.Add(Template :=oMacroTemplate)	
	'xlwb = excelApp.Workbooks.Add (oMacroTemplate)'This works also
 	
	xlwb._SaveAs(FileName := fName, FileFormat := 52)'xlOpenXMLWorkbookMacroEnabled- using this text does not work in place of integer	'or
	'xlwb._SaveAs(fName, 52)'This works also
	
	'Syntax
	'SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
	Catch
		MessageBox.Show("Cannot find XL File: Exiting", "iLogic")
	Exit Sub
	End Try
		
End Sub