Notes

Sub createBIFile()
' Macro to create Bulk Import File
Application.ScreenUpdating = False
Application.StatusBar = "Payment record added successfully!"
Dim in_payFrom As String
Dim in_payTo As String
Dim in_paymentType As String
Dim payFromArray() As String
Dim payToArray() As String
Dim in_localFolderPath As String
Dim in_amount As String
Dim in_BIFileName As String
Dim in_CrCurrency As String

Dim in_FX_Type As String
Dim in_FX_Ref As String
Dim in_FX_Date As String
Dim in_FX_Rate As String

Dim in_Invoice_Type As String
Dim in_No_Of_Invoices As String

Dim in_TemplateSaved_Flag As String
Dim in_Template_Name As String
Dim in_File_Format As String


Dim array_DBacctname As String
Dim array_DBAcctno As String
Dim array_DBCurrency As String
Dim array_DBBankCode As String
Dim array_DBCtrycode As String
Dim temp As String

Dim array_CRnickname As String
Dim array_CRacctno As String
Dim array_CRPayeeName As String
Dim array_CRBankCode As String
Dim array_CRBankName As String
Dim array_CRCtrycode As String
Dim CRCitycode As String
Dim LRandomNumber As Integer
On Error GoTo err

Sheet2.Cells.ClearContents
'Display a message in Status bar
Application.StatusBar = "Creating Bulk Import File..."
'MsgBox Format(Date + 1, "dd/mm/yyyy")
Sheet2.Cells(1, 1).Value = "H" 'Header
Sheet2.Cells(1, 2).Value = "P" 'File Type


'=================================Start loop===================================

With Sheets("MasterList")
Set rngTC = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
For j = 2 To rngTC.Rows.Count

'tcStatus = .Cells(j, 3)

'in_payFrom = Sheet1.Range("D3").Value
'in_payTo = Sheet1.Range("D5").Value
'in_localFolderPath = Sheet1.Range("D7").Value
'in_paymentType = Sheet1.Range("D9").Value
'in_amount = Sheet1.Range("D11").Value
'in_CrCurrency = Sheet1.Range("D13").Value
'in_BIFileName = Sheet1.Range("D15").Value

in_payFrom = .Cells(j, 1)
in_payTo = .Cells(j, 2)
in_localFolderPath = .Cells(j, 3)
in_paymentType = .Cells(j, 4)
in_amount = .Cells(j, 5)
in_CrCurrency = .Cells(j, 6)
in_BIFileName = .Cells(j, 7)
in_FX_Type = .Cells(j, 8)
in_FX_Ref = .Cells(j, 9)
in_FX_Date = .Cells(j, 10)
in_FX_Rate = .Cells(j, 11)

in_Invoice_Type = .Cells(j, 12)
in_No_Of_Invoices = .Cells(j, 13)

in_TemplateSaved_Flag = .Cells(j, 14)
in_Template_Name = .Cells(j, 15)
in_File_Format = .Cells(j, 16)

payFromArray() = Split(in_payFrom, "-")
payToArray() = Split(in_payTo, "-")

'payFrom = DBName -DBAcctno - DBCurrency - Name - BankCode - Ctrycode
'payTo = Nickname -CrAcctno - PayeeName - -BankCode - BankName - Ctrycode

For i = LBound(payFromArray) To UBound(payFromArray)
'MsgBox (payFromArray(i))
Next i

For i = LBound(payToArray) To UBound(payToArray)
'MsgBox (payToArray(i))
Next i

array_DBacctname = Trim(payFromArray(0))
array_DBAcctno = "'" & Trim(payFromArray(1))
array_DBCurrency = Trim(payFromArray(2))
array_DBBankCode = Trim(payFromArray(4))
array_DBCtrycode = Trim(payFromArray(5))

array_CRnickname = Trim(payToArray(0))
array_CRacctno = "'" & Trim(payToArray(1))
array_CRPayeeName = Trim(payToArray(2))
array_CRBankCode = Trim(payToArray(3))
array_CRBankName = Trim(payToArray(4))
array_CRCtrycode = Trim(payToArray(5))

Sheet2.Visible = True 'Intermediate sheet

Dim Row
Dim vlookup
Dim lastrow
Row = 0
lastrow = Sheets("BIFile").Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row + 1
Row = lastrow

'MsgBox Row

Sheet2.Cells(Row, 1).Value = "P" 'Record Type
Sheet2.Cells(Row, 2).Value = in_paymentType 'Payment Type =========== variable input==done
Sheet2.Cells(Row, 3).Value = "ON" 'Processing Mode
Randomize
LRandomNumber = Int((300 - 200 + 1) * Rnd + 200)

Sheet2.Cells(Row, 5).Value = "CustRef" & LRandomNumber 'Customer Ref#

Sheet2.Cells(Row, 6).Value = "CustMemo" 'Customer Memo
Sheet2.Cells(Row, 7).Value = array_DBCtrycode 'DB CtryCode =========== variable input==done
On Error Resume Next
vlookup = Application.WorksheetFunction.vlookup(array_DBCtrycode, Sheet3.Range("A1:B1000"), 2, False)
On Error GoTo 0
Sheet2.Cells(Row, 8).Value = vlookup 'DB CityCode =========== vlookup to be placed==done
Sheet2.Cells(Row, 9).Value = array_DBAcctno 'DB Acc no. =========== variable input==done
Sheet2.Cells(Row, 10).Value = Format(Date, "dd/mm/yyyy") 'PaymentValueDate =========== Done Sydate +1
Sheet2.Cells(Row, 11).Value = array_CRPayeeName 'CR PayeeName =========== variable input==done
Sheet2.Cells(Row, 12).Value = "Address1" 'CR PayeeAddress - dummy data
Sheet2.Cells(Row, 13).Value = "Address2" 'CR PayeeAddress - dummy data
Sheet2.Cells(Row, 14).Value = "Address3" 'CR PayeeAddress - dummy data
Sheet2.Cells(Row, 15).Value = "11220033" 'CR PayeeFax# - dummy data
Sheet2.Cells(Row, 16).Value = array_CRBankCode 'CR Payee Bank Code =========== variable input==done
Sheet2.Cells(Row, 17).Value = "" 'Payee Bank LocalClearingCode =========== vlookup to be placed
Sheet2.Cells(Row, 18).Value = "" 'PayeeBranchCode =========== vlookup to be placed
Sheet2.Cells(Row, 19).Value = "" 'PayeeSubBranchcode =========== vlookup to be placed
Sheet2.Cells(Row, 20).Value = array_CRacctno 'CR Payee Acct# =========== variable input==done
Sheet2.Cells(Row, 36).Value = "" 'Discount amount- dummy data
Sheet2.Cells(Row, 37).Value = "" 'InvoiceFormat- dummy data
Sheet2.Cells(Row, 38).Value = in_CrCurrency 'PaymentCurrency - Variable or vlookup===================?
Sheet2.Cells(Row, 39).Value = in_amount 'PaymentAmount - Variable or dummy===================?
Sheet2.Cells(Row, 43).Value = "" 'Clearing code for TT - required===================?
Sheet2.Cells(Row, 44).Value = "" 'Clearing zone code for LBC or XCHQ - required===================?
Sheet2.Cells(Row, 46).Value = "" 'delivery Method - required===================?
Sheet2.Cells(Row, 47).Value = "" 'deliver To - required===================?
If array_DBCtrycode = "SG" And (in_paymentType = "CC" Or in_paymentType = "LBC" Or in_paymentType = "IBC") Then
Sheet2.Cells(Row, 46).Value = "M" 'delivery Method - required===================?
Sheet2.Cells(Row, 47).Value = "C" 'deliver To - required===================?
End If

Sheet2.Cells(Row, 48).Value = "" 'Counter Pickup location - required===================?
Sheet2.Cells(Row, 49).Value = "" 'FX Type - TBD===================?
If array_DBCurrency <> in_CrCurrency Then

If in_FX_Type = "Pre Booked Rate" Then
Sheet2.Cells(Row, 49).Value = "C"

Sheet2.Cells(Row, 10).Value = Format(in_FX_Date, "Short Date")
ElseIf in_FX_Type = "Bank Rate" Then
Sheet2.Cells(Row, 49).Value = "S"
Else
Sheet2.Cells(Row, 49).Value = "A"
End If

'FX Type - ==================== S BANK TYPE, A ASSIGN LATER, C PREBOOKED RATE
End If

Sheet2.Cells(Row, 58).Value = "" 'VAT Type - TBD===================?
Sheet2.Cells(Row, 59).Value = "" 'Discount Type - TBD===================?
Sheet2.Cells(Row, 60).Value = array_DBCurrency 'Debit Currency - Variable or vlookup===================?
Sheet2.Cells(Row, 61).Value = array_DBBankCode 'DB Bank Code =========== variable input==done
Sheet2.Cells(Row, 62).Value = array_CRnickname 'Cr Nickname =========== variable input==done
Sheet2.Cells(Row, 63).Value = "[email protected]" 'Cr email =========== dummy data
Sheet2.Cells(Row, 151).Value = array_CRCtrycode 'Destination Center Ctry Code =========== variable input

On Error Resume Next
vlookup = Application.WorksheetFunction.vlookup(array_CRCtrycode, Sheet3.Range("A1:B1000"), 2, False)
On Error GoTo 0
Sheet2.Cells(Row, 152).Value = vlookup 'Destination Center City Code =========== vlookup
'Clearingzonecode
CRCitycode = vlookup
temp = array_CRCtrycode + CRCitycode + in_CrCurrency
On Error Resume Next
vlookup = Application.WorksheetFunction.vlookup(temp, Sheet9.Range("D1:E1000"), 2, False)
On Error GoTo 0

If in_paymentType = "LBC" And vlookup <> "" Then
Sheet2.Cells(Row, 44).Value = "'" & vlookup
'MsgBox vlookup
End If


Sheet2.Cells(Row, 153).Value = "C" 'Date Priority
Sheet2.Cells(Row, 154).Value = "C" 'Amount Priority

If array_DBCtrycode = "SG" And (in_paymentType = "ACH" Or in_paymentType = "IBFT") Then
Sheet2.Cells(Row, 166).Value = "BONU" 'Purpose of payment =========== variable input
End If

If in_Invoice_Type <> "" Then
If in_Invoice_Type = "4 Column" Then
Sheet2.Cells(Row, 37).Value = "4" 'Invoice Type
For i = 1 To in_No_Of_Invoices
Row = Row + 1
Sheet2.Cells(Row, 1).Value = "I" 'Invoice Row
Sheet2.Cells(Row, 2).Value = "IR/6011" 'Invoice Ref == Dummy data
Sheet2.Cells(Row, 3).Value = Format(Date, "dd/mm/yyyy") 'Invoice Date = System date
Sheet2.Cells(Row, 4).Value = "Invoice Desc" 'Invoice Desc == Dummy data
Sheet2.Cells(Row, 5).Value = (in_amount / in_No_Of_Invoices) 'Invoice Amount
Next i

Else
Sheet2.Cells(Row, 37).Value = "2" 'Invoice Type
For i = 1 To in_No_Of_Invoices
Row = Row + 1
Sheet2.Cells(Row, 1).Value = "I" 'Invoice Row
Sheet2.Cells(Row, 5).Value = (in_amount / in_No_Of_Invoices) 'Invoice Amount
Next i

End If
End If


If in_FX_Ref <> "" And in_FX_Type = "Pre Booked Rate" Then
Row = Row + 1
Sheet2.Cells(Row, 1).Value = "F" 'FX Row
Sheet2.Cells(Row, 2).Value = in_FX_Ref 'FX Ref == user input
Sheet2.Cells(Row, 3).Value = in_FX_Rate 'FX Rate == user input
Sheet2.Cells(Row, 4).Value = "DealerName" 'FX dealer name == Dummy data
Sheet2.Cells(Row, 5).Value = in_FX_Date 'FX Date == User input
Sheet2.Cells(Row, 6).Value = in_amount 'FX amount = user input

End If

Next
End With

'=================================End loop===================================
Row = Sheets("BIFile").Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row + 1
Sheet2.Cells(Row, 1).Value = "T" 'Tail
Sheet2.Cells(Row, 2).Value = (Sheets("MasterList").Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row - 1) 'Number of payments
Sheet2.Cells(Row, 3).Value = "1000" 'Payment Amount

'Code to Save BIPayments as template
If in_TemplateSaved_Flag = "Yes" Then

With Sheets("MasterList")
Set rngTC = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
lastrow = Sheets("SavedTemplates").Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row
Row = lastrow
For j = 2 To rngTC.Rows.Count
Row = Row + 1
Sheets("SavedTemplates").Cells(Row, 1).Value = Sheets("MasterList").Cells(j, 1).Value ' Pay From
Sheets("SavedTemplates").Cells(Row, 2).Value = Sheets("MasterList").Cells(j, 2).Value ' Pay To
Sheets("SavedTemplates").Cells(Row, 3).Value = Sheets("MasterList").Cells(j, 3).Value ' Folder Path
Sheets("SavedTemplates").Cells(Row, 4).Value = Sheets("MasterList").Cells(j, 4).Value ' Payment Type
Sheets("SavedTemplates").Cells(Row, 5).Value = Sheets("MasterList").Cells(j, 5).Value ' Amount
Sheets("SavedTemplates").Cells(Row, 6).Value = Sheets("MasterList").Cells(j, 6).Value ' Credit Currency
Sheets("SavedTemplates").Cells(Row, 7).Value = Sheets("MasterList").Cells(j, 7).Value ' BI File Name
Sheets("SavedTemplates").Cells(Row, 8).Value = Sheets("MasterList").Cells(j, 8).Value ' FX Type
Sheets("SavedTemplates").Cells(Row, 9).Value = Sheets("MasterList").Cells(j, 9).Value ' FX Ref
Sheets("SavedTemplates").Cells(Row, 10).Value = Sheets("MasterList").Cells(j, 10).Value ' FX Date
Sheets("SavedTemplates").Cells(Row, 11).Value = Sheets("MasterList").Cells(j, 11).Value ' FX Rate
Sheets("SavedTemplates").Cells(Row, 12).Value = Sheets("MasterList").Cells(j, 12).Value ' Invoice Type
Sheets("SavedTemplates").Cells(Row, 13).Value = Sheets("MasterList").Cells(j, 13).Value ' Number of Invoices
Sheets("SavedTemplates").Cells(Row, 14).Value = in_TemplateSaved_Flag ' SaveTemplateFlag
Sheets("SavedTemplates").Cells(Row, 15).Value = in_Template_Name ' TemplateName
Sheets("SavedTemplates").Cells(Row, 16).Value = in_File_Format ' FileFormat
Next
End With

End If


Dim wbkExport As Workbook
Dim SheetToExport As Worksheet

Set SheetToExport = ThisWorkbook.Worksheets("BIFile")
Set wbkExport = Application.Workbooks.Add
SheetToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False
If in_File_Format = ".CSV" Then
wbkExport.SaveAs Filename:=in_localFolderPath & "BI File " & in_BIFileName & " " & Replace(Replace(Now, ":", "-"), "/", "-") & ".csv", FileFormat:=xlCSV

Else
wbkExport.SaveAs (in_localFolderPath & "BI File " & in_BIFileName & " " & Replace(Replace(Now, ":", "-"), "/", "-") & ".xls")
End If

Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False
Application.StatusBar = "Bulk Import file created successfully!"
Row = Sheets("MasterList").Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row - 1
MsgBox "Voila! Your file having " & Row & " payments has been created."
Sheet2.Visible = False
' Sheet14.Rows("2:" & Rows.Count).ClearContents
Sheet14.Rows("2:" & Rows.Count).EntireRow.Delete
Exit Sub

err:
'Display the error message in Status bar
Application.StatusBar = err.Description
End Sub