User Information

[jay]

Hi,

Rather than creating a series of templates for individual users I'm looking
to create a standard template for all users that populates a users name,
email address, job title and phone number.

I thaught of looking up username from a database in a shared location,
anyone know how to achieve this or even suggest another approach

Thanks in advance,

Jay

 

[Howard Kaikow]

You would use the Auto* macros to modify whatever is needed when a user
opens/creates a document based on the template.

 

[Tammy]

Hi Jay,

You need to connect to the database using a connection string which
contains the name of the server, database, password if any. You then
return the required records to a recordset. You can use the
ExecuteSQL command to run a query if you don't want all the records
return. This is pretty vague but will give you an idea of what to
look for.

Another way to set it up is to keep the information in an ini file and
use Word's system.privateprofilestring command to return the
information. I've set this up before so the users' information is
stored in a table in a Word document. This makes it easy for people
to maintain. Once they've made their changes a macro is run to create
an ini file based on that information. Templates are linked to this,
so for example there's a text field where users can enter someone's
initials. The text box then displays that user's name and the rest of
the information (ie email address, phone number) is saved into
variables and used in the document.

I used this way because connecting to the database where this
information was stored wasn't viable. There were 7 separate offices
and so many people the information took too long loading into the
templates. I set up different ini files for each centre so only their
people would be included - much quicker. Plus the information wasn't
being kept up to date. Ideally though it would be better to connect
to the database so information isn't stored in two places.

Hope this helps.

Tammy

 

[jay]

t'daaaaa

Many thanks Tammy, that works a treat, based on your lead I found an
excelent page in microsoft KB (Article 237436) -
http://support.microsoft.com/default.aspx?scid=kb;en-us;237436&Product=OFF2003

now what I want to do is get a combo box to initialize from the ini file and
I'll be a very very happy bunny - but that's a project for tomorrow

once again Tammy, thankyou very much

Kind regards,

Jay

 

[Tammy]

Glad I could help - if you'd like some code that I have which puts the
ini file info into a combo box let me know.

Cheers,
Tammy

 

[jay]

thanks Tammy, very nice of you to offer.

I'm going to have a play arround now with it and see what I can come up
with, though your code will come very handy though. Please do feel free to
mail me directly.

I'll let you know how I get on

Regards,

Jay

 

[jay]

Hi Tammy

I've managed to achieve this with 2 ini files but can't figure out how to do
it with just one

first of all I initialize the combo box from a file called userindex.ini
that looks a bit like this:

[1]
Index=1
Name=Jay
[2]
Index=2
Name=Tammy

with this code:

Public Sub UserForm_Initialize()

Dim i As Integer ' defines the integer it index
Dim sName As String ' defines the string to hold the name

' looks at up to 30 i's in userindex.ini and returns the Name field into
the combo box
For i = 1 To 30 ' for each i
' make sName = to the Name field
sName = System.PrivateProfileString("\\path\userindex.ini", i, "Name")
' and add it to the combo box list
cmbSelectName.AddItem sName
Next i


End Sub

then get the userdata from a userinfo.ini containing something like this:

[Jay]
Name=Jay
JobTitle=Junior Assistant Trainee Macro Writer
[email protected]
phone=01234 567 890
[Tammy]
Name=Tammy
JobTitle=Senoir Macro Guru
[email protected]
phone=09876 543 210

and returns it with this code:

Public Sub cmdOK_Click()
'code for OK button goes here

Dim sName As String ' defines the string to hold the name
Dim sJobTitle As String ' defined the string to hold the job
title
Dim sEmail As String ' defines the string to hold the email
Dim sPhone As String ' defines the string to hold the phone

' pulls the field from the userinfo.ini and holds it in the relivent
string
sName = System.PrivateProfileString("\\path\userinfo.ini",
cmbSelectName.Value, "Name")
sJobTitle = System.PrivateProfileString("\\path\userinfo.ini",
cmbSelectName.Value, "JobTitle")
sEmail = System.PrivateProfileString("\\path\userinfo.ini",
cmbSelectName.Value, "email")
sPhone = System.PrivateProfileString("\\path\userinfo.ini",
cmbSelectName.Value, "phone")

Selection.GoTo What:=wdGoToBookmark, Name:="name" ' goto the
bookmark
Selection.TypeText Text:=sName ' write the
contents of the string
Selection.GoTo What:=wdGoToBookmark, Name:="from" ' goto the
bookmark
Selection.TypeText Text:=sName ' write the
contents of the string
Selection.GoTo What:=wdGoToBookmark, Name:="job" ' goto the
bookmark
Selection.TypeText Text:=sJobTitle ' write
the contents of the string
Selection.GoTo What:=wdGoToBookmark, Name:="email" ' goto the
bookmark
Selection.TypeText Text:=sEmail ' write the
contents of the string
Selection.GoTo What:=wdGoToBookmark, Name:="phone" ' goto the
bookmark
Selection.TypeText Text:=sPhone ' write the
contents of the string
Selection.GoTo What:=wdGoToBookmark, Name:="home" ' goto home (top
of document)

Unload Me
End Sub



Because I don't want to edit the code each time I add or remove a user I've
used a For Loop to populate the combo box, but I can only do that if the
index is an integer (am I correct in thinking that?)

However, since I've got an integer as the index, I can't then use the same
ini file to get the userinfo because I'm returning it with combobox.value
which is the username not the index value

It works, as it stands, and I can write a macro that will ammend both files
at the same time, but it just seems that it's not a very elegent way of
achieving the result.

Any suggestions??? (anyone?)

Kind regards,

Jay

 

[Tammy]

Hi Jay,

What I do with mine is instead of having the sections named the
username, I have given it a generic name (User#) and then I have
another section which tells you how many users their are ie

[NumberofUsers]
Number=2

[User1]
Name=Jay
JobTitle=Junior Assistant Trainee Macro Writer
[email protected]
phone=01234 567 890
[User2]
Name=Tammy
JobTitle=Senoir Macro not really a guru
[email protected]
phone=09876 543 210

Then I have a dynamic multi-dimensional array picking up the info ie

dim strUserFile as String 'String variable to hold user
ini filename
dim iNumber as Double 'iNumber holds the number of
users
dim i as Double 'i is a counter
dim strUserNumber 'used to hold the section name
(User1)

'I try and use a double type as vb.net no longer recognises integers,
this may be a problem in later version of Word (better safe than sorry
anyway) ...

strUserFile = "\\path\userindex.ini"

iNumber = System.PrivateProfileString (strUserFile, "NumberOfUsers",
"Number")

ReDim UserDetails(iNumber, 3)
For i = 0 To iNumber - 1
strUserNumber = "User" + Trim(Str(i + 1))
x = 0
UserDetails(i, x) = System.PrivateProfileString(strUserFile,
strUserNumber, "Name")
x = 1
UserDetails(i, x) = System.PrivateProfileString(strUserFile,
strUserNumber, "JobTitle")
x = 2
UserDetails(i, x) = System.PrivateProfileString(strUserFile,
strUserNumber, "Email")
x = 3
UserDetails(i, x) = System.PrivateProfileString(strUserFile,
strUserNumber, "Phone")
Next i


The UserDetails array is a global variant declared in a global module
I have. I then use it in a variety of different macros. The users'
name is set in a combo box

dim i as Double 'used for counting

For i = 0 To UBound(UserDetails) - 1
cboUserName.AddItem UseDetails(i, 0)
Next i

and then the list index of that combo box is used to determine which
user has been chosen ..

ie

dim i as Double 'used for counting
dim strName as string
dim strJobTitle as string
dim strEmail as string
dim strPhone as string

i = cboUserName.listindex

If Not i = -1 Then
strName = UserDetails (1,0)
strJobTitle = UserDetails(i, 1)
strEmail = UserDetails(i, 2)
strPhone = UserDetails(i, 3)
End If


I have a Word document which holds a table with all the user
information in it. This is maintained by the IT department in each
centre. All they need to do is open the document, make the changes
then I have a macro which creates the ini file based on that table.
The NumberOfUsers is the number of rows in the table then it loops
through and adds each user. At one stage I did automate it further
and have a dialog box come up and they edited/deleted etc. through
that but they preferred the table as it was easy to use and they could
sort on whatever column they wanted. All they need to do is press a
button once the document has been amended and they're set!

cheers,
Tammy

Hi Tammy

I've managed to achieve this with 2 ini files but can't figure out how to do
it with just one

first of all I initialize the combo box from a file called userindex.ini
that looks a bit like this:

[1]
Index=1
Name=Jay
[2]
Index=2
Name=Tammy

with this code:

Public Sub UserForm_Initialize()

Dim i As Integer ' defines the integer it index
Dim sName As String ' defines the string to hold the name

' looks at up to 30 i's in userindex.ini and returns the Name field into
the combo box
For i = 1 To 30 ' for each i
' make sName = to the Name field
sName = System.PrivateProfileString("\\path\userindex.ini", i, "Name")
' and add it to the combo box list
cmbSelectName.AddItem sName
Next i


End Sub

then get the userdata from a userinfo.ini containing something like this:

[Jay]
Name=Jay
JobTitle=Junior Assistant Trainee Macro Writer
[email protected]
phone=01234 567 890
[Tammy]
Name=Tammy
JobTitle=Senoir Macro Guru
[email protected]
phone=09876 543 210

and returns it with this code:

Public Sub cmdOK_Click()
'code for OK button goes here

Dim sName As String ' defines the string to hold the name
Dim sJobTitle As String ' defined the string to hold the job
title
Dim sEmail As String ' defines the string to hold the email
Dim sPhone As String ' defines the string to hold the phone

' pulls the field from the userinfo.ini and holds it in the relivent
string
sName = System.PrivateProfileString("\\path\userinfo.ini",
cmbSelectName.Value, "Name")
sJobTitle = System.PrivateProfileString("\\path\userinfo.ini",
cmbSelectName.Value, "JobTitle")
sEmail = System.PrivateProfileString("\\path\userinfo.ini",
cmbSelectName.Value, "email")
sPhone = System.PrivateProfileString("\\path\userinfo.ini",
cmbSelectName.Value, "phone")

Selection.GoTo What:=wdGoToBookmark, Name:="name" ' goto the
bookmark
Selection.TypeText Text:=sName ' write the
contents of the string
Selection.GoTo What:=wdGoToBookmark, Name:="from" ' goto the
bookmark
Selection.TypeText Text:=sName ' write the
contents of the string
Selection.GoTo What:=wdGoToBookmark, Name:="job" ' goto the
bookmark
Selection.TypeText Text:=sJobTitle ' write
the contents of the string
Selection.GoTo What:=wdGoToBookmark, Name:="email" ' goto the
bookmark
Selection.TypeText Text:=sEmail ' write the
contents of the string
Selection.GoTo What:=wdGoToBookmark, Name:="phone" ' goto the
bookmark
Selection.TypeText Text:=sPhone ' write the
contents of the string
Selection.GoTo What:=wdGoToBookmark, Name:="home" ' goto home (top
of document)

Unload Me
End Sub



Because I don't want to edit the code each time I add or remove a user I've
used a For Loop to populate the combo box, but I can only do that if the
index is an integer (am I correct in thinking that?)

However, since I've got an integer as the index, I can't then use the same
ini file to get the userinfo because I'm returning it with combobox.value
which is the username not the index value

It works, as it stands, and I can write a macro that will ammend both files
at the same time, but it just seems that it's not a very elegent way of
achieving the result.

Any suggestions??? (anyone?)

Kind regards,

Jay

thanks Tammy, very nice of you to offer.

I'm going to have a play arround now with it and see what I can come up
with, though your code will come very handy though. Please do feel free to
mail me directly.

I'll let you know how I get on

Regards,

Jay


http://support.microsoft.com/default.aspx?scid=kb;en-us;237436&Product=OFF2003 file
and I'm
looking users
name,