Storing maintainable paragraphs of text

[Jfp87]

Guys,

This is sort of related to a previous question of mine, but slightly different.

I will be storing a lot of standard paragraphs of text, all of which must be maintained by the user. Each paragraph will relate to a specific option selected in the userform ('No' = one paragraph, 'Yes' = another paragraph, 'Maybe' = some other paragraph etc.). There is around 70-100 pages each containing these standard paragraphs. I am trying to work out the best way of storing them so that they can be accessed quickly & efficiently but also be maintained.

I have tried Word tables (good to look at visually and easy to maintain for the user but slow) and .ini files (quick & efficient but unfamiliar format and not designed to store paragraphs of text as far as i know).

My next options are 1) .txt file 2) .txt/.ini file with the data being transferred into userform textboxes and maintained that way (with the userform being an interface between the .txt/.ini file).

I hope I have explained it clearly enough. Any help or suggestions are appreciated.

Thanks again,
Joe

[gmayor]

If you are considering using text files, it implies that there is no formatting in the paragraphs. That being the case, I would suggest using an Excel worksheet. You can read the worksheet into an array with ADO which is virtually instantaneous then interrogate the array.

The following code will read the worksheet into an array:


Option Explicit

Function xlFillArray(strWorkBook As String, _
strWorksheetName As String) As Variant
Dim RS As Object
Dim CN As Object
Dim lngRows As Long

strWorksheetName = strWorksheetName & "$]"
Set CN = CreateObject("ADODB.Connection")
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkBook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

Set RS = CreateObject("ADODB.Recordset")
RS.Open "SELECT * FROM [" & strWorksheetName, CN, 2, 1

With RS
.MoveLast
lngRows = .RecordCount
.MoveFirst
End With
xlFillArray = RS.GetRows(lngRows)
If RS.State = 1 Then RS.Close
If CN.State = 1 Then CN.Close
lbl_Exit:
Set RS = Nothing
Set CN = Nothing
Exit Function
End Function

[Jfp87]

Thanks Graham, I think that is the sort of thing I am looking for. I will try that.

Joe