Excel Values in Word listbox - resolved

OK. So, there are a few ways to do this - Word Help says via a DDE connection. I have scoured the forums here to see how I can possibly do it, including Dim'ing new objects and applications... but I am at a loss.

This is the plan;

1 x Excel file that has two columns. Column A has the title of a document. Column B has the server path to that document. There will be about 30 documents - therefore, 30 lines in the excel file (for now, called "Extras.xls"). So - Extras.xls could have this as the first line;
R1C1: File1 R1C2: C:\File1.doc

1 x Open, blank word document. Run a macro that shows a form. On that form I simply want a listbox, listing the values in Extras.xls - Column A.

When you select one of the values in the listbox, and click on a button, I would like word to open the particular word document as listed in Column B of the excel sheet, Copy the text in that document, close that document, and then paste it into the Blank document that you started with. Then the user can choose another record in the listbox, and repeat the process for that record...

So - a new document is created, made up of lots of other smaller documents, based on which records the user chooses from within that listbox. Does this make sense?

Ideas, or sample code anyone?

Scott.

Hi all. I see a lot of people have looked here, but not replied - so here is a solution if you are interested... managed to sort it out. Connecting to an Access db was a better way to go.

VB Code:

Private Sub UserForm_Initialize()
 
    Dim rstTitles As ADODB.Recordset
    
    Set rstTitles = CreateObject("ADODB.Recordset")
    rstTitles.Open Source:="SELECT * FROM qTitles", _
        ActiveConnection:="provider=microsoft.jet.oledb.4.0;data source=p:\Endorsements.mdb", _
        CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly
        
    Do Until rstTitles.EOF = True
        lstTitles.AddItem rstTitles.Fields("Title").Value
        rstTitles.MoveNext
    Loop
    
    lstTitles.ListIndex = 0
    
    rstTitles.Close
    Set rstTitles = Nothing
End Sub
 
 
Private Sub InsertButton_Click()
 
    Dim Title As String
 
    Title = lstTitles
   
    Dim cnnLogs As ADODB.Connection
    Dim strSQL As String
    strSQL = "INSERT INTO Log (Endorsement) VALUES ('" & Title & "');"
    Set cnnLogs = CreateObject("ADODB.Connection")
 
    cnnLogs.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
            "Dbq=p:\Endorsements.mdb;"
 
    cnnLogs.Execute (strSQL)
        
    ChangeFileOpenDirectory "P:\Clauses\"
    Documents.Open FileName:="""" & Title & ".doc""", _
            ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
            PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
            WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
            wdOpenFormatAuto
    Selection.WholeStory
    Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
    Selection.Copy
    ActiveDocument.Close
    Selection.PasteSpecial Link:=False, DataType:=wdPasteHTML, Placement:= _
            wdInLine, DisplayAsIcon:=False
            
    cnnLogs.Close
 
End Sub

This initialises the form with the contents of the listbox having the contents of the table "Titles" in the database, "Endorsements". When you choose a particular title, the appropriate document is opened, copied, closed and then inserted into the open document. Also, the title is inserted into the database, to keep a track of when a particular clause is used - reporting issue.

Thanks to all that checked it out - hope you find this useful. :)