Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add code module to Excel programmatically
I need to add a code module to Excel from Access via automation.
I've looked at these 2 sites for help: http://www.j-walk.com/ss/excel/tips/tip96.htm http://www.cpearson.com/excel/vbe.htm For starters, I'm trying to simply add a module to an Excel workbook from a standard Excel module. The code below returns this: This workbook has 6 modules. Error Number 440: Method 'Add' of object '_VBComponents' failed Is there something wrong with the way I'm instantiating newmod? Any guidance would be appreciated. Thanks in advance. Public Function AddModuleToExcel() On Error GoTo HandleErr Dim intCt As Integer Dim vbp As Object Dim newmod As Object If Val(Application.Version) = 10 Then Set vbp = ActiveWorkbook.VBProject If Err.Number < 0 Then MsgBox "Your security settings do not allow this procedure to run." _ & vbCrLf & vbCrLf & "To change your security setting:" _ & vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." & vbCrLf _ & " 2. Click the 'Trusted Sources' tab" & vbCrLf _ & " 3. Place a checkmark next to 'Trust access to Visual Basic Project.'", _ vbCritical Exit Function Else intCt = vbp.VBComponents.Count Debug.Print "This workbook has " & intCt & " modules." Set newmod = vbp.VBComponents.Add(vbext_ct_StdModule) newmod.Name = "MyNewModule" intCt = vbp.VBComponents.Count Debug.Print "This workbook has " & vbpCt & " modules." End If End If Exit_He Exit Function HandleErr: Debug.Print "Error Number " & Err.Number & ": "; Err.Description End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add code module to Excel programmatically
I tried this from Excel, and after renaming the variable vbpCpt to intCpt,
it ran fine for me. If running this from Excel, you need to set a variable object to the Excel app, and qualify each Excel object with that app object. Not sure if that applies equally to VBE objects, never tried it from another app, but try it anyways. -- HTH RP (remove nothere from the email address if mailing direct) "deko" wrote in message . .. I need to add a code module to Excel from Access via automation. I've looked at these 2 sites for help: http://www.j-walk.com/ss/excel/tips/tip96.htm http://www.cpearson.com/excel/vbe.htm For starters, I'm trying to simply add a module to an Excel workbook from a standard Excel module. The code below returns this: This workbook has 6 modules. Error Number 440: Method 'Add' of object '_VBComponents' failed Is there something wrong with the way I'm instantiating newmod? Any guidance would be appreciated. Thanks in advance. Public Function AddModuleToExcel() On Error GoTo HandleErr Dim intCt As Integer Dim vbp As Object Dim newmod As Object If Val(Application.Version) = 10 Then Set vbp = ActiveWorkbook.VBProject If Err.Number < 0 Then MsgBox "Your security settings do not allow this procedure to run." _ & vbCrLf & vbCrLf & "To change your security setting:" _ & vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." & vbCrLf _ & " 2. Click the 'Trusted Sources' tab" & vbCrLf _ & " 3. Place a checkmark next to 'Trust access to Visual Basic Project.'", _ vbCritical Exit Function Else intCt = vbp.VBComponents.Count Debug.Print "This workbook has " & intCt & " modules." Set newmod = vbp.VBComponents.Add(vbext_ct_StdModule) newmod.Name = "MyNewModule" intCt = vbp.VBComponents.Count Debug.Print "This workbook has " & vbpCt & " modules." End If End If Exit_He Exit Function HandleErr: Debug.Print "Error Number " & Err.Number & ": "; Err.Description End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add code module to Excel programmatically
I tried this from Excel, and after renaming the variable vbpCpt to intCpt,
it ran fine for me. What did you try? There was no variable named vbpCpt in my code. If running this from Excel, you need to set a variable object to the Excel app, and qualify each Excel object with that app object. Do you mean "if running this from Access"... ?? Not sure if that applies equally to VBE objects, never tried it from another app, but try it anyways. hmmm... do I need to instantiate VBE objects differently? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add code module to Excel programmatically
"deko" wrote in message m... I tried this from Excel, and after renaming the variable vbpCpt to intCpt, it ran fine for me. What did you try? There was no variable named vbpCpt in my code. Okay, so I got the name slightlyu wrong. This is the code, it should have been vbpCt Debug.Print "This workbook has " & vbpCt & " modules." If running this from Excel, you need to set a variable object to the Excel app, and qualify each Excel object with that app object. Do you mean "if running this from Access"... ?? Or an other automation client. Not sure if that applies equally to VBE objects, never tried it from another app, but try it anyways. hmmm... do I need to instantiate VBE objects differently? Don't know as I said I have never done it, but I would expect so. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add code module to Excel programmatically
Ah, I see...
Debug.Print "This workbook has " & vbpCt & " modules." Yes, that was a mistake in my code... should be intCt Still, the problem I had was instantiating newmod. You say that code instantiated newmod for you? I have that code in a standard module named "Module1". I tried putting the code in "ThisWorkbook" as well as "Sheet1" but then the code would not run (from the immediate window using ?AddModuleToExcel). I tried the below revised code can got this: This workbook has 5 modules. Error Number 440: Method 'Add' of object '_VBComponents' failed Error Number 91: Object variable or With block variable not set This workbook has 5 modules. What I'm expecting to see is this: This workbook has 5 modules. This workbook has 6 modules. And then, of course, see an additional module in the project from the IDE. The next step would be finding a way to get the actual code in the module, and also getting the code to run via automation (as you mentioned, this should simply be a matter of creating an object to hold reference to the Excel application object and using something like xlapp.vbp = Active Workbook.VBProject). Public Function AddModuleToExcel() On Error GoTo HandleErr Dim intCt As Integer Dim vbp As Object Dim newmod As Object If Val(Application.Version) = 10 Then Set vbp = ActiveWorkbook.VBProject If Err.Number < 0 Then MsgBox "Your security settings do not allow this procedure to run." _ & vbCrLf & vbCrLf & "To change your security setting:" _ & vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." & vbCrLf _ & " 2. Click the 'Trusted Sources' tab" & vbCrLf _ & " 3. Place a checkmark next to 'Trust access to Visual Basic Project.'", _ vbCritical Exit Function Else intCt = vbp.VBComponents.Count Debug.Print "This workbook has " & intCt & " modules." Set newmod = vbp.VBComponents.Add(vbext_ct_StdModule) 'Set newmod = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_S tdModule) newmod.Name = "MyNewModule" intCt = vbp.VBComponents.Count Debug.Print "This workbook has " & intCt & " modules." End If End If Exit_He Exit Function HandleErr: Select Case Err.Number Case Else Debug.Print "Error Number " & Err.Number & ": "; Err.Description Resume Next End Select End Function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add code module to Excel programmatically
Got it working. I put the below code in a standard module named "Module1"
and run it from the immediate window: ?AddMod This workbook has 6 modules. This workbook has 7 modules. This KB helped: http://support.microsoft.com/?kbid=245801 The key was setting a reference to Microsoft Visual Basic for Applications Extensibility. But this raises a new question: Can I programmatically set the reference? My code prompts the user to change security settings if necessary, which gets around one potential hurdle, but how do I set the required reference? Public Function AddMod() On Error GoTo HandleErr Dim vbcoms As VBComponents Dim vbp As Object Dim objMod As Object Dim intCt As Integer If Val(Application.Version) = 10 Then Set vbp = ActiveWorkbook.VBProject If Err.Number < 0 Then MsgBox "Your security settings do not allow this procedure to run." _ & vbCrLf & vbCrLf & "To change your security setting:" _ & vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." & vbCrLf _ & " 2. Click the 'Trusted Sources' tab" & vbCrLf _ & " 3. Place a checkmark next to 'Trust access to Visual Basic Project.'", _ vbCritical Exit Function Else Set vbcoms = Application.VBE.ActiveVBProject.VBComponents intCt = vbcoms.Count Debug.Print "This workbook has " & intCt & " modules." Set objMod = vbcoms.Add(vbext_ct_StdModule) objMod.Name = "MyNewModule" intCt = vbcoms.Count Debug.Print "This workbook has " & intCt & " modules." End If End If Exit_He Exit Function HandleErr: Select Case Err.Number Case Else Debug.Print "Error Number " & Err.Number & ": "; Err.Description Resume Next End Select End Function 'http://www.j-walk.com/ss/excel/tips/tip96.htm 'http://www.cpearson.com/excel/vbe.htm 'http://support.microsoft.com/?kbid=245801 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add code module to Excel programmatically
Third time's a charm...
The below code adds a standard module to Book1.xls from Access via automation. This assumes that "Trust access to Visual Basic Project" is checked on the Trusted Publishers tab of the Security Dialog in Excel (accessed from Tools Macro Security). I've omitted the code that alerts the user with a message box if this is not the case. The reference I was worried about (Microsoft Visual Basic for Applications Extensibility) I don't need in the Excel workbook - I just set it in Access (which I have control over). Now if I can just figure out how to get my code into the module... Public Function AddMod() On Error GoTo HandleErr Dim xlapp As Excel.Application Dim strXlsPath As String Dim strXlsFile As String Dim intCt As Integer strXlsPath = "C:\Book1.xls" strXlsFile = "Book1.xls" Set xlapp = CreateObject("Excel.Application") xlapp.Workbooks.Open (strXlsPath) intCt = xlapp.VBE.ActiveVBProject.VBComponents.Count Debug.Print strXlsFile & " has " & intCt & " modules." xlapp.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule) intCt = xlapp.VBE.ActiveVBProject.VBComponents.Count Debug.Print strXlsFile & " has " & intCt & " modules." Exit_He xlapp.Workbooks(strXlsFile).Save xlapp.Workbooks(strXlsFile).Close xlapp.Quit Set xlapp = Nothing Exit Function HandleErr: Select Case Err.Number Case Else Debug.Print "Error Number " & Err.Number & ": "; Err.Description Resume Next End Select End Function |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add code module to Excel programmatically
You don't need to set a reference to that library, that is early binding, it
is just as simple with late binding. Just change this line Set newmod = vbp.VBComponents.Add(vbext_ct_StdModule) to Set newmod = vbp.VBComponents.Add(1) To add code, try something like '---------------------------------------------------------------- Sub AddModuleProc() '---------------------------------------------------------------- Dim StartLine As Long Dim cLines As Long With ActiveWorkbook.VBProject.VBComponents("Module2").C odeModule cLines = .CountOfLines + 1 .InsertLines cLines, _ "Sub myProc()" & Chr(13) & _ " Msgbox ""myProc installed"" " & Chr(13) & _ "End Sub" End With End Sub adapted to your code of course. -- HTH RP (remove nothere from the email address if mailing direct) "deko" wrote in message ... Third time's a charm... The below code adds a standard module to Book1.xls from Access via automation. This assumes that "Trust access to Visual Basic Project" is checked on the Trusted Publishers tab of the Security Dialog in Excel (accessed from Tools Macro Security). I've omitted the code that alerts the user with a message box if this is not the case. The reference I was worried about (Microsoft Visual Basic for Applications Extensibility) I don't need in the Excel workbook - I just set it in Access (which I have control over). Now if I can just figure out how to get my code into the module... Public Function AddMod() On Error GoTo HandleErr Dim xlapp As Excel.Application Dim strXlsPath As String Dim strXlsFile As String Dim intCt As Integer strXlsPath = "C:\Book1.xls" strXlsFile = "Book1.xls" Set xlapp = CreateObject("Excel.Application") xlapp.Workbooks.Open (strXlsPath) intCt = xlapp.VBE.ActiveVBProject.VBComponents.Count Debug.Print strXlsFile & " has " & intCt & " modules." xlapp.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule) intCt = xlapp.VBE.ActiveVBProject.VBComponents.Count Debug.Print strXlsFile & " has " & intCt & " modules." Exit_He xlapp.Workbooks(strXlsFile).Save xlapp.Workbooks(strXlsFile).Close xlapp.Quit Set xlapp = Nothing Exit Function HandleErr: Select Case Err.Number Case Else Debug.Print "Error Number " & Err.Number & ": "; Err.Description Resume Next End Select End Function |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add code module to Excel programmatically
You don't need to set a reference to that library, that is early binding,
it is just as simple with late binding. Just change this line Set newmod = vbp.VBComponents.Add(vbext_ct_StdModule) to Set newmod = vbp.VBComponents.Add(1) Yes, but I have control over the Access app. I'm not sure there's any advantage to late binding in this case. I like to use early binding where I can - it's like getting something for free. Although references can be a pain in the neck, to be sure. As for adding code, I figured out how to add code to a standard module (see below). But what I need to do is put a button on worksheet that launches a user form with some controls with which users can run various "what if" scenarios - by changing formulas, adding highlighting, etc. So the user form will have (among other things) an input box for users to enter a 'control value' which sets a ceiling, max variance, etc. All this code will make for a very long string to insert. And how would I add the user form? The other thing that could be tricky is that I will be creating an unknown number of worksheets - which I will not know the names until Access inserts them into the workbook. Am I getting too ambitious with automation here? I appreciate any thoughts you care to offer. [Access code to add Excel code] Private Sub AddModule(xlapp As Excel.Application) On Error GoTo HandleErr Dim str1 As String Dim lngLn As Long xlapp.VBE.ActiveVBProject.VBComponents.Add(vbext_c t_StdModule).Name = _ "AdvancedSettings" lngLn = xlapp.VBE.ActiveVBProject.VBComponents("AdvancedSe ttings") _ .CodeModule.CountOfLines + 1 xlapp.VBE.ActiveVBProject.VBComponents("AdvancedSe ttings") _ .CodeModule.InsertLines lngLn, _ "Private Sub MyNewProcedure()" & Chr(13) & _ " Msgbox" & Chr(34) & "Here is the new procedure" & Chr(34) & _ Chr(13) & "End Sub" Exit_He Exit Sub HandleErr: Select Case Err.Number Case Else str1 = "Error Number " & Err.Number & _ " [basWorksheets.AddModule]: " & Err.Description Call basHandler.Logger(str1) End Select Resume Exit_Here End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add code module to Excel programmatically
-- HTH RP (remove nothere from the email address if mailing direct) "deko" wrote in message m... Yes, but I have control over the Access app. I'm not sure there's any advantage to late binding in this case. I like to use early binding where I can - it's like getting something for free. Although references can be a pain in the neck, to be sure. Which is why I suggested late binding, it overcomes those problems. As for adding code, I figured out how to add code to a standard module (see below). But what I need to do is put a button on worksheet that launches a user form with some controls with which users can run various "what if" scenarios - by changing formulas, adding highlighting, etc. So the user form will have (among other things) an input box for users to enter a 'control value' which sets a ceiling, max variance, etc. All this code will make for a very long string to insert. And how would I add the user form? Why not just create a template workbook with a userform. To add the button is easy '----------------------------------------------------------------- Sub CreateControlButton() '----------------------------------------------------------------- Dim oWs As Worksheet Dim oOLE As OLEObject Set oWs = ActiveSheet Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", _ Left:=200, Top:=100, Width:=80, Height:=32) With oOLE .Object.Caption = "Run myMacro" .Name = "myMacro" End With With ThisWorkbook.VBProject.VBComponents(oWs.CodeName). CodeModule .InsertLines .CreateEventProc("Click", oOLE.Name) + 1, _ vbTab & "Userform1.Show" End With End Sub The other thing that could be tricky is that I will be creating an unknown number of worksheets - which I will not know the names until Access inserts them into the workbook. With xlApp .SheetsInNewWorkbook = 4 .Workbooks.Add For i = 1 To .Activeworkbook.Worksheets.Count 'etc Next i End With |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add code module to Excel programmatically
Why not just create a template workbook with a userform.
And do things the easy way??!! Unfortunately, that would require users to do something differently, and politics being as they are, that's not an option. The thing I'm concerned about is having to add code to every worksheet - that would require looping through all the worksheets and inserting a lot of redundant code, would it not? On the other hand, inserting a module sounds simple enough, but can that one module get a button on each worksheet in the workbook? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add code module to Excel programmatically
"deko" wrote in message om... Why not just create a template workbook with a userform. And do things the easy way??!! Unfortunately, that would require users to do something differently, and politics being as they are, that's not an option. No, I'm thinking nothing to do with the users, you can open that template in VBA, so you keep control. The thing I'm concerned about is having to add code to every worksheet - that would require looping through all the worksheets and inserting a lot of redundant code, would it not? On the other hand, inserting a module sounds simple enough, but can that one module get a button on each worksheet in the workbook? Adding a module is simple, so is inserting procs in those nodules, but remember, each sheet has a class module automatically created with it that can be used. In addition, you can add workbook events that can apply to all sheets. One procedure can create buttons on each worksheet, but why not create a toolbar button instead, which can test what the activesheet is at runtime? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add code module to Excel programmatically
No, I'm thinking nothing to do with the users, you can open that template
in VBA, so you keep control. Sounds interesting. But the Access app inserts new worksheets into an existing workbook which is selected but the user at runtime. Adding a module is simple, so is inserting process in those nodules, but remember, each sheet has a class module automatically created with it that can be used. So I'm not constrained to use the worksheet module to get a button on the sheet? That was my concern - i.e. inserting redundant code into 255 modules. In addition, you can add workbook events that can apply to all sheets. hmmm... One procedure can create buttons on each worksheet, but why not create a toolbar button instead, which can test what the activesheet is at runtime? A toolbar? Now that sounds like a winner. I'll look into it. Thanks for your comments. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code in module A to not execute a Worksheet_SelectionChange sub of another module | Excel Discussion (Misc queries) | |||
How to programmatically determine which rows are outlined using Excel VBA code | Excel Programming | |||
Excel VBA - How do I programmatically change source code in another file? | Excel Programming | |||
programmatically open VBE and go to a specified procedure in target module? | Excel Programming | |||
Add reference programmatically before a module compiles | Excel Programming |