0

Background: a proprietary piece of veterinary software generates a document pre-populated with merge fields containing data for a particular patient. The field I am interested in is weight but its a string (Top_Stat) that looks like this "24.5 kg".

I have created a script to read that field and convert it into an integer. However I now want to use this integer to male medication dose calculations based on the animal weight. As create document variables for this but the variable (name and value) gets stored in the document. I want at least the value to be removed but can't seem to get the result with the following script.

Sub GetWeight()
 ActiveDocument.Variables("WeightInKg").Delete
 WeightInt = ActiveDocument.MailMerge.DataSource.DataFields("Top_Stat").Value

 WeightInt = Replace(WeightInt, " kg", "") 'This removes the superfluous text
 WeightInt = Val(WeightInt) 'This converts the weight into a number (integer)
 
 ActiveDocument.Variables.Add Name:="WeightInKg", Value:=WeightInt 'Add the Word variable
 ActiveDocument.Fields.Update
 
End Sub

What am I missing? Apologies, I am new to VBA.

Martin McDowell
  • 3
  • 1

1 Answers1

0

Your code needs some error checking. This first time it is run the document variable "WeightInKg" does not exist and when you go to delete it, the routine errors out.

Document variables, not to be confused with VBA Subroutine variables are not Word document fields so unless you have another reason for updating all fields, that code line is unnecessary.

Finally, you should always declare your VBA Subroutine variables.

I have modified your code but could not fully test it because I don't have your mail merge data source ... but give it a try and see if it now works for you.

Sub AutoOpen()
    Call GetWeight
End Sub


Sub GetWeight()
    Dim WeightIn As Long
    On Error Resume Next
    ActiveDocument.Variables("WeightInKg").Delete
    On Error GoTo ErrHandler
    WeightInt = ActiveDocument.MailMerge.DataSource.DataFields("Top_Stat").Value
    
    WeightInt = Replace(WeightInt, " kg", "") 'This removes the superfluous text
    WeightInt = Val(WeightInt) 'This converts the weight into a number (integer)
    
    ActiveDocument.Variables.Add Name:="WeightInKg", Value:=WeightInt 'Add the Word variable
'    ActiveDocument.Fields.Update
ErrHandler:
    If Err.Number > 0 Then
        MsgBox Err.Number & vbCr & Err.Description, vbCritical
        Err.Clear
    End If
End Sub

This is the screenshot of the Word document I am trying to populate. Screenshot

Rich Michaels
  • 1,663
  • 2
  • 12
  • 18
  • Thank you Rich. Very much appreciated. Wasn't sure about declaring before. Unfortunately the fields in the document still don't populate correctly with WeightInKg. I have added a screenshot of the Word document that I am trying to populate with the WeightInKg value. – Martin McDowell Mar 11 '21 at 12:33
  • Corrected a small typo from Rich's suggestion as it was declared as "WeihgtIn" not "WightInt" I also noticed that the fields get populated if I manually run the subroutine in the VB Editor but won't if I open the document. Do I need to run a command when document opened? – Martin McDowell Mar 11 '21 at 12:51
  • @MartinMcDowell I edited my answer with a way you can have it automatically run each time the document is opened. – Rich Michaels Mar 11 '21 at 13:03
  • Thank you Rich. This seems to be a tough cookie as now I'm getting Error 5852 - Requested object is not available. Not sure if this is a security issue with running the VBA script to do a database call? From everything I read the AutoOpen directive should work. – Martin McDowell Mar 25 '21 at 09:40