Creating a Xlsm file with a Modulem using VBA

teranprasanna
Lounger
Posts: 27
Joined: 24 Jun 2020, 01:39

Creating a Xlsm file with a Modulem using VBA

Post by teranprasanna »

Dears,

Appreciate if anyone can help me for this..

I have a main macro workbook, I need to create a another macro enabled xlsm file with a module ( inserting a preset code in new file) using a VBA code in main file, Can you please help me.

Regards,
Teran

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating a Xlsm file with a Modulem using VBA

Post by HansV »

Where does the code for the module come from? Do you want to include it in the macro, or import it from a text file. or ...?
Best wishes,
Hans

teranprasanna
Lounger
Posts: 27
Joined: 24 Jun 2020, 01:39

Re: Creating a Xlsm file with a Modulem using VBA

Post by teranprasanna »

HansV wrote:
16 Mar 2021, 07:51
Where does the code for the module come from? Do you want to include it in the macro, or import it from a text file. or ...?
Hi Hans,

Thanks for your reply.

Code needs to be included in macro.

Appreciate your support!

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating a Xlsm file with a Modulem using VBA

Post by HansV »

Before doing this, select File > Options > Trust Center > Trust Center Settings... > Macro Settings.
Make sure that the check box 'Trust access to the VBA project object model' is ticked, then click OK.

Use the following as starting point and edit it as you like.

Code: Select all

Sub CreateXLSM()
    Dim wbk As Workbook
    Dim vbc As Object ' VBComponent
    Dim mdl As Object ' CodeModule
    Set wbk = Workbooks.Add(xlWBATWorksheet)
    Set vbc = wbk.VBProject.VBComponents.Add(1) ' vbext_ct_StdModule
    Set mdl = vbc.CodeModule
    mdl.AddFromString _
        "Sub Test()" & vbCrLf & _
        "    MsgBox ""Hello World!"", vbInformation" & vbCrLf & _
        "End Sub"
    wbk.SaveAs Filename:="Test.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    wbk.Close
End Sub
Best wishes,
Hans

teranprasanna
Lounger
Posts: 27
Joined: 24 Jun 2020, 01:39

Re: Creating a Xlsm file with a Modulem using VBA

Post by teranprasanna »

HansV wrote:
16 Mar 2021, 16:40
Before doing this, select File > Options > Trust Center > Trust Center Settings... > Macro Settings.
Make sure that the check box 'Trust access to the VBA project object model' is ticked, then click OK.

Use the following as starting point and edit it as you like.

Code: Select all

Sub CreateXLSM()
    Dim wbk As Workbook
    Dim vbc As Object ' VBComponent
    Dim mdl As Object ' CodeModule
    Set wbk = Workbooks.Add(xlWBATWorksheet)
    Set vbc = wbk.VBProject.VBComponents.Add(1) ' vbext_ct_StdModule
    Set mdl = vbc.CodeModule
    mdl.AddFromString _
        "Sub Test()" & vbCrLf & _
        "    MsgBox ""Hello World!"", vbInformation" & vbCrLf & _
        "End Sub"
    wbk.SaveAs Filename:="Test.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    wbk.Close
End Sub

Thank you & really appreciate it... :clapping: