Add-In or VB: what do you think?

[Ed]

I've got a project in the works that I think might best be implemented by an
Add-In. It's either that, or a Visual Basic program. I'm leaning towards
an Add-In, but I've never done one before - for that matter, I've only done
one VB project so far! So there may be a lot of factors I'm unaware of that
could hang me up, and I'd like to get the expert's opinions.

Basically, the program will collect 8 or 10 bits of data, write them to doc
variables, and create a string to insert them into a document. There will
be a form or two to walk the user through this. The touchy point is that
the program must somehow store these bits of data for recall as a collection
so they can be inserted into later documents. I envision the main form as
having a series of text boxes; upon opening, the boxes populate with data
from the first collection of data bits, and the user can click a spinner to
iterate through the collections to find the one he wants, or click a "New"
button to collect a new set of data.

That sounds like a small database, which VB is excellent for. But an Add-In
can launch with Word in the background and be accessible from a menu item.
Here's where I'm a bit fuzzy on Add-Ins: in my understanding, this is a .dot
document, which means it also has "writing space" that the user never sees.
So maybe I could write my data collections as tables on the "face" of the
Add-In document surface to store them?

What do you think?
Ed

 

[Doug Robbins]

Check out the System.PrivateProfileString.

That allows you to write values to .txt file from where you can retrieve
them.

Sounds like what you should be using for this is a userform.

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

Probably rather than the template being stored in the Word startup folder as
an addin, it should just be stored in the User or Workgroup template
directory and the user should use File>New to create a new document from
that template.

See the article "Distributing macros to other users" at:

http://word.mvps.org/FAQs/MacrosVBA/DistributeMacros.htm




--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Ed]

Thanks for replying, Doug.

Check out the System.PrivateProfileString.
That allows you to write values to .txt file from where you can retrieve
them.

I read the Help file on that, but I'm not too sure it would work for me. If
I do this for 10 different sets of data, then when I open the UserForm, it
has to grab and read through 10 different .txt files. That also means I
have to create a storage place for them on the user's machine, and I'm not
sure our permissions will allow a program to do that. One set of data
points may need to be inserted in two dozen different documents over the
space of a year; all the while, the user is collecting more for another set
of documents.

Probably rather than the template being stored in the Word startup folder as
an addin, it should just be stored in the User or Workgroup template
directory and the user should use File>New to create a new document from
that template.

Doug, if we could get our users to do THAT, then we wouldn't have to try to
get them something as small as "select one menu item and walk through the
boxes." The problem is that I'm a bottom-feeder in this food chain, but one
of a handful responsible for thinking about our reports. Most of the users
are high up the feeding chain, won't take any direction from me, and can't
be bothered with selecting a specific template when they open Word - they
just start typing. (Most don't even know how to un-set Indents with Tabs
and Backspace!) But if an Add-In opens with Word, invisible to them, adding
information is as simple as select and click, and inserting the information
is one button on a UserForm, we *might* stand a chance!

My biggest concern is where to store all of these "data bit sets" so the
UserForm can retrieve it and iterate through the whole collection for the
user to select one set, and so a new set can be easily added and stored.
Hence the question of simply writing it into a table on the Add-In
template - or maybe as an AutoText in the Add-In? In my limited knowledge,
keeping everything down to the one Add-In file seems to be an easier
solution that creating an entire directory of .txt files. Am I missing
something critical?

Ed

 

[Doug Robbins]

The System.PrivateProfileString can write each piece of data to the one
file, you would not need 10 files.

You need to think this whole thing through a bit more. Especially the bit:

One set of data points may need to be inserted in two dozen different
documents over the
space of a year; all the while, the user is collecting more for another set
of documents.

How is the decision of which data points get used in which document going to
be made?

You could write each set of data into the cells of table that is stored
somewhere and you could then have the initialize event of the userform,
populate a listbox or combobox with the stored data for the user to be able
to select which set to use.

It is NOT a question of Add-In or VB(A). It's going to be fairly heavy duty
VBA and a template in the templates directory is going to be the way to go,
not an Add-In.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Ed]

Doug, I appreciate your time, patience, and expertise. I apologize if I
seem obtuse - I'm just having a hard time wrapping my mind around both what
I see as the problem I'm trying to solve and the solution you are
presenting.

You need to think this whole thing through a bit more. Especially the bit:

How is the decision of which data points get used in which document going to
be made?

The whole backstory is contained in a post on the docmanagement forum here:
http://tinyurl.com/b6kpk .
Briefly, I write reports. Each report is identified by the author (not me -
the person who assigned the report), title, report number, and several other
"data bits", all of which are contained on a cover page. When all of this
was one big chunk of paper, the cover page was part of it. We are now
writing these reports one section at a time and emailing them or posting
them to a server - and we have lost the connection to the cover page info.

My idea is to have the report writer collect the cover page "data bits" and
insert them into every report section as DocVariables. Three years from
now, when someone has five Section 3-2 docs on their computer, it would be
nice to know all of this info.

Given some of the social aspects of implementing this across our
organization, I am stretching the capacities of some reports writers to
expect them to select a menu item, walk through a UserForm to assign and
collect the "data bits", and then click a single button to insert this info
into each report section. Getting these people to select a specific
template before writing a report is not even a consideration! Given some of
the restrictions IT puts on users, it is not unthinkable that a macro trying
to create and modify a text file would be at least blocked, if not cause for
a three-alarm response. (Think of the poor guy in "Monsters, Inc" with the
sock on his back!)

You could write each set of data into the cells of table that is stored
somewhere and you could then have the initialize event of the userform,
populate a listbox or combobox with the stored data for the user to be able
to select which set to use.

That was exactly my idea - except my thought was to store them within the
Add-In template, since it is already there, in use and available, and no
other file needs to be created and tracked by the macro.

I know there are whole worlds of VBA that I don't understand, and a lot of
that is probably showing right now. Like trying to see how to get past the
limitations to do things the best way. If you have the time and desire to
continue this thread, Doug, I would be very grateful. Otherwise, I again
thank you for all you have given me.

Ed

 

[Doug Robbins]

The first thing that you need to understand is that an Add-in will not
automate the process. There is no way that you can avoid having the user
enter the information concerning the report of which the section they are
working on forms a part into something somewhere.

You really need to sort out this workflow issue first before trying to
create the system to do it.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Ed]

There is no way that you can avoid having the user

enter the information

My sincere apologies, Doug, if I have miscommunicated this. I am *not*
looking for a "magic bullet" that will automatically know what to write to
the document. The way I envision it, the writer will fire the macro upon
creating a new report section (probably using a menu item - it would be so
much better to use an AutoNew from a template, but there's no way I can get
them to choose a specific template), which pops up a UserForm with text
boxes and buttons. Let's say the information from five reports has already
been collected from previous reports. The code in the form grabs the info
from the first report and populates the text boxes. If it's the right info,
the user clicks a button, and it's written into the doc as DocVariables. If
it's not, the user can click a spinner to iterate through the rest of the
information collected and find the right set of data, or click NEW and
create a new collection of data. This can either be manually input into the
text boxes, or the user can select a range of text and click an ENTER
button, and the text will be captured (formatting doesn't matter) and saved
for use in new report sections.

Interfacing with the user is going to be a big deal, since I'm dealing with
a wide assortment of users who can't be bothered to do too much unless
they're forced to. A UserForm popping up is something they will deal with,
but selecting a specific template rather than just opening Word and using
the default Normal is too much to ask.

The question of the Add-In versus a separate VB program is again getting the
users to deal with what they consider extraneous items. A menu item in Word
they will remember - or maybe a colorful button; launching a separate
program after launching Word they will not. And in my limited experience,
to automatically put a menu item or button in Word upon opening, it needs to
be an Add-In. Thus my questions of storing the collected information in the
Add-In, rather than trying to create and track a separate file.

Is this just not going to work at all, Doug?

Ed

 

[Doug Robbins]

Have the button that the Add-In adds to the toolbar run a macro that starts
a new document from the template that contains the user form.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[jay]

Sounds like you need some doc management there...
Anyway- it depends on how many chunks of info change in the users'
hands and how many of them just belong to the doc package that this
particular doc belongs to.
Info that belongs to the greater package may be better kept in an
external file.
The external file could be an Excel spreadsheet, a database, or
whatever.
If each chapter/fragment file contains an identifier which shows which
package it belongs to, then your macro can use the .xls for doc data,
rather than trying to stash info in the doc fragment.

This assumes (ass/u/me) that your network will allow macros on each
user's machine to read/write to some central location.

The user form is one way to handle the data collection. VB/VBA makes it
prett easy...
comments?