Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / January 2007

Tip: Looking for answers? Try searching our database.

Transfer data from word table to userform

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
darkhorizon2002@yahoo.com - 12 Jan 2007 04:48 GMT
Hi,

Is it possible to transfer data from a word table to a Userform?  I
have a userform with several textboxes which I would like to populate
using the data in a word table that is saved in a separate document.

Regards

J
Doug Robbins - Word MVP - 12 Jan 2007 11:48 GMT
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.  It can be
modified to do what you want.

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 Clients.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.

Signature

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

> Hi,
>
[quoted text clipped - 5 lines]
>
> J
darkhorizon2002@yahoo.com - 13 Jan 2007 19:24 GMT
Hi Doug,

In my research, I came upon your code but not sure how to apply it to
my project.  The assignment entails that the user have the option to
add a new name to the file the combobox in the userform refers to if
the name is not listed.  However, now the user would like to
select/create a name and then have other textboxes in the userform
populated with the contact info of that name once the selection is
made.

I thought the best way to do this was to create a table in a separate
word document that holds the name and contact info and have the combo
box refer to the table, once the selection is made the textboxes would
retrieve the contact data in the row in the table of the name selected.
That's why your code was of interest to me, however a bit unclear as
how to have the option to add the new name to the table if name not
listed as well as how to populate the textboxes in the userform since
your code refers to bookmarks in the active doc.

Another thought was to attempt to do this in excel if not possible in
word.

I'd love to hear any ideas you may have in this matter.

> This routine loads a listbox with client details stored in a table in a
> separate
[quoted text clipped - 79 lines]
> >
> > J
Arche - 13 Jan 2007 22:27 GMT
In Doug's reply below the VBA code takes all rows and columns in the
sourcedoc table and polulates the listbox in the UserForm.  I found
this to be very usefule and I have the need to pull a specific row and
column based on a variable.  Can anyone provide some basic syntax or
example to return a specific row and column based on Doug's example
below?

Thank you!

On Jan 13, 1:24 pm, darkhorizon2...@yahoo.com wrote:
> Hi Doug,
>
[quoted text clipped - 105 lines]
>
> > > J- Hide quoted text -- Show quoted text -
Doug Robbins - Word MVP - 14 Jan 2007 07:46 GMT
That is what the code in the Private Sub CommandButton1_Click() routine
does.

Signature

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

> In Doug's reply below the VBA code takes all rows and columns in the
> sourcedoc table and polulates the listbox in the UserForm.  I found
[quoted text clipped - 127 lines]
>>
>> > > J- Hide quoted text -- Show quoted text -
darkhorizon2002@yahoo.com - 14 Jan 2007 12:43 GMT
Hi Doug,

Unlike ARCHE, I understand what your code does; however, my question
referred to is it possible to make it a bit more free form giving the
user the flexibility to add a name to the table via the userform if the
name is not listed?

> That is what the code in the Private Sub CommandButton1_Click() routine
> does.
[quoted text clipped - 138 lines]
> >>
> >> > > J- Hide quoted text -- Show quoted text -
Doug Robbins - Word MVP - 14 Jan 2007 17:27 GMT
I would probably have a button on the form that is clicked when the user
want to add a new name and the code behind that button would then:

1.    Display the required text boxes into which the user could enter that
information
2.    Display an Add Item and a Cancel button.

The Cancel button would hide the textboxes

The Add Item button would validate that the user had entered the necessary
information and would then access the document containing the information
that is displayed in the combobox and add a row to that table into which it
would insert the information from the textboxes.
It would then sort the items in the table in the Word document and save it,
then it would delete all of the items from the combobox and then repopulate
the combobox with the items from the table that would now include the new
item.  It would also delete the information from and hide the textboxes into
which the user entered the details of the addition.

Here's some code that does the adding the row to a table in the Word
document and then populates the cells of that row with information from a
userform

Private Sub cmdContinue_Click()
Dim Log As Word.Document
Dim logrange As Range, logtable As Table

   With Word.Application
       Set Log =
.Documents.Open("h:\projet\9501c-sangaredi\11-client\document
management\logout.doc")
   End With

   With Log
       Set logtable = .Tables(i)
       logtable.Rows.Add
       rownum = logtable.Rows.Count
       With logtable
           .Cell(rownum, 1).Range = txtLogNum
           .Cell(rownum, 2).Range = Format(Date, "MMM dd, yyyy")
           .Cell(rownum, 3).Range = txtaddressee
           .Cell(rownum, 4).Range = txtcompany
           .Cell(rownum, 5).Range = txtsubject
       End With
       .Save
       .Close
   End With

Signature

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

> Hi Doug,
>
[quoted text clipped - 159 lines]
>> >>
>> >> > > J- Hide quoted text -- Show quoted text -
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.