Hi

I have an access database with a table, within which I have a memo field.

In a folder on my hard drive, I have over 2000 word documents. For every word document, I would like to create a new record in the table, and copy and paste the word document into the memo field. Once the documents are in the database, we use a query to search based on the text in the memo field.

Can anyone help me with automating this process? We currently do this manually (i.e. copy and paste each document into Access) but we need to automate it now that we have so many documents to paste in.

Thanks
Kate

Assume a table like the following:

MyTable
-------------------
FilePath - Text/255
MemoText - Memo

The following Sub will loop through all .DOC files in the specified folder, and append a record for each one into the table MyTable with the full File Path and the Text of the Doc file:

Public Sub GetWordDocs(ByVal sFolderPath As String)

Dim appWord As Object
Dim appDoc As Object

Dim rs As DAO.Recordset

Dim sCol As Collection
Dim vCol As Variant

If Right(sFolderPath, 1) = "\" Then
sFolderPath = Left(sFolderPath, Len(sFolderPath) - 1)
End If

If Dir(sFolderPath, vbDirectory) = "" Then Exit Sub

Set sCol = New Collection
vCol = Dir(sFolderPath & "\*.doc")

Do While vCol > ""
sCol.Add sFolderPath & "\" & vCol
vCol = Dir
Loop

Set appWord = CreateObject("Word.Application")
Set rs = DBEngine(0)(0).OpenRecordset("MyTable", dbOpenDynaset)
For Each vCol In sCol
Set appDoc = appWord.Documents.Open(vCol, , True)
With rs
.AddNew
.Fields("FilePath") = vCol
.Fields("MemoText") = Left(appDoc.Range.Text, 65536)
.Update
End With
appDoc.Close False
Set appDoc = Nothing
Next vCol

rs.Close
Set rs = Nothing

appWord.Quit False
Set appWord = Nothing

Set sCol = Nothing

End Sub


Call this Sub with something like:
GetWordDocs "C:\MyWordDocs"