Solved

VB - StrLen and MERGEFIELD

Posted on 2006-07-05
26
994 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

 

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
 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

724 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