New to Macros Please help

[Mathew]

I have a word template, actually 45 but if I can get this to work in one I’ll
be able to copy it to the others. What I need to do is capture data, copy it
and paste it, from Microsoft Excel and insert it into the address block and
the Dear: block using a macro. A mail merge would be too cumbersome. We
have a long list of “clients.†Daily we create standard form letters to some
of these clients each day. We have about 45 different form letters. In
short, the users, 5 of us, generate these letters about 50 times a day each
to about 75 different “Clients†a day. The letters generated vary greatly.
The “client data†is kept in Microsoft Excel, 2003. The file name is:
“Client Data†and is located in H:\clients\general. We use Microsoft Word
2003. How would you best do this? In addition, is it possible to create a
macro that will print the letter, then “turn on†a footer and print 2 copies
with the footer showing on the letter as well. To do this the user has to do
the following: Tools then Options then go to the Print tab then check the
Hidden Text box.

I am somewhat familiar with VB in Excel but have never used it for Word.

 

[Steve Yandl]

Matthew,

I'd create the Word template with bookmarks inserted where you want the
address fields. Create a userform that opens your workbook in a hidden
instance of Excel, populates a combobox with the names from the appropriate
column in that workbook and then have a command button with a sub the
appropriate data from the workbook based on what the user has selected in
the combobox and inserts those values where the bookmarks are located.

Take a look at http://word.mvps.org/FAQs/index.htm
Look under the section 'Cross-Application dev' and read the article on
control of Excel from Word. Also check the section 'Userforms' to get tips
on making a userform. I think that will cover most of what you need.

Steve Yandl

 

[Mathew]

Steve: Thanks for the help.

Matthew,

I'd create the Word template with bookmarks inserted where you want the
address fields. Create a userform that opens your workbook in a hidden
instance of Excel, populates a combobox with the names from the appropriate
column in that workbook and then have a command button with a sub the
appropriate data from the workbook based on what the user has selected in
the combobox and inserts those values where the bookmarks are located.

Take a look at http://word.mvps.org/FAQs/index.htm
Look under the section 'Cross-Application dev' and read the article on
control of Excel from Word. Also check the section 'Userforms' to get tips
on making a userform. I think that will cover most of what you need.

Steve Yandl

 

[David Sisson]

the Dear: block using a macro. A mail merge would be too cumbersome. We
have a long list of "clients." Daily we create standard form letters to some

It's going to be a arduous task however you approach it.

I tend to write macro in the environment I'm working in. Since you
are creating a letter, I would tend to write the macro in Word.

I would create a new document as a way to track the letters I've
written. So, create a table with the pertinent information, Name,
Subject, date. Within this document, create a userform that populates
listboxes with data pulled from Excel. Since you have a rather large
list, you could break the list up in cascading listboxes, Say A-N, and
N-Z, or by town, or subject, etc.

Once the choices were made on the userform, the macro would create a
new form letter with the info pulled from Excel.

macro that will print the letter, then "turn on" a footer and print 2 copies
with the footer showing on the letter as well. To do this the user has to do
the following: Tools then Options then go to the Print tab then check the
Hidden Text box.

Record a macro while you perform this function to get you started.

In the table document, add a custom menu to the menubar and put two
items in it. One to run the userform, and one to perform this footer
on/off printout.

----------------
As I'm looking over what I've written, if you decide to track this
information, a table in Word probably isn't the best choice since
large tables in Word are precarious at best. You could store the info
in Excel, but then, why would you write this in Word to begin with.
Perplexing, no?

 

[Ed from AZ]

One thing to be cautious of - make sure that when you read "template"
in these responses that you understand a file with a ".dot" extension,
and not simply a blank document (".doc" extension) that you open, fill
with information, and save as a new file.

I don't know how your information is set up in Excel. I may be
entirely incorrect, but I imagine a row with the client's name,
address, etc, and the pertinant factors that decide which letter
"template" you are going to use. If I had data set up like that, I
might be look at driving this from an Excel macro. All the info is
already there - why load it up and transfer into another application?
I could open the appropriate Word doc or create a new doc based on the
proper template, then read the info straight from the spreadsheet into
bookmarks or table cells and SaveAs a new doc.

Ed