Question Locked

Exporting Text from Access Table into RTF files

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have created a simple table in Access 2000 containing just two fields in each record: Name and Contents. I have around 200 lines in the table (200 records) so there are 200 Names with 200 corresponding pieces of associated text in the Contents field.
I want to create a separate RTF file for each separate record that contains the text from the Contents field and named after the Name field thus:
Name Content
file1 blah, blah, blah, blah
file2 yada, yada, yada, yada
file3 etc

The desired result would be a file named file1.rtf containing just the text blah, blah, blah, blah
and a file named file2.rtf containing just the text yada, yada, yada, yada
all the way down the list of 200 records.
Help!!!

3 replies

by pantera4life00

In a module, create a new function:
You may also need to set a reference to (Microsoft ActiveX Data Objects 2.x)

Public Sub Whatever()
Dim rst As Object
Dim tbl As String
Dim fldr as String

'Change this var to the name of the table
tbl = "Table1"

'Change this var to the path
'you would like to save the final docs to
'Make sure to end it with \
fldr = "C:\Folder\"

Set rst = CreateObject("ADODB.Recordset")

rst.Open _
"Select * From " & tbl, _
CurrentProject.Connection, _
adOpenKeyset, _
adLockOptimistic

Do Until rst.EOF
'This will create a text document
'instead of an rtf doc
'Make sure the field names
'match the fields in your table

Open fldr & rst.Fields("Name") & _
".txt" For Output As #1

Print #1, rst.Fields("Content")

Close #1
rst.MoveNext
Loop

rst.close
Set rst = Nothing

End Sub

by pantera4life00

'Call this function after building
'the text document and pass in
'the location of the text document

Public Sub ConvertTextDocToRTF(TextDoc as String)

Dim wdApp As Object
Dim wdDoc As Object
Dim FilePath As String
Dim FileToOpen As String

If Dir(TextDoc) = "" Then
Msgbox "File Not Found: " & TextDoc
Exit Sub
End If

FilePath = _
Replace(TextDoc, Dir(TextDoc), "")
FileToOpen = Dir(TextDoc)

Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
wdApp.ChangeFileOpenDirectory FilePath

Set wdDoc = wdApp.Documents.Add(FileToOpen)
wdDoc.SaveAs FilePath & Left(FileToOpen, Len(FileToOpen) - 3) & "rtf", 6 'wdFormatRTF

'Delete Text Document
Kill FileToOpen

wdDoc.Close

Set wdDoc = Nothing
Set wdApp = Nothing

End Sub

'AFTER Close #1 in previous post
'Call this function and pass the location
'of the txt document you built:

Call ConvertTextDocToRTF( _
fldr & rst.Fields("Name") & ".txt")

This discussion is closed to new replies.