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 Else giftId = orow.Field(1) Set oGift = New CGift oGift.Init SessionContext oGift.LoadByField gufIMPORT_ID, giftId bankId = oGift.Fields(GIFT_fld_ConstituentBankId) oGift.CloseDown 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) oBank.CloseDown Set oBank = Nothing End If End If Cleanup_GiftBank: If Not oGift Is Nothing Then oGift.CloseDown Set oGift = Nothing End If If Not oBank Is Nothing Then oBank.CloseDown Set bank = Nothing End If Exit Sub Err_GiftBank: 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.
1 thought on “VBA User Fields in Export”
Comments are closed.