Solved

VB - StrLen and MERGEFIELD

Posted on 2006-07-05
26
976 Views
Last Modified: 2012-08-13
The below Visual Basic code has two bugs I cannot figure out.
1) line 'a' should place whatever text is in the ExoRequest (merge information) and assign it to TheString. Is the document's MERGEFIELD different than Visual Basics?

2) line 'b' should see if TheString is empty or has text in it. If TheString has text THEN it should procede to 'c'.

Can someone help debug the below? My syntax is wrong on both lines.

--- Code ------------------------------------
a) TheString = {MERGEFIELD ExoRequest}
b) If (TheString) Than
c)    . . . . .
d) End If
----------------------------------------------
0
Comment
Question by:rw263
  • 14
  • 11
26 Comments
 
LVL 7

Expert Comment

by:gbahri
ID: 17048159
Hi Rw263,

Try this code:
------------------------------------------------------------------------
Dim myField As Field
Dim TheString As String

For Each myField In ActiveDocument.Fields ' Loops through all fields
    If myField.Type = wdFieldMergeField Then ' if MergeField is found
        If InStr(1, myField.Code, "ExoRequest") <> 0 Then ' if ExoRequest if found
            TheString = myField.Result  ' Value is assigned to TheString ' Part A
           
            If TheString <> "" Then ' If TheString is not empty. Part B
                MsgBox "I found the Value of ExoRequest" ' Your Part C ....
            End If
            TheString = ""
             Exit For
        End If
    End If
Next
Set myField=Nothing
------------------------------------------------------------------------

Hope, this helps you a bit,

Thanks,

GBahri
0
 

Author Comment

by:rw263
ID: 17049724
GBahri -- Wow where did you learn all this?  You Rock!

so... If (TheString <> "") <=- WORKS!

as for TheString = {MERGEFIELD ExoRequest}....
  TheString = ActiveDocument.myField.Result.ExoRequest
or is it TheString = myField.Result.ExoRequest


0
 
LVL 7

Expert Comment

by:gbahri
ID: 17050114
Above code iterates through all fields present in active document to find Mergefields. If found, field result is assigned to a variable "TheString". If this variable contains some value, you can proceed with Step C (Part C of you Message).

Below line holds the Value:

TheString = myField.Result

Thanks,

GBahri
0
 

Author Comment

by:rw263
ID: 17050511
GBahri -- I have just ONE field present in the document. Can I just assign TheString to the ExoRequest, or must I search threw every field? Is this a limitation in Visual Basic? In Word, there is just ONE line to Merge a single field.
0
 
LVL 7

Expert Comment

by:gbahri
ID: 17055992

If your document contains only one field code then you can use the following code:

activedocument.Range.Fields(1).Result

Thanks,

GBahri
0
 

Author Comment

by:rw263
ID: 17057406
You cannot call the field by name?? ActiveDocument.Range.ExoRequest.Result?
0
 
LVL 7

Expert Comment

by:gbahri
ID: 17057589
Field object doesn't have any property like name associated with it. We have to call as elaborated in my last message.

Thanks,

GBahri
0
 

Author Comment

by:rw263
ID: 17058080
I tried your FIRST code snippet and got:
     A field calculation error occurred in record 1.
0
 

Author Comment

by:rw263
ID: 17058194
   Dim myField As Field
    Dim TheString As String
    For Each myField In ActiveDocument.Fields
        If myField.Type = wdFieldMergeField Then
            If InStr(1, myField.Code, "ExtractionRequest") <> 0 Then
                TheString = myField.Result
            End If
        End If
    Next
    If (TheString <> "") Then
     . . . . . . .

Causes:  A field calculation error occurred in record 1.
0
 
LVL 7

Expert Comment

by:gbahri
ID: 17064284

"A field calculation error occurred in record #" (where # is every record number) may appear when you use an IF field to include files whose names are specified in other merge fields, and any of the merge fields specified is blank.

Pls. refer this page to know why this error appear:

WD: A Field Calculation Error Occurred in Record . . .
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B120278

Thanks,

GBahri
0
 

Author Comment

by:rw263
ID: 17064486
I saw the microsoft knowledge support on the field calculation error, the problem is that my Visual Basic Knowledge is limited and I do not know how to rewrite the If Instr() line to correct the problem. How do I go about debugging?
0
 
LVL 7

Expert Comment

by:gbahri
ID: 17064562
To trace the error, would it be possible for you to upload the document:
http://www.ee-stuff.com/

You will be able to link it to this question by pasting in the URL. Update when you have uploaded it.

Thanks,

GBahri
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:rw263
ID: 17064571
the ONLY visual basic code I have in my document is:

Private Sub Document_Open()
    If CommandBars("Control Toolbox").Controls(1).Caption = "&Exit Design Mode" Then
        ActiveDocument.ToggleFormsDesign
        CommandBars("Control Toolbox").Visible = False
    End If
    For Each myField In ActiveDocument.Fields
        If myField.Type = wdFieldMergeField Then
            If InStr(1, myField.Code, "ExtractionRequest") <> 0 Then
                TheString = myField.Result
                Exit For
            End If
        End If
    Next
    If (TheString <> "") Then
        If InStr(TheString, "Lower Left Central Incisor") > 0 Then
            CheckBoxLL1.Value = True
        End If
        . . . . same lines for each tooth . . . .
        If InStr(TheString, "Upper Right Deciduous Second Molar") > 0 Then
            CheckBoxURe.Value = True
        End If
    End If
End Sub
0
 
LVL 7

Expert Comment

by:gbahri
ID: 17064701
I've tried above code but its working fine with Merge fields. Error is related to fields that you've used and their values are blank which is creating problem. It would be better if you can upload the file.

Thanks,

GBahri
0
 

Author Comment

by:rw263
ID: 17064735
The reason I didn't u/l the file is because I _think_ the error is coming from the commercial program I am using, that is "passing the ExtractionRequest Merge" info. Is that possible or MUST it be from THIS code snippet?

According to the MSFT documentation, the problem must be with the If Instr(1,myField.Code.... line. I will try and replace that if with a MsgBox myField.Code to see if I can find out which Merge paramater is causing the error. If I can bypass that or I can call it direct via TheString = activedocument.Range.Fields(#).Result, I just need to find out which '#' is the ExtractionRequest. This is going to be difficult, but I'll try to put in some debug code and get back with more information? Till then, I'm stuck :(.
0
 
LVL 7

Accepted Solution

by:
gbahri earned 500 total points
ID: 17071994
Any update on this?
0
 

Author Comment

by:rw263
ID: 17077317
Still can't figure it out, I put in some MsgBox'es and still can't figure out what is wrong.
uploaded file to: https://filedb.experts-exchange.com/incoming/ee-stuff/358-gbahri.zip
0
 
LVL 7

Expert Comment

by:gbahri
ID: 17078930

Are you using this document as a main form for mail-merge?
If this is so, then update your code as below:

I've updated the code that will unprotect the form while iterating through fields and restore protection on its completion.
---------------------------------------------------------------------------
Private Sub Document_Open()
    If CommandBars("Control Toolbox").Controls(1).Caption = "&Exit Design Mode" Then
        ActiveDocument.ToggleFormsDesign
        CommandBars("Control Toolbox").Visible = False
    End If
    Dim myField As Field
    Dim TheString As String
    Activedocument.UnProtect   ' Password:="ABC"  Password, if any
     For Each myField In ActiveDocument.Fields
        If myField.Type = wdFieldMergeField Then
            If InStr(1, myField.Code, "ExtractionRequest") <> 0 Then
                TheString = myField.Result
                Exit For
            End If
        End If
    Next
activedocument.Protect Type:=wdAllowOnlyFormFields ' Password:="ABC" 'if any



'- Your code here
'-
'-
'-
'-
'-

End sub
---------------------------------------------------------------------------

Pls. revert if you still have any doubt

Thanks,

GBahri
0
 

Author Comment

by:rw263
ID: 17080038
The document is getting the merged fields from a commercial program. The document is NOT protected. If you notice on page two of the letter, there is the SAME merge field ExtractionRequest that merges great!
0
 
LVL 7

Expert Comment

by:gbahri
ID: 17080389
Can you check database where this information is stored. Pls. check that no field remains blank.

Try this, if this doesn't work try removing Fields with "If condition" (Alt-F9 to reveal fields). If this succeeds then we'll have to see for another workaround.

Make a backup copy of this document before proceeding.

Thanks,

GBahri
0
 

Author Comment

by:rw263
ID: 17087052
My Document contained a Microsoft Word Object, ThisDocument... as well as Module1 under the Modules. As it turns out the third party commercial program adds Modules1, Module2 and Module3 under the Modules. On a guess, I removed the Module1 in MY document thinking that it may intefere with the third party one and . . . .

I received a new error message --
 Microsoft Visual Basic
 Run-time error '-2147024809' (80070057)':
 The index into the specified collection is out of bounds.
 Continue/End/Debug/Help (Continue and Debug are grayed out)

Any ideas??
0
 

Author Comment

by:rw263
ID: 17114259
GBahri -- any thoughts??
0
 

Author Comment

by:rw263
ID: 17117488
GBahri -- is it possible that the third party program is not passing the MERGEFIELD to Word soon enough? When I have the MsgBox debug code, the MERGEFIELD's are all blank.  That doesn't explain the above run-time error though? ANy ideas?
0
 
LVL 7

Expert Comment

by:gbahri
ID: 17119623
Sorry for coming late to you (I was busy with other chores). This could be a possibility. I would suggest you to run this code after Third party application process is over. For that you'll have to replace "Private Sub Document_Open()" with some new sub procedure say, "Sub FieldCalculation()".

Thanks,

GBahri
0
 

Author Comment

by:rw263
ID: 17304267
Never figured out the answer to this, but Gbahri put in a lot of time so you get it. I'll go back to the drawing board.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Do you ever need to create a 20 page Word document for some testing purpose? Are you tired of copying & pasting old boring "lorem ipsum" text over and over again, increasing font size and line space in order to make the document 20+ pages long? Look…
Like many others, we try and discourage users from printing documents unnecessarily and instead send or share them electronically. However, this doesn't always work and documents are still printed. With this simple solution, if the user tries to …
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now