Parsing Memo Fields using VBA

[jmriddic]

Hi,

Is it possible to parse paragraphs in memo fields into different variables? Things is I don't how many paragraphs each memo field may hold so would an array work? Anybody have a suggestion?

 

[WayneRyan]

jm,

You can certainly parse your memo fields.

You can even store the "pieces" into a string array, using the ReDim
statement in VBA.

But then, what do you do with them after that?

Need more info ...

Wayne

 

[jmriddic]

More info

Wayne,

Thanks for the reply. I am sure you have head of Word Automation. I have been using the fields to populate table cells in a word document. I have two different columns of memo fields. One column is for competency(its job related data) and the the other for assessment. Problem is that the end user is typing in multiple paragraphs of assessment in each assessment memo field for each competency. I originally intended them to hold one assessment each but thats not how the users have been filling them out. I have 25 competency and 25 assessment fields which can be filled out I wanted to parse it out of each assessment memo field into an array. In the word document I have a table of about 6 columns. Column One holds the comptenecy and in column 2 the value of the assessment field and the rest are manually filled out with the exception of column 5 which is free text which is circled based on age. I can have multiple assessments for each competency I need to created mutiple cells in column 2 depending on how many paragraphs I can parse of each assessment memo field. Basically I would be created new cells in the rest of the table as I go across horizontally for each assessment paragraph. I would also have to recreated the free text for each of these minirows in column 5. I do not now how I would dynamically create the field marker the automation uses to populate the word form uses either to compound the problem. So the problem I have is quite complex and any help would be welcomed. Thanks.

 

[WayneRyan]

jm,

Just a quick stab at it ...

Dim strParagraphs(100) As String
Dim intFrontPointer As Long
Dim intBackPointer As Long
Dim intTotalParagraphs As Long
'
' In this example, paragraphs are delimited by a "double carriage-return"
'
' First, is there more than one paragraph?
'
intBackPointer = InStr(1, Me.YourMemoField, vbCrLf & vbCrLf)
If intBackPointer = 0 Then
   ' Only 1 paragraph
   strParagraphs(1) = Me.YourMemoField
   intTotalParagraphs = 1
Else
   ' Count the paragraph, store it in an array, increment total count, then move
   ' the BackPointer to the FrontPointer, then check for any more paragraphs.
   While intBackPointer > 0
      intTotalParagraphs = intTotalParagraphs + 1
      strParagraphs(intTotalParagraphs) = Mid(Me.YourMemoField, intFrontPointer, intBackPointer - intFrontPointer)
      intTotalParagraphs = intTotalParagraphs + 1
      intFrontPointer = intBackPointer + 2
      intBackPointer = InStr(1, Me.YourMemoField, vbCrLf & vbCrLf)
      Wend
   ' Get the "last paragraph"
   intTotalParagraphs = intTotalParagraphs + 1
   strParagraphs(intTotalParagraphs) = Mid(Me.YourMemoField, intFrontPointer)

Wayne

 

[Kodo]

You could use the split function on the carriage return as your delimeter and automatically create an array.

Then from there, you loop through and perform your desired functions in the loop.

 

[jmriddic]

Please elaborate on split function

Kodo,
Never heard of split function. Have heard of "\n". Please elaborate.

Wayne, Looks good question is would I need to do a separate array for each field? Is it possible to do this in the procedure for the word document automation? I would assume so.

 

[Kodo]

Split Function

for acc2k and above. I don't think it's available for acc97.

 

[jmriddic]

Question

Looks interesting to me. Question is like I ask Wayne would I need a separate array for each field.Seems you would have to or you would empty it out before going to the next one. How would able to determine how many paragraphs you got out of the field since I need a specific number of cells and field markers and text Another question for both. Does anyone have any ideas on how to go about creating bookmarks and the rows and text. I think that is major challenge looking over the VB Code I have access to.

Here is a rough sketch outline of the code so far and also what I need

With objWord
"Code for non memo fields"

"code for memo field1 to split"
"code to create cells,field markers, rows and populate them"
"memo field2 code"

 

[Kodo]

split puts the chunks into an array. Arrays have upper and lower boundaries (ubound/lbound). To determine if the array exists you do this:

(arrvarname is the name of the variable that holds the array. You assign this variable name)


if isarray(Arrvarname) then
'do something
end if

to check the size of the array:

ArraySize=ubound(Arrvarname)

Remember that arrays are indexed and the default starting point is 0 (zero).
So an array with 5 indexes actually has 6 records.

 

[jmriddic]

Got this so far.

Maybe somebody can help me with the next bit of code here:
Private Sub AccessClin_Click()
Dim objWord As Word.Application
Dim MyString, MyArray, ArraySize As Integer
Dim Par As Integer
Set objWord = CreateObject("Word.Application")
With objWord
.Visible = True
.Documents.Open ("AnnualCompetencyformclin.doc")
.ActiveDocument.Bookmarks("Job_Title").Select
.Selection.Text = (CStr(Forms!HumanResourcesForm3!JobTitle))
.ActiveDocument.Bookmarks("Department").Select
.Selection.Text = (CStr(Forms!HumanResourcesForm3!Department))
.ActiveDocument.Bookmarks("Assessment1").Select
If IsNull(Forms!HumanResourcesForm3!Assessment1) Then
.Selection.Text = ""
Else
MyString = (CStr(Forms!HumanResourcesForm3!Assessment1))
MyArray = Split(MyString, "\n", -1, 1)
ArraySize = UBound(MyArray)

If ArraySize > 1 Then
'ActiveDocument.Bookmarks("Assessment1").Delete
For Par = 1 To ArraySize
'Split Cells
'Add Bookmark
' Put Text in Bookmark
'Move down own cell repeat the process
Loop
Else
.Selection.Text = (CStr(Forms!HumanResourcesForm3!Assessment1))
End If
End If
From about the first reference to Assessment1 Bookmark I am a bit confused what next to do here. I believe the first part after I have figured but I don't know if I still need a reference to Assessment1 because remember I first to select the current cell,maybe delete that bookmark if more than 1 paragraph exists, spilt the cell horizontally based on the number of paragraphs, move down and repeat the process. Any suggestions?

 

[WayneRyan]

jm,

I'm just guessing here, but I assume that you have
bookmarks set up for the paragraphs.

Not a strong area for me, but this is a stab at it.

Private Sub AccessClin_Click()
Dim objWord As Word.Application
Dim MyString, MyArray, ArraySize As Integer
Dim Par As Integer

Set objWord = CreateObject("Word.Application")
With objWord
  .Visible = True
  .Documents.Open ("AnnualCompetencyformclin.doc")
  .ActiveDocument.Bookmarks("Job_Title").Select
  .Selection.Text = (CStr(Forms!HumanResourcesForm3!JobTitle))
  .ActiveDocument.Bookmarks("Department").Select
  .Selection.Text = (CStr(Forms!HumanResourcesForm3!Department))
  .ActiveDocument.Bookmarks("Assessment1").Select
  If IsNull(Forms!HumanResourcesForm3!Assessment1) Then
     .Selection.Text = ""
  Else
     MyString = (CStr(Forms!HumanResourcesForm3!Assessment1))
     MyArray = Split(MyString, vbCrLf, -1, 1)
     ArraySize = UBound(MyArray)
     For Par = 1 To ArraySize
       Select Case Par
          Case 1
            .SelectionText = MyArray(1)
          Case 2
            .ActiveDocument.Bookmarks("2nd Para").Select
            .SelectionText = MyArray(2)
          Case 3
            .ActiveDocument.Bookmarks("3rd Para").Select
            .SelectionText = MyArray(3)
          Case 4
            .ActiveDocument.Bookmarks("4th Para").Select
            .SelectionText = MyArray(4)
          Case Else
            MsgBox("I didn't put a bookmark for more than 4 paragraphs.")
            Stop
          End Select
       Next Par
   End If
   End With

Wayne

 

[jmriddic]

Didn't work

Private Sub cmdApplClin_Click()
Dim objWord As Word.Application
Dim MyString, MyArray, ArraySize As Integer
Dim Par As Integer
Set objWord = CreateObject("Word.Application")
With objWord
.Visible = True
.Documents.Open ("Z:Job Docs\AnnualCompetencyformclin1.doc")
.ActiveDocument.Bookmarks("Job_Title").Select
.Selection.Text = (CStr(Forms!HumanResourcesForm3!JobTitle))
.ActiveDocument.Bookmarks("Department").Select
.Selection.Text = (CStr(Forms!HumanResourcesForm3!Department))
.ActiveDocument.Bookmarks("Assessment1").Select
If IsNull(Forms!HumanResourcesForm3!Assessment1) Then
.Selection.Text = ""
Else
MyString = (CStr(Forms!HumanResourcesForm3!Assessment1))
MyArray = Split(MyString, "\n", -1, 1)
ArraySize = UBound(MyArray)
For Par = 1 To ArraySize
Select Case Par
Case 1
.Selection.Text = MyArray(1)
Case 2
.ActiveDocument.Bookmarks("Assessment1b").Select
.Selection.Text = MyArray(2)
Case 3
.ActiveDocument.Bookmarks("Assessment1c").Select
.Selection.Text = MyArray(3)
Case 4
.ActiveDocument.Bookmarks("Assessment1d").Select
.Selection.Text = MyArray(4)
Case Else
MsgBox ("I didn't put a bookmark for more than 4 paragraphs.")
Stop
End Select
Next Par
End If

I tried this bit it just skiped over the bookmarks in question even though they existed. Not sure what is wrong.

 

[WayneRyan]

jm,

I can't tell if it is skipping over them, or putting
an empty string in the bookmarks.

You need the debugger.

Get your code in Design View.

At the first executable statement, click on the
left-margin. A red dot will appear. This is a
breakpoint. When your code executes, it will stop
there.

Select: View --> Immediate Window (from the menu)

If you hover over a variable, its value will be
displayed.

If you go to the Immediate Window (lower screen)
and type:

?MyArray(2)

It will print out the value for that array element.

F8 will single-step you through the code.
F5 will "run to completion"

Ctrl-F9 will move the next executable statement.

Experiment with it. I can't tell anything from here.

Wayne

 

[jmriddic]

Results

Wayne,

Everything is getting saved into MyArray(0). Why I don't know. Maybe the split function isn't working correctly. Any suggestions, guys?

 

[WayneRyan]

jm,

At least with the debugger, we're seeing things now.

Your split call is suspect (this is not 'C'):

MyArray = Split(MyString, "\n", -1, 1)

Maybe:

MyArray = Split(MyString, vbCrLf, -1, 1)

Or (depending on where you got the data):

MyArray = Split(MyString, Chr(10), -1, 1)
MyArray = Split(MyString, Chr(13), -1, 1)

Now, you can use the debugger, try the various split commands, view
results in Immediate window, then use Ctrl-F9 to "rerun" the split.

We're getting closer,
Wayne

 

[jmriddic]

Results

Thanks Wayne

I used vbCrLf and it work!. Like your advice though,
I set up twelve bookmarks to case through. If for example the case terminates after six paragraphs I need to delete the rest of the
the bookmarks and the rows they are on. I do have some code to do this like below but not sure where to stick it in relevance to the case statement. In the past I have checked the memo field(specifically the Competency field) to see if its was Null before deleting the rows in the document now I need to check the bookmark and do the same. Your suggestion?

.Selection.Text = ""
.Selection.Rows.Delete

 

[WayneRyan]

jm,

Glad to hear it.

Unfortunately, MS Word is not a database. You have to specifically allocate
x number of bookmarks. If they have (x+1), then Word can't handle it.

Maybe, what needs to be done is to let each paragraph
be a ROW in a table (a subform).

But, the RECEIVING Word document can't programmatically handle it.

To me, there's no clear answer.

However, you did overcome your short-term problem and learned how to use
the debugger.

I do not however, deal with Word as a destination for a "report" .. Dunno

Next Step?

Wayne