Automate the creation (and insertion) of a .bas file

[KR]

As a test, I exported a word VBA module, and it appears to be a text file of
the code, with the following header added:
Attribute VB_Name = "Module1"

I have a large amount of information that ends up being hardcoded into my
modules (filling large arrays) because for my purposes, I need to keep
everything in one file instead of pulling it from a database or other file.
Since the VBA interface itself is a pain to update long text strings, I
decided to keep the raw content in excel, then automate getting it into my
word template when I have updated content.

Question 1: In Excel, I can create a text file that has the header (above)
and populate it with the "code" that will fill my arrays. Is there anything
else I need to worry about, or can I create a .bas as a text file (changing
the extension) and then just import it into Word VBA?

Question 2: A minor point, but is there a way in automate Word VBA to delete
a module with a given name, then inport a module of the same name from the
current directory (I can hardcode the directory, that isn't a problem).
Obviously the code would be running from a different module than the one
being deleted/replaced, but I don't know how this could be done.

Sorry if these are dumb questions, I've just never messed with automating
the creation and importing of modules before.

Thanks!
Keith

 

[Word Heretic]

G'day "KR" <[email protected]>,

Mate - self inconsistent:

I need to keep everything in one file instead of pulling it from a database or other file.
and

keep the raw content in excel, then automate getting it into my word template

Q1: Excel can create cvs files, or you can use VBA file open
statements to create anything

Q2: Absolutely. VBProject.VBComponents

Steve Hudson - Word Heretic
Want a hyperlinked index? S/W R&D? See WordHeretic.com

steve from wordheretic.com (Email replies require payment)


KR reckoned:

 

[KR]

I apologize for not being clearer: the template (.dot) file that is
distributed to users must be a single file, including all information
hardcoded within the document.

My process for /updating/ that template with new/revised information as
necessary can take any format and as many files as I want, as long as the
_final_ template is self-contained.

My goal is to find a way (within my programming ability) to simplify the
updating of the big chunks of information that currently is hardcoded in VBA
as:

Array1(1,1,1,1,1)= "long string of info"
Array1(1,1,1,1,2)="another long string of info"
etc.

I had considered docvariables, but too much opportunity for the end user to
find those, and muck them up which could crash my template's VBA code.

My current strategy to update the template is to maintain the source strings
in Excel, then use Excel VBA to port out a text file (then rename it .bas)
that looks like:

Attribute VB_Name = "Module1"

Sub Whatever()
Array1(1,1,1,1,1)= "long string of info"
Array1(1,1,1,1,2)="another long string of info"
etc.
end sub

My questions are:
(1) whether it is really that simple to create a .bas file (is it really
just a text file, or are there any hidden codes that I don't see when I open
it in a text editor), and
(2) If I do create a .bas file from Excel (or another Word document, or
Access, or any external program) what is the Word VBA code to "load" that
module into my template so that it is part of the document when I distribute
it. Alternatively I could just import them all using file/import, but I may
have many .bas files, and I may want to turn this process over to someone
else, so having it mostly automated will reduce the chance of human error.

Any responses to these two questions would be greatly appreciated,
Keith

 

[Word Heretic]

G'day "KR" <[email protected]>,

No no, I apologise for not being clearer and for taking the easy way
out - all this was obvious in your first post. Generically this
technique is called Localisation (L10N). In its extreme form, you also
translate all your output strings...

I often come across this problem in many different forms. There are
numerous ways of addressing it. You will rapidly discover why i tried
for the easy option (wry grin}

The first way is a whole punch of publics.

Eg

Public Parm1 as String
Public Parm2 as Long

....


Sub SomeGuiInterface()
Parm1="agagagagag"
RunIt
End Sub


Yuck (as recently decried by Jez), so we wrap all that into a class
module and those publics become properties. We can set them at
class_init time but our calling code can change at any time. Eg

Sub RunIt()
Dim MyObj as oMyObject
Set MyObj = new oMyObject

with MyObj
.Parm1 = "agagagag"
.Parm2 = 2
.RunIt
end with
set myObj = Nothing
End Sub

However, it is STILL hard-coded into the template. We often want a
single technology solution with many implementations. So in these
cases we have to store our information elsewhere where it is easily
customizable without having to open and play with our template.

So option 1 is only good for a few options type settings or for
implementing purely API driven changes (ie, externally referencing
this global code from a local template). We extend it a touch further
by:

Storing all our strings in the registry
Including a UserForm to change those options.

Now it is useful for storing user / session preferences. But we dont
want to have to roll out registry changes / config instructions for
users to install it. So it still isnt suitable for L10N usage.

So, where else can we store this information? There are a whole bunch
of possibilities, I will quickly discuss them below. Two roles here,
the Dev (you) and the User (the Word users who will use your
templates). The amount of coding is reasonably similar in all cases.


1. External config file.

a) Simple text file

Dev Cons: Some extra to a LOT of extra work, good file formats are a
PITA to implement (eg true XML). New user machines require a copy or
access to the user's latest copy of this file if the user prefs are
stored there. OS dependant.

Dev Pros: Easily extendable with forwards and backwards compatibility.

User: Extra file to install but reasonably easy to self-maintain
providing there is doco.


b) External Word file

(eg MyConfig.doc kept in the Tools dir)

Dev Cons: Tricky issues involved in structuring the data and
retreiving / changing it. New user machines require a copy or access
to the user's latest copy of this file if the user prefs are stored
there.

Dev/User Pros: Can be used to store ANYTHING our Word user could be
interested in.

User: Extra file to install but reasonably easy to self-maintain
providing there is doco.



2. Document variables

Dev/User Pro/Con: Users cannot edit the contents

Dev Pros: Extendable but tricky. Single file solution.



3. Custom Document Properties

Dev/User Pro/Con: Users can easily edit the contents via File >
Properties > Custom

Dev Pros: Extendable but tricky. Single file solution.

Dev Cons: mild data type limitations



4. AutoText Entries

Dev & User Pros: Holds RTF data (eg pix, formatting)

Dev Cons: Can be quite tricky at times and updating them for
distribution is a slow PITA. Easily extendable.

User Cons: God help em if they try and play with them. They WILL
regularly stuff it up.


5. Registry entries

Dev Pros: Quick and easy, extendable

Dev and User Cons: Playing with registry entries is never fun to have
to set them. An options interface as per the external file solution
mitigate this.



Putting all this together

What you have in reality is a bunch of settings with no analyses. So,
before proceeding any further draw up a table with the headings
"Setting" and "Edit Dependency" (ED).

Now list all your settings in it, and analyse them. By ED keywords:

Run This setting needs to be user set every time a macro runs.

Session This setting may need changing during the WORD SESSION.

User This setting tends to be set once by the user and not
changed very often

Locale This setting is dependant on some common user characteristic
eg geography, work group, company.

Version This is set only by the dev for all copies of that version.


Be sensible now, if you only have a few in one category and you can
get away with squeezing them into another to avoid implementing
another technology component, do it! Remember that sensible subroutine
use always makes reading a setting a single line command.

<Var> If you have RTF+ data, you are immediately limited to ATEs
or an external Word doc.

Run Dialog or simple UI prompted by macro. Use private vars.
Store last used setting in the registry
If you must support an API, use Optional parms.
App.DisplayAlerts can be read to block msging and use an alt
data source.

Session Registry settings backed by a dialog.

User Anything that is maintainable by the user.

Locale External config file (you as dev can store multiple copies
for each locale for upgrade use)

Version Private (where possible) constants (where possible).


Steve Hudson - Word Heretic
Want a hyperlinked index? S/W R&D? See WordHeretic.com

steve from wordheretic.com (Email replies require payment)


KR reckoned: