I assume the error is genereated in the following line (can you confirm?)

sqlcmd = sqlcmd & _

" insert into #Eft_Transfer values ('" & strEftNo & "','" & strDate & "','" & strCode & "','" & strReference & "')" & vbCrLf

The error indicates a string value is entered in a field (or variable) with the BIGINT datatype. It looks like one of your string-values doesn't contain a value that can be converted to a integer vale. To troubleshoot you can print the values just before inserting them. That way you can manually check if the values are correct.