Need to move tons of word documents into Access

[dakcg]

Greetings All,

I have a potential client that is using an access database, tons of word templates and excel spreadsheets. They are having problems with word merge. The way it works now, they have to do one document at a time, constantly switching back and forth. I would like to put all of the templates in Access, giving them the option to choose the reports they want to print out.

My questions are:

1) Would having a 100 or so reports in a database be too much for Access to handle? They are mostly legal documents, average of 4 pgs each.

2) Is there an easier way to import the word documents without having to copy and paste all the info.

Thanks for any help.

dakcg

 

[richary]

I had a problem similar to yours.

What I did was to keep the docs outside access (client wanted to update templates as before via Word).

I used the dir() funtion to build a listbox which listed the filenames of all the word templates. The user chose which one(s) he/she wanted and access did a mail merge through word using automation.

 

[dakcg]

Thanks for the reply, question- Using automation, which I am now studying up on, how do I refer in the code to the list box and the selections? I really need to have many documents open in word and print without the user having to do anything other than picking the documents they want and pushing a button.

Thanks again for any help
dakcg

 

[richary]

The approach I took was to get a list of all teh word template files in teh directory into a list box. The List box's row source type is a "Value List". The row source data is 'poked' into the list box on the form's Load event:

Private Sub Form_Load()

MyPath = DB_LOCATION & "\Templates\*.dot" ' Set the path.
Myname = Dir(MyPath, vbNormal) ' Retrieve the first entry.
Me.lstFiles.RowSource = ""
Do While Myname <> "" ' Start the loop.
Me.lstFiles.RowSource = Me.lstFiles.RowSource & ";" & Myname
Myname = Dir ' Get next entry.
Loop

Me.lstFiles.RowSource = Mid(lstFiles.RowSource, 2, Len(Me.lstFiles.RowSource) - 1)
End Sub

Basically pinched from the example in Help for Dir()

The user selects the document from the list box and clicks a command button (the mail merge records have already been selected prior to this form)

Here is the code. I've taken out some irrelavent stuff to aid readability. Hoepfully I haven't missed anything.

Dim objWord As Word.Application
Dim objWordDoc As Word.Document
Dim db As Database
Dim rstPupils As Recordset
Dim rstLog As Recordset

Set objWord = CreateObject("Word.Application")
Set objWordDoc = objWord.Documents.Add(DB_LOCATION & "\Templates\" & Me.lstFiles)


With objWord
.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
.ActiveDocument.MailMerge.OpenDataSource Name:= _
DB_LOCATION & "\Templates\otformat.txt", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _
:="", SQLStatement1:=""
.ActiveDocument.MailMerge.EditMainDocument
' .ActiveDocument.MailMerge.DataSource.QueryString = .ActiveDocument.MailMerge.DataSource.QueryString & _
" ORDER BY " & Me.cmbSortedBy

' insert field codes. locations in doc marked with bookmarks

.Selection.GoTo What:=wdGoToBookmark, Name:="CName"
.ActiveDocument.MailMerge.Fields.Add Range:=.Selection.Range, Name:="FName"
.Selection.TypeText Text:=" "
.ActiveDocument.MailMerge.Fields.Add Range:=.Selection.Range, Name:="LName"
.ActiveDocument.Bookmarks("school").Select
.ActiveDocument.MailMerge.Fields.Add Range:=.Selection.Range, Name:="School"
End With

objWordDoc.MailMerge.Destination = wdSendToNewDocument

'The ActiveDocument is now the merged document,
'not the main document

objWordDoc.MailMerge.Execute
objWord.ActivePrinter = cmbPrinter

If Not chkDontPrint Then
objWord.ActiveDocument.PrintOut Background:=False
End If

objWordDoc.Close (False)
Set objWordDoc = Nothing
Set objWord = Nothing

End Sub

It's not pretty, but it does work (usually!)

I think the main changes for you, would be to

(a) Change the list box to be a multi-select box
(b) In the button click event iterate over the documents selected and do a mail merge for each one.
(c) For the client's convience, the word template is a normal one (ie not a mail merge) to aviod problems with data sources, field codes etc. The locations for the field codes are marked with bookmarks and the code above inserts the fields, data sources etc. You may wish to remove that code.

The only thing I am unsure about, is that I think there is a limit to the number of characters in a list box's row source (2048, I think) With 100 filenames maybe you would hit that limit, I don't know. In which case you would need to write a callback function for the listbox.

Hope it helps

PS Can anyone tell me why my code doesn't indent when I quote it??