CODEWITHSUNDEEP
excelvbams-word
Holger Nielsen
Holger Nielsen

Reputation: 7

Creating a macro-enabled Excel file from within Word VBA

The following code, when used within Word VBA, successfully creates a standard Excel workbook in the chosen folder:

Const ExcelSourcePath = "C:\Users\Holge\_Universet i billeder\_ExcelDocs\"
Dim xlAppl As New Excel.Application
Dim xlBook As New Excel.Workbook

Private Sub TestCreateExcelFile()
    Set xlAppl = CreateObject("Excel.Application")
    Set xlBook = xlAppl.Workbooks.Add
    Application.DisplayAlerts = False
    xlBook.SaveAs FileName:=ExcelSourcePath & "TestFile.xlsx"
    Application.DisplayAlerts = True
    xlBook.Close False
    Set xlBook = Nothing
    xlAppl.Quit
    Set xlAppl = Nothing
End Sub

However, what I really need is to create a macro-enabled file. But if I change ".xlsx" into ".xlsm" I get a RTE 1004, "Wrong file type name" (translated from Danish). Perhaps CreateObject should be called with another argument, but which one? I have not been alble to find possible values for this argument.

Upvotes: 0

Views: 114

Answers (2)

Thomas Stillwell
Thomas Stillwell

Reputation: 79

Like this:

Const ExcelSourcePath = "C:\Users\Holge\_Universet i billeder\_ExcelDocs\"
Dim xlAppl As Excel.Application
Dim xlBook As Excel.Workbook

Private Sub TestCreateExcelFile()
    Set xlAppl = CreateObject("Excel.Application")
    Set xlBook = xlAppl.Workbooks.Add
    Application.DisplayAlerts = False
    xlBook.SaveAs FileName:=ExcelSourcePath & "TestFile.xlsx",_
                  FileFormat:=xlOpenXMLWorkbookMacroEnabled
    Application.DisplayAlerts = True
    xlBook.Close False
    Set xlBook = Nothing
    xlAppl.Quit
    Set xlAppl = Nothing
End Sub

Upvotes: 1

Eugene Astafiev
Eugene Astafiev

Reputation: 49397

In the following code:

Dim xlAppl As New Excel.Application
Dim xlBook As New Excel.Workbook

Private Sub TestCreateExcelFile()
    Set xlAppl = CreateObject("Excel.Application")
    Set xlBook = xlAppl.Workbooks.Add

A new Excel Application instance is created twice. You need to keep the one line of code for create a new instance, not doing that twice.

Moreover, an instance of the Workbook class can't be created by using the New operator like shown in your code:

Dim xlBook As New Excel.Workbook

Most probably the intention was to declare objects out of the sub and then instantiate them:

Const ExcelSourcePath = "C:\Users\Holge\_Universet i billeder\_ExcelDocs\"
Dim xlAppl As Excel.Application
Dim xlBook As Excel.Workbook

Private Sub TestCreateExcelFile()
    Set xlAppl = CreateObject("Excel.Application")
    Set xlBook = xlAppl.Workbooks.Add
    Application.DisplayAlerts = False
    xlBook.SaveAs FileName:=ExcelSourcePath & "TestFile.xlsx"
    Application.DisplayAlerts = True
    xlBook.Close False
    Set xlBook = Nothing
    xlAppl.Quit
    Set xlAppl = Nothing
End Sub

Just need to remove New operators from the code with declarations.

Upvotes: 1

Related Questions