Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add userform to excel workbook using script
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add userform to excel workbook using script
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add userform to excel workbook using script
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" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add userform to excel workbook using script
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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add userform to excel workbook using script
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. "Chip Pearson" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving Excel 2003 Workbook in Excel 07 - Error Msg on Script obj | Excel Discussion (Misc queries) | |||
Update a workbook from a script | Excel Discussion (Misc queries) | |||
can share vba script using different workbook? | Excel Programming | |||
Excel 2000/XP script to Excel97 script | Excel Programming | |||
keeping VBA script with a workbook | Excel Programming |