Excel Values in Word listbox - resolved

[Scott_MacKenzie]

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.

[Scott_MacKenzie]

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.


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