I a macro in a word template that will get data from a named range in an excel workbook

[Dee]

I have just started with VBA and am having real difficulty in solving this
problem.

I work for a small law firm and I want to create a template that can be used
by all our sectetaries. I want to have a message box that asks them to
enter the Lawyer's initials. and then I want to get the information from
the excel work book and place it in relavent places in the letter. so it
would be info like the email address, fax number, their name and jot title.
I am unclear as to the best way of doing this. I have looked at book marks
but cannot see how to assign the data to the bookmark. I am fine dealing
with macros within word but getting information from another application is
foxing me.

Basically all the lawyers info is stored in the spreadsheet

initials email fax name job
dm d.jones 45 Dennis Jones Associate
fb f.bloggs 23 Fred Bloggs Partner

I want to drop this into the letter/fax template and I want it to auto run
when the template is opened.

If someone could point me in the right direction I would be eternally
grateful.

Thanking you in anticipation.

Dee

 

[Dee]

Sorry I should have said I am working in Word 2000 on Small business server
2000 and wnat to place the excel spreadsheet and the template in the same
server folder. This is so that when new people join and staff leave I can
update everything centrally once only instead of doing every pc speterately.

 

[Malcolm Smith]

Dee

Where are you based? I specialise in doing templates for law firms, both
with and without Document Management Systems. Now, are you based within
the UK because, if so, perhaps you would like to contact me off-list, via
the web-site, so that I can give you some pointers for an overall
strategy?

I ask about the UK thing because, perhaps, you could lift the phone and
give me a ring, otherwise perhaps a chat via e-mail...

- Malc
www.dragondrop.com

 

[Dee]

Malcolm I have sent you an email regarding your response. All I can say is
that they will not pay for anyone to come in and do this work. They are
expecting me to do it myself with my pile of manuals.

Well whilst I have amanaged most things they have asked me to do this has so
far stumped me. I find all the different methods confusing ans irritating
whan what I want to do is really very simple.

Dee

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Dee,

What you need is a UserForm. Malcolm has a tutorial on creating them on his
website - you should ignore his disparaging comments about my article “How
to create a Userform” at:

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

Choose for yourself, which one you want to follow.

Then, I would suggest that you copy the information from Excel into a table
in Word Document, and then the following information will tell you how to
populate a listbox on the userform with that data. The secretaries can then
select the lawyer from that list box.

This routine loads a listbox with client details stored in a table in a
separate
document (which makes it easy to maintain with additions, deletions etc.),
that document being saved as Clients.Doc for the following code.

On the UserForm, have a list box (ListBox1) and a Command Button
(CommandButton1) and use the following code in the UserForm_Initialize() and
the CommandButton1_Click() routines

Private Sub UserForm_Initialize()
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range,
m As Long, n As Long
' Modify the path in the following line so that it matches where you
saved Suppliers.doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="e:\worddocs\Clients.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.ColumnCount = j
' Define an array to be loaded with the client data
Dim MyArray() As Variant
'Load client data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub

The Initialize statement will populate the listbox with the data from the
table and then when a client is selected in from the list and the command
button is clicked, the information for that client will be inserted into a
bookmark in the document. You may want to vary the manner in which it is
inserted to suit our exact requirements, but hopefully this will get you
started.

To make it easy for you, the code has been written so that it will deal with
any number of clients and any number of details about each client. It
assumes that the first row of the table containing the client details is a
header row.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

 

[Malcolm Smith]

Dee

You have mail with an example of what you need. Let me know if you're
ever up for a pint in the Smoke.

- Malc

 

[Malcolm Smith]

Dee

You ought to have the example code by now, but if anyone else is
interested I have written up what I have done on my web site with the
template and database which is able to be downloaded.

Hope that this helps.

- Malc


http://www.dragondrop.com/WordCoding/word013.asp

 

[Dee]

Malcolm I would like to thank you very much for all your help. The example
you sent me has really helped me to understand how this works. It is
unfortunate that there is not a decent text book on creating VBA code in
Word. As I explained to you I am used to Excel and have a much better
working knowledge of it. Word just stumps me at times.

Kind Regards


Dee

 

[Charles Kenyon]

You might do better with a mailmerge and a query for the initials instead of
a macro.

--

Charles Kenyon

Word New User FAQ & Web Directory:
<URL: http://addbalance.com/word/index.htm>

Intermediate User's Guide to Microsoft Word (supplemented version of
Microsoft's Legal Users' Guide)
<URL: http://addbalance.com/usersguide/index.htm>

See also the MVP FAQ: <URL: http://www.mvps.org/word/> which is awesome!
--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.