Inserting vba modules programmatically

[Harry-Wishes]

I have developed sort of this mega-template that writes subroutines
programmatically into an blank Word document. It will be for use for another
template I will distribute to my co-workers. I've been consulting the forums
and literature on how to not only programmatically write the code (which
I've done successfully ) but also take that code and insert it as a vba
module into the second template on the fly without having to go into the VBA
editor manually to insert the code.

Currently I have to do the crude copy and paste method into the project
template module which is a clunky process since part of the code will go
into the portion of the template associated with a userform I've created for
the new template. The remaining portion gets pasted into the main module
associated with the template itself. Am I making sense? Can this
programmatically be done so I can distribute just one template file to other
users?

Harry Wishes

 

[Cindy M.]

Hi Harry-Wishes,

Currently I have to do the crude copy and paste method into the project
template module which is a clunky process since part of the code will go
into the portion of the template associated with a userform I've created for
the new template. The remaining portion gets pasted into the main module
associated with the template itself. Am I making sense?

No, not really...

I've been consulting the forums
and literature on how to not only programmatically write the code (which
I've done successfully ) but also take that code and insert it as a vba
module into the second template on the fly without having to go into the VBA
editor manually to insert the code.

Using the same VBE library you can create modules, and import them from a file.
It's also possible to use the OrganizerCopy to copy from one file to another -
this copies modules. Is that the kind of thing you mean?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Harry-Wishes]

Hi Cindy

Thanks for your reply

Actually I already have some subroutines prewritten in a vba module
associated with the template I plan to distribute since that code will be
common to all of the documents created from the template. The additional code
I need to write will be specific to the template the end user plans to
create. But, it sounds like I will not be able to dump additional code into
the existing module to add to the template's functionally, at least not the
way I had envisioned.

Is it possible programmatically to write the code outside the VBA editor and
save it as an eternal file and then programmatically import the file as a
second module of the template? Perhaps that is asking a lot from a
programming perspective. My current thinking now is to have some call
statement in module 1 call the second module that is imported. Is that
possible? If so, how to I do the import of the second module without having
to do it manually? It is the second module (if it can be programmatically
created) that will make the template fully functional.

Harry Wishes

 

[Harry-Wishes]

Too bad the forum doesn't allow edits to postings.

Anyway, I want to reiterate that in addition to the code I have already
written in the current module of the template, I have designed a userform
that will appear on the user's screen. What I was saying earlier is that the
code that I will need to make the buttons and other items on the userform
functional needs to get imported as a module as well. That is in addtion to
all of the subroutines that will ultimately be needed to control the word
document on the front end.

I'm doing all of this right now by hand (copy and paste method) and it all
works in the end but still a clunky process.

Harry Wishes

 

[Cindy M.]

Hi Harry-Wishes,

Anyway, I want to reiterate that in addition to the code I have already
written in the current module of the template, I have designed a userform
that will appear on the user's screen. What I was saying earlier is that the
code that I will need to make the buttons and other items on the userform
functional needs to get imported as a module as well. That is in addtion to
all of the subroutines that will ultimately be needed to control the word
document on the front end.

I'm doing all of this right now by hand (copy and paste method) and it all
works in the end but still a clunky process.

I guess I'm a bit unsure what it is you already know how to do when you say
"I've been consulting the forums and literature on how to not only
programmatically write the code (which I've done successfully )..."

If you already know how to write code to an existing module, then you shouldn't
have any problem with " The additional code I need to write will be specific to
the template the end user plans to create. But, it sounds like I will not be
able to dump additional code into the existing module to add to the template's
functionally, at least not the way I had envisioned. " ??

Is it possible programmatically to write the code outside the VBA editor and
save it as an eternal file and then programmatically import the file as a
second module of the template?

Yes, by saving it as a *.bas file. But I suggest you look in the VBA Editor at
the File/Export and File/Import commands. These will save modules to text files
and import the same. And there are equivalents in the VBE object model.

In addition, experiement with the Organizer. This would be Tools/Templates and
Add-ins/Organizer if you're using Word 2003 or earlier. The Word object model
has an equivalent to this that lets you copy modules directly from one Word
file to another one.

Everything you want to do is possible - I just can't follow what you already
know, what you actually need, and where to point you at...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Cindy M.]

Hi Harry-Wishes,

Anyway, I want to reiterate that in addition to the code I have already
written in the current module of the template, I have designed a userform
that will appear on the user's screen. What I was saying earlier is that the
code that I will need to make the buttons and other items on the userform
functional needs to get imported as a module as well. That is in addtion to
all of the subroutines that will ultimately be needed to control the word
document on the front end.

I'm doing all of this right now by hand (copy and paste method) and it all
works in the end but still a clunky process.

I guess I'm a bit unsure what it is you already know how to
do when you say "I've been consulting the forums and
literature on how to not only programmatically write the
code (which I've done successfully )..."

If you already know how to write code to an existing
module, then you shouldn't have any problem with " The
additional code I need to write will be specific to the
template the end user plans to create. But, it sounds like
I will not be able to dump additional code into the
existing module to add to the template's functionally, at
least not the way I had envisioned. " ??

Is it possible programmatically to write the code outside the VBA editor and
save it as an eternal file and then programmatically import the file as a
second module of the template?

Yes, by saving it as a *.bas file. But I suggest you look
in the VBA Editor at the File/Export and File/Import
commands. These will save modules to text files and import
the same. And there are equivalents in the VBE object
model.

In addition, experiement with the Organizer. This would be
Tools/Templates and Add-ins/Organizer if you're using Word
2003 or earlier. The Word object model has an equivalent to
this that lets you copy modules directly from one Word file
to another one.

Everything you want to do is possible - I just can't follow
what you already know, what you actually need, and where to
point you at...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[Cindy M.]

Hi Harry-Wishes,

Anyway, I want to reiterate that in addition to the code I have already
written in the current module of the template, I have designed a userform
that will appear on the user's screen. What I was saying earlier is that the
code that I will need to make the buttons and other items on the userform
functional needs to get imported as a module as well. That is in addtion to
all of the subroutines that will ultimately be needed to control the word
document on the front end.

I'm doing all of this right now by hand (copy and paste method) and it all
works in the end but still a clunky process.

I guess I'm a bit unsure what it is you already know how to
do when you say "I've been consulting the forums and
literature on how to not only programmatically write the
code (which I've done successfully )..."

If you already know how to write code to an existing
module, then you shouldn't have any problem with " The
additional code I need to write will be specific to the
template the end user plans to create. But, it sounds like
I will not be able to dump additional code into the
existing module to add to the template's functionally, at
least not the way I had envisioned. " ??

Is it possible programmatically to write the code outside the VBA editor and
save it as an eternal file and then programmatically import the file as a
second module of the template?

Yes, by saving it as a *.bas file. But I suggest you look
in the VBA Editor at the File/Export and File/Import
commands. These will save modules to text files and import
the same. And there are equivalents in the VBE object
model.

In addition, experiement with the Organizer. This would be
Tools/Templates and Add-ins/Organizer if you're using Word
2003 or earlier. The Word object model has an equivalent to
this that lets you copy modules directly from one Word file
to another one.

Everything you want to do is possible - I just can't follow
what you already know, what you actually need, and where to
point you at...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[Harry-Wishes]

I appreciate your help.

I think I better give you a glimpse of what I am actually doing right now
and perhaps you can direct me to a better solution. The code I am writing
outside of the VBA editor is being written to a Word document via the
Selection.TypeText Text method:

Selection.TypeText Text:=" whatever code " & Chr(13)

Then I manually copy the code generated in the Word document and go into the
VBA editor to paste the code in the correct module of the template. That is
the way I know how to write code outside the VBA editor. Ofcoure I also
have code already in a module of VBE which I have written. No problem there.

Back to the external coding, if I understand you correctly I need to write
to a bas file somewhere on my harddrive so the template can access it after
importation as a new module. That is where my knowledge of VBA is limited.
I see 3 things I need to accomplish using VBA coding.

1) Create a bas file
2) Write to the bas file
3) Import the bas file into the template I want associated with it.

I've never created a bas file but assume it is just a text file that Word
and other Office applications can understand. Hopefully, this clears up some
confusion.
I'm still trying to figure out this whole thing, still in the learning
process.

Thanks once again!

Harry Wishes.

 

[Harry-Wishes]

To sum things up. I saw this in another forum with no replies. This in a
nutshell is generally what I'm after.

"How can I create a subroutine in Excel VBA that writes a .bas file and then
runs the file it just created?

 

[Cindy M.]

Hi Harry-Wishes,

I think I better give you a glimpse of what I am actually doing right now
and perhaps you can direct me to a better solution. The code I am writing
outside of the VBA editor is being written to a Word document via the
Selection.TypeText Text method:

AHA! Yes, that does explain why my half of the discussion was going right by
you <g>

I mentioned the File/Import and File/Export part of the VBA Editor? I still
recommend you look at that. You can use that to create the *.bas files if it
works for you to access a set of these, as required. Here's a sample
procedure; you'll probably need to set a reference to the Microsoft Visual
Basic for Application Extensibility library (Tools/References):

Sub ImportBasFile()
Dim vbProj As VBProject

Set vbProj = ActiveDocument.VBProject
vbProj.VBComponents.Import "C:\MyBackBasFiles\FindReplaceStuff.bas"
End Sub


Another way, as I mentioned, is to use the Organizer to copy a module from
one container to another. For example, if you have a document or template
that contains all the modules you need, and you pick-and-choose the ones for
a specific document:

Sub TransferViaOrganizer()
Dim source As String
Dim target As String

source = NormalTemplate.FullName
target = ActiveDocument.FullName
Application.OrganizerCopy source:=source, _
Destination:=target, Name:="FindReplaceStuff", _
Object:=wdOrganizerObjectProjectItems
End Sub


To write code directly into an existing module:

'Adds to beginning of module
Sub WriteCodeToModule()
Dim vbProj As VBProject

Set vbProj = ActiveDocument.VBProject
vbProj.VBComponents("MathStuff").CodeModule.AddFromString _
"Sub NewCode()" & vbCr & _
"MsgBox " & """This procedure was added by code!""" & _
vbCr & "End Sub"

End Sub


And to import from a bas file to an existing module:

Sub ImportIntoCodeModule()
Dim vbProj As VBProject

Set vbProj = ActiveDocument.VBProject
vbProj.VBComponents("MathStuff").CodeModule.AddFromFile _
"C:\BasBackup\OLEStuff.bas"
End Sub

These things aren't really well documented, but you can access what Help
files there are by clicking in something (CodeModule, for example) then
pressing F9.

If you need to create the *.bas files on-the-fly, then all you need to do is
to save your current Word document as a plain text file with the file
extension bas.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Harry-Wishes]

Wow! ! ! Thanks! This works like a charm! I took all of those
"Selection.TypeText Text:=" statements out, replacing them with "Print
#fnum," statement ---- taking just seconds. In setting up the actual output
to an external file, I wrote this.


myFile = "c:\" & "main_module.txt"
fnum = FreeFile()
Open myFile For Output As fnum


It was the last snippet you provided that took me over the threshold I've
been trying to cross. I did tweak it a bit to suit my specific goal.

Sub ImportIntoCodeModule()
Dim vbProj As VBProject
Set vbProj = Application.Documents(1).VBProject
vbProj.VBComponents(1).CodeModule.AddFromFile "C:\main_module.txt"
End Sub

The only thing I needed to remember is set that reference to the Microsoft
Visual
Basic for Application Extensibility library.

Within the click of a button, I can avoid the copy and paste method inside
the VBE. I'll play around with the other portion regarding the userform I
created.

Thanks for your assistance.

Harry Wishes

 

[Cindy M.]

Hi Harry-Wishes,

I'll play around with the other portion regarding the userform I
created.

For this you probably will need to use File/Export if you want to
use an existing UserForm. That generates two files, a *.frm and a
*.cls. The *.cls you can create from scratch, but not a *.frm
(although when you look at the *.frm file in a text editor you'd
think you could - but it usually doesn't work that well).

If you do need to create the form on-the-fly then you should
automate the VBA-IDE, using the VBE. You can create a UserForm that
way, but it can be tricky. I think there's a KB article around that
gives you a starting point...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17
2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[shree k]

Hello,

how can i insert VBA modules into word VB editor using command promt? can anybody please help me regarding this.

Thanks,
Shree



HarryWishe wrote:

Re: Inserting vba modules programmatically
05-Jun-09

Wow! ! ! Thanks! This works like a charm! I took all of those
"Selection.TypeText Text:=" statements out, replacing them with "Print
to an external file, I wrote this

myFile = "c:\" & "main_module.txt
fnum = FreeFile(
Open myFile For Output As fnu

It was the last snippet you provided that took me over the threshold I've
been trying to cross. I did tweak it a bit to suit my specific goal

Sub ImportIntoCodeModule(
Dim vbProj As VBProjec
Set vbProj = Application.Documents(1).VBProjec
vbProj.VBComponents(1).CodeModule.AddFromFile "C:\main_module.txt
End Su

The only thing I needed to remember is set that reference to the Microsoft
Visual
Basic for Application Extensibility library.

Within the click of a button, I can avoid the copy and paste method inside
the VBE. I'll play around with the other portion regarding the userform I
created.

Thanks for your assistance

Harry Wishe

:

EggHeadCafe - Software Developer Portal of Choice
Silverlight 2 Beta 2 - Doing Data Part I
http://www.eggheadcafe.com/tutorial...cc-58558a7e83f8/silverlight-2-beta-2--do.aspx

 

[shree k]

Hello,

how can i import VBA .bas file into word VB editor module by command prompt.Actually i have already created .bas file in my local drive.please help me regarding this.

Thanks,

Shree





HarryWishe wrote:

Re: Inserting vba modules programmatically
05-Jun-09

Wow! ! ! Thanks! This works like a charm! I took all of those
"Selection.TypeText Text:=" statements out, replacing them with "Print
to an external file, I wrote this.


myFile = "c:\" & "main_module.txt"
fnum = FreeFile()
Open myFile For Output As fnum


It was the last snippet you provided that took me over the threshold I've
been trying to cross. I did tweak it a bit to suit my specific goal.

Sub ImportIntoCodeModule()
Dim vbProj As VBProject
Set vbProj = Application.Documents(1).VBProject
vbProj.VBComponents(1).CodeModule.AddFromFile "C:\main_module.txt"
End Sub

The only thing I needed to remember is set that reference to the Microsoft
Visual
Basic for Application Extensibility library.

Within the click of a button, I can avoid the copy and paste method inside
the VBE. I'll play around with the other portion regarding the userform I
created.

Thanks for your assistance.

Harry Wishes

:

EggHeadCafe - Software Developer Portal of Choice
MDI .NET Forms Include Docked Controls And Smaller MDIClient
http://www.eggheadcafe.com/tutorial...84-57049e53b8c5/mdi-net-forms-include-do.aspx