Read from document to ListBox

[dsc]

I know this must have been answered before, and I'm very sorry, but I just
can't find it.

Nine recently opened files is just not enough. I need about 50.

I thought I would write the recently opened file names to a word document,
then read them from the document to a UserForm ListBox.

I figured out how to write them to the file--sort of, I have to open the
file to write them--but I can't figure out how to read them back from the
document to the ListBox.

I would much appreciate any assistance with reading lines of text from a
document into the lines of a ListBox.

Thank you very much.

 

[Doug Robbins]

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


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

 

[dsc]

Thanks, much appreciated.

 

[dsc]

Doug,

I've been trying to use your code with a macro and userform as follows:

The basic macro:

Public Sub RecentFiles()



RecentFilesForm.Show



End



End Sub



******************************************


And the code in RecentFilesForm:

Private Sub UserForm_Initialize()



Application.ScreenUpdating = False



Dim RFArray() As Variant

Dim sourcedoc As Document, i As Integer, j As Integer, m As Long, myitem As
Range, n As Long





'********** Do something here to check for the existence of C:\Recent Files
List.doc

Set sourcedoc = Documents.Open(FileName:="C:\Recent Files List.doc")



' Get the number of rows in the table

i = sourcedoc.Tables(1).Rows.Count



'Define an array to be loaded with the client data

ReDim RFArray(i)



'Load client data into MyArray

For m = 0 To i - 1

Set myitem = sourcedoc.Tables(1).Cell(m + 1, n + 1).Range

myitem.End = myitem.End - 1

RFArray(m) = myitem.Text

Next m



' Load data into ListBox1

ListBox1.List() = RFArray



' Close the file containing the client details

sourcedoc.Close SaveChanges:=wdDoNotSaveChanges



End Sub

Private Sub CloseButton_Click()



End



End Sub



******************************************



Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)



'Check to see if anything is selected

With ListBox1

If .ListIndex = -1 Then

MsgBox "Nothing is selected. You must make a selection, go back, or
cancel."

Else

RecentFilesForm.Hide

Documents.Open FileName:=ListBox1.Value

End

End If

End With



End Sub



******************************************



Private Sub OkayButton_Click()



'Check to see if anything is selected

With ListBox1

If .ListIndex = -1 Then

MsgBox "Nothing is selected. You must make a selection, go back, or
cancel."

Else

RecentFilesForm.Hide

Documents.Open FileName:=ListBox1.Value

End

End If

End With



End Sub



******************************************



The problem is, when I open word and click on RecentFiles before I do
anything else, I get error 91. If I then choose debug, it takes me to the
RecentFilesForm.Show line of the basic macro. If I then click on the Run
Macro error, it runs to the end, and will run perfectly well from then on
until I close Word again.



It only happens when the first thing I do after opening Word is trying to
run this macro. If I open a file first, it doesn't happen. I read the Help
on error 91 and tried a few things, but I'm just stumped.



Any idea why this is happening?



Best regards,



dsc

 

[Doug Robbins]

The problem would have to be in the initialize() event of the userform and I
suspect that it may be something to do with the C:\Recent Files List.doc.
Try running a macro that contains the following command when you first start
Word and see if it causes a problem:

Dim sourcedoc As Document
Set sourcedoc = Documents.Open(FileName:="C:\Recent Files List.doc")


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

 

[dsc]

Doug,

If I comment out the AutoOpen and AutoClose macros, it runs without a hitch.

If I don't comment them out, I get stack overflow errors because (I think)
it loops trying to open Recent Files List over and over again.