Add userform to excel workbook using script

[Guest]

I am able to add code modules to excel, creating Excel macros via VBScript.
Is it possible to add Excel Userforms the same way? Here is my code below:

Dim Code, objXL, Workbook, Worksheet, Macros dim oFSO, oFile,f
Const ForReading = 1, ForWriting = 2, ForAppending = 8

Set fso = CreateObject("Scripting.FileSystemObject")


Set f = fso.OpenTextFile("c:\KFA\Book1.txt", ForReading) 'Read the text file
Code = f.Read(1500)'1500 denotes the number of characters
Set objXL = CreateObject("Excel.Application")
Set Workbook = objXL.Workbooks.Open("C:\KFA\BOOK1.XLS")
Set Worksheet = Workbook.Sheets(1)
'Add ref to macro position
Set Macros = Workbook.VBProject.VBComponents(1).CodeModule
'Add new macro; AddFromFile is also an option

Macros.AddFromString Code





' Save the result
objXL.Save
Set objXL = Nothing

 

[Chip Pearson]

Here's some code that should get you started. See also
www.cpearson.com/excel/vbe.htm . You'll need a reference to the MS VBA
Extensibility library.


Dim VBP As VBIDE.VBProject
Dim UF As VBIDE.VBComponent
Dim Ctrl As MSForms.Control
Dim LineNum As Long
Set VBP = ThisWorkbook.VBProject
Set UF = VBP.VBComponents.Add(vbext_ct_MSForm)
Set Ctrl = UF.Designer.Controls.Add("Forms.CommandButton.1")
Ctrl.Top = 100
Ctrl.Left = 100
Ctrl.Caption = "Click Me"
Ctrl.Name = "MyButton"
LineNum = UF.CodeModule.CreateEventProc("Click", Ctrl.Name)
UF.CodeModule.InsertLines LineNum + 1, "Msgbox ""Hello World"""



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"kalbrecht1972_hotmail_com"

 

[Guest]

Thanks for the reply Chip -

But I need to do this in VBScript, ala DTS SQL 2000, so I cannot reference
the extendibility library like that. Could I modify to use in AxtiveX
Script? - Thanks

 

[Chip Pearson]

For everything declared As VBIDE.whatever, use As Object. Then change
vbext_ct_MSForm to a 3, and you should be all set.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"kalbrecht1972_hotmail_com"

 

[Guest]

Thanks for your time Chip,

I have gotten your code to work with some modification, using
CreateObject("Excel.Application") when setting my objects.

My ideal solution would be to "Import" a userform from a file, as opposed to
re-creating all of the code objects.