Insert VBA Code With VBA Code

[Derek Hart]

How I can use vba code to insert vba code into another document?

 

[Gordon Bentley-Mix]

Derek,

The usual answer to your question is "You can't" or "With difficulty".

If you provide more information on what you are trying to accomplish,
chances are that someone will provide an equally detailed answer.

In this instance, I suspect that you are trying to make some VBA
functionality that is available in one document automatically available in
another, similar document. The best approach to this achieving this goal is
to create a template on which all such similar documents are based. Then, as
long as the user has access to the template, any VBA functionality will be
available in any document based on the template. There are many excellent
resources available online to assist you with this process. I would recommend
starting with www.mvps.org and expanding your search from there.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Shasur]

Hi

If you have it as a module you can do that as follows

Function Insert_Code(ByRef oWB As Workbook)

Dim oVBP As VBProject ' VB Project Object
Dim oVBC As VBComponent ' VB Component Object

On Error GoTo Err_VBP

Set oVBP = oWB.VBProject '. VBProjects

Set oVBC = oVBP.VBComponents.Add(vbext_ct_StdModule)

oVBC.CodeModule.AddFromFile "c:\MyOldCode.bas"

oWB.Save

' -------------------
' Destroy Objects
' -------------------
Finally:
If Not oVBP Is Nothing Then Set oVBP = Nothing

' -------------------
' Error Clearer
' -------------------
Err_VBP:
If Err <> 0 Then
Err.Clear
GoTo Finally
End If
End Function


if you want code to be from string you can use

oVBC.CodeModule.AddFromString

(http://vbadud.blogspot.com/2007/06/extract-procedure-names-from-all.html)

Cheers

 

[Greg Maxey]

Translated to Word VBA I think this would look something lik this:

Option Explicit
Sub OpenDocumentForProcessing()
Dim oDoc As Word.Document
Set oDoc = Documents.Open(FileName:="C:\Test.doc")
Insert_Code oDoc
oDoc.Close wdSaveChanges
End Sub

Function Insert_Code(ByRef Doc As Word.Document)
Dim stdModule As VBComponent
With Doc.VBProject
Set stdModule = .VBComponents.Add(vbext_ct_StdModule)
stdModule.Name = "Test_Module_1"
stdModule.CodeModule.AddFromString ("Sub Test_Code()" & vbCr _
& "Msgbox ""This code was added programatically""" & _
vbCr & "End Sub")
Set stdModule = Nothing
Set stdModule = .VBComponents.Add(vbext_ct_StdModule)
'Clear any line in new module
Do While stdModule.CodeModule.CountOfLines > 0
stdModule.CodeModule.DeleteLines 1
Loop
stdModule.CodeModule.AddFromFile "C:\Code.bas"
End With
End Function