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
Creating a Xlsm file with a Modulem using VBA
-
- Lounger
- Posts: 27
- Joined: 24 Jun 2020, 01:39
-
- 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
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
Hans
-
- Lounger
- Posts: 27
- Joined: 24 Jun 2020, 01:39
-
- 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
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.
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
Hans
-
- Lounger
- Posts: 27
- Joined: 24 Jun 2020, 01:39
Re: Creating a Xlsm file with a Modulem using VBA
HansV wrote: ↑16 Mar 2021, 16:40Before 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...