Storing and inserting multiple user information in Msoft Word

[colin81]

Hi all

Im new to the vba world and need a bit of hand with something.....

I have a letter template which uses a userform to fill in a recipients address etc on a document (this bit i am alright with). The letter also needs to hold a certain amount of user information (more then can be held in user information!). I want the user to be able to select the person they are writing for from a personal list which is stored on a drive and can be reused (they would select the user from a combobox). Is there anyway I can store files with data in that can then be used on a document? The user must be able to add/edit/delete people from the list.

Thanks for reading

Colin

 

[RobBroekhuis]

How is the information structured? Is it just plain text? Depending on what other applications reside on your desktop, you could set up a file in Excel or Access (or other database) to hold the information. But in the absence of that, a simple text file would work just fine too. How to structure it depends on the nature of your information. And you'd have to write the infrastructure for adding/deleting people in VBA code.


Rob

 

[colin81]

Hi Rob

Thanks for replying

Users have excel so it might be good to use this. Unfortuately I have no idea how to add or delete people within an excel spreadsheet. I better start looking on the wb!

Thanks
Colin

 

[RobBroekhuis]

Most likely, you'd store your user information on a single Excel worksheet, one line per user. Then deleting a user is as simple as deleting a line from the worksheet, and adding one would be either appending a line to the bottom, or, if you keep your list sorted, inserting a line at the proper location. Both are easy, and we'd be happy to help you figure out specific questions.


Rob

 

[colin81]

Cool cheers mate,

The first question i have is how to do i get a list of data from excel into a combo box in a word userform, perhaps just the first column of a table would be displayed in the combo box. This would need to take into account that alot of users could be added into the spreadsheet (perhaps ranges?) ?

The second question would be how would I get it so if a user selects a users name from the combo box above it enters there details into a document from the spreadsheet in specific locations (i.e. where bookmarks are) ? This would be after the user presses an ok button.

Oh and third does anyone recommend a decent (and quite in depth) book about vba...we have a sams teach yourself book and its pants!

Many Thanks
Colin

 

[RobBroekhuis]

You're either gonna find a good book on Excel VBA, or on Word VBA - not both. And for this project, you need a bit of both, although it seems like most of the tricky bits will be in Word. I have Word 2000 Developer's Handbook by Hart-Davis, which is okay, not stellar. The questions about inserting information at bookmarks that you're asking would certainly be addressed. Most of the rest is basic userform usage. For example, to populate your combobox (named "comboUser&quot from Excel, you'd do something like:

sub userform_activate
dim cell as excel.range, xlWbk as excel.workbook
set xlWbk=createobject("users.xls&quot
set cell=xlWbk.sheets("users&quot.range("A2&quot
do while cell<>&quot;&quot;
comboUser.additem cell
set cell=cell.offset(1,0)
loop
end sub

You'd need to set a reference to the Microsoft Excel object model in your Word VBA project


Rob