VBA User Fields in Export

In export for a recurring gift it is not possible to pull out the bank details specific to that gift. You can only pull the name of the bank or all the banks on the constituent’s record. It is not possible to pull the account number and sort code. This is really important when there are bank issues with recurring gifts that need to be sorted out.

Using the VBA module it is possible to output these values. Firstly we set up a gift export with four fields. Import Id and three VBA user fields. These are found in the list of field and can be added as any other field.

In the VBA module we add the following code:

Public Sub GiftBank(orow As IBBExportRow)  
On Error GoTo Err_GiftBank 

    Dim giftId As String 
    Dim oGift As CGift 
    Dim bankId As String 
    Dim oBank As CFinancial2 
    If orow.BOF Then 
    ElseIf orow.EOF Then 

        giftId = orow.Field(1) 
        Set oGift = New CGift 
        oGift.Init SessionContext 
        oGift.LoadByField gufIMPORT_ID, giftId 

        bankId = oGift.Fields(GIFT_fld_ConstituentBankId) 
        Set oGift = Nothing 

        If bankId <> "" Then 
             Set oBank = New CFinancial2 
             oBank.Init SessionContext 
             oBank.Load bankId 

             orow.Field(2) = oBank.Fields(FINANCIAL2_fld_SORT_CODE) 
             orow.Field(3) = oBank.Fields(FINANCIAL2_fld_ACCOUNT_NAME) 
             orow.Field(4) = oBank.Fields(FINANCIAL2_fld_ACCOUNT_NO) 

             Set oBank = Nothing 
        End If 

End If 


     If Not oGift Is Nothing Then 
        Set oGift = Nothing 
    End If 

     If Not oBank Is Nothing Then 
        Set bank = Nothing 
    End If 

    Exit Sub 

     MsgBox Err.Description 
     GoTo Cleanup_GiftBank 

End Sub

It is important the fields are in the correct order, i.e. there should be three VBA User Fields in columns 2,3 and 4 or the export otherwise this will not work.

There are three stages when the code is entered; at the beginning before any rows are read (orow.BOF), for each row and at the end once all the rows have been read (orow.EOF). We have to capture the oRow.BOF and oRow.EOF so that we don’t try to process a row at this point. Clearly you can do other things at this time such as initialise external databases or write summary data to a file if this were part of your process.

Just because you have a VBA user field in the export does not mean that you have to actually display anything in the field. In other examples I have done some processing at this point based on the values in the export. For example it is possible to mark membership cards as having been issued based on an export that is used to produce the membership welcome pack letters.

One thought on “VBA User Fields in Export

  1. Pingback: What kinds of things are you using VBA user fields for? - Blackbaud User Society - Forum

Comments are closed.