Export danych z Excela do pliku PLA (przelewy zagraniczne)
 MultiCash i iPKO biznes
  strona główna:
A po co ten Excel ;-)
 
     Choć sam nie widzę potrzeby automatyzacji wysyłki przelewów zagranicznych (7sztuk przez ostatnie 3 lata xD ) to jednak pokusiłem się   przykład po pobrania
ostatnio o napisanie procedury tworzącej taki plik z danych zawartych w arkuszach Excela. Na początku myślałem że będzie to prosta   xls2pla.zip
przeróbka procedury tworzącej plik PLI, jednak struktura pliku przelewów zagranicznych PLA okazała się zupełnie inna i w przynajmniej  
paru miejscach godna wyjaśnienia.  
     W firmie przelewy realizujemy przez Bank PKOBP toteż mogę potwierdzić że plik PLA jaki tworzy moja procedura importuje się do  
programów przelewowych tego banku (MultiCash, iPKO Biznes). Innych nie sprawdzałem i nie porównywałem różnić pomiędzy strukturami  
plików PLA innych Banków. Jednak mam nadzieję że lektura mojego artykułu i dokumentacji dot. Struktury pliku PLA w innych Bankach  
pozwoli zainteresowanych dostosować moją procedurę również do ich potrzeb :-)  
  Struktura pliku wejściowego iPKO biznes – przelewy zagraniczne
W sieci jest oczywiście szczegółowe info jak ma taki plik wygladać - sam się tą informacją posiłkowałem --->>  
 
     Tym razem troche inaczej podejdę do próby wyjaśnienia działania algorytmu. Nie zacytuję całości a od razu, fragmentami będę omawiał  
poszczególne linie kodu.  
 
 
Private Type vDaneInfoPLA  
    lngData As Long  
    strNrRach As String  
    lngNrBanku As Long  
    strNazwa As String  
    strSwift As String  
End Type  
 
Private Type vDaneKontrPLA  
    strNazwa As String  
    strKRaj As String  
    strNrRach As String  
      
    strBANKKraj As String  
    strBANKNazwa As String  
    strBANKswift As String  
End Type  
 
Sub StartPLA2()  
    Dim xlWks As Excel.Worksheet, ostAG As Long  
    Dim tblDane As Variant, tblWart As Variant  
    Dim vDaneNadawca As vDaneInfoPLA, vDane() As Variant, i As Long, j As Long, k As Long, l As Long  
    Dim strPLAname As String: strPLAname = Format(Now(), "yyyymmddhhmm") & ".PLA"  
    Set xlWks = ActiveSheet 'ThisWorkbook.Worksheets("Przelewy")  
      
    Static nrPrzesyłki As Integer: nrPrzesyłki = nrPrzesyłki + 1  
 
Typy vDaneInfoPLA  i vDaneKontrPLA grupują w jednej strukturze całość danych potrzebnych "w danej chwili". Pewnie dałoby się z nich  
zrezygnowac ale (wg mnie) sa istotne powody dla których tak własnie organizuję dane. Jednym z najistotniejszych powodów jest jednorazowe  
pozyskanie danych dot. Kontrahenta. Strukturę (typ) vDaneKontrPLA zwraca mi funkcja której podaję (jako argument) unikatory indeks  
przypisany do danego Kontrahenta. Funkcja nie zwraca np.: numeru Rachunku, czy Kraju Kontrahenta lub Banku. Zwraca wszystkie te dane  
po jednym obiegu pętli po danych Kontrahentów :-) Dlatego Funkcja zwracająca strukturę (typ) - rulez!!  
 
strPLAname (nazwę pliku) okreslam już teraz bo później będzie trzeba to podać jako element nagłówka pliku (w treśli pliku).  
xlWks - określam jako ActiveSheet raczej z doświadczenia :-| w PLI mam klika Arkuszy źródłowych (jednorazowe, stałe…)  
 
 
    With xlWks  
        ostAG = last(.Columns("B:B")) 'jedynie kol.B  
        tblDane = .Range("B11:J" & ostAG)  
        tblWart = .Range("D11:D" & ostAG)  
          
        vDaneNadawca.lngData = CLng(Format(.[B3], "yyyymmdd"))  
        vDaneNadawca.strNrRach = Replace(.[C7], " ", "")  
        vDaneNadawca.lngNrBanku = Mid(.[C7], 3, 10) * 1  
        vDaneNadawca.strSwift = .[D6]  
        vDaneNadawca.strNazwa = Left(.[C2], 35) & vbCrLf & _  
                                Left(.[C3], 35) & vbCrLf & _  
                                Left(.[C4], 35) & vbCrLf & _  
                                Left(.[C5], 35)  
    End With  
 
W tym fragmencie określam dane dotyczące Nadawcy. Informacje te są zawarte w nagłówku Arkusza PLA.  
Z ciekawych kwestii: ilość przelewów okreslam jedynie po kolumnie B (indeks) - tak mi najwygodniej. (formuły w innych kolumnach po prostu  
przeciągam wg. potrzeby). Nazwa firmy (4x35znaków) - pola ograniczyłem do 35znaków od lewej.  
 
        'Struktura pliku wejściowego iPKO biznes – przelewy zagraniczne  
        'https://www.pkobp.pl/media_files/264adc68-576e-4ad2-9cf4-61b31e4b175c.pdf  
        '2.2. Struktura pliku  
      
                        'Opis formatu:  
                            'X - znak alfanumeryczny  
                            'N -cyfra  
                            'F - stała długość  
                            'V - zmienna długość  
                            'Np. X(3) F oznacza pole alfanumeryczne o stałej długości 3 znaków  
 
        'Struktura nagłówka pliku  
 
    ReDim Preserve vDane(1 To 6)  
      
                    'Referencje zleceniodawcy  
                    'X(16) V        '??? NIe wiem co to jest??  
                                    'Analizując historię przelewów (dosłownie 7 w ciągu 3 lat) _  
                                    struktura tego ciągu to:  
                                        'REF  
                                        'data przelewu w formacie "MMDD" (bez roku)  
                                        '6 cyfr ??  
                                        ' nr przesyłki w formacie "000"  
        vDane(1) = ":01:REF" & _  
                    Format(xlWks.[B3], "MMDD") & _  
                    "123456" & _  
                    Format(nrPrzesyłki, "000")  
 
                    'Suma kwot poleceń umieszczonych w pliku _  
                    (niezależnie od waluty). _  
                    Kwota z częścią dziesiętną (2 cyfry), _  
                    oddzieloną znakiem ‘,’ (przecinek).  
                    'N(17) V    ':02:1920,00          '  
        vDane(2) = ":02:" & Application.Sum(tblWart)  
 
                    'Liczba poleceń płatniczych  
                    'N(5) V     ':03:1    '  
        vDane(3) = ":03:" & ostAG - 10 '(11-1)  
 
                    'Kod SWIFT banku zleceniodawcy (opcjonalne)  
                    'X(11) V    ':04:           '  
        vDane(4) = ":04:" & vDaneNadawca.strSwift  
                      
                    'Nazwa i adres zleceniodawcy. _  
                    Dane mogą być zapisane w kilku wierszach. _  
                    Każdy wiersz musi być oddzielony znakiem końca wiersza <CR><LF>.  
                    '4*X(35) V'  
        vDane(5) = ":05:" & vDaneNadawca.strNazwa  
 
                    'Nazwa pliku  
                    'X(12) V    ':07:13081201.PLA'  
        vDane(6) = ":07:" & strPLAname  
 
Na tm się kończy nagłówek pliku. Z ciekawych kwestii:  
 - Referencje zleceniodawcy.. - nie wiem co to jest jednak nie jest to pole opcjonalne i coś trzeba wpisać. Nigdzie w sieci nie znalazłem  
                                           wyjasnienia a analiza mojej historii przelewów nie daje odpowiedzi.. Tak jak to zapisałem (trochu na "odczep  
                                           się") przesdzła import poprawnie (samej wysyłki nie testowałem)  
 - moje komentarze. - Starałem się żeby każde pole było wyjasnione w komentarzu przez: Cytat z pliku pomocy, format pola, i przykład z  
                              mojej historii przelewów + czasem moje komentarze.  
 
ReDim Preserve vDane(1 To (UBound(tblDane) * 13) + 6)  
      
    'Informacje dotyczące poszczególnych poleceń  
      
 
 
 
 
 
 
 
 
 
Poniższy fragment będzie korzystał z bazy danych kontrahentów. (Arkusz Kontrahenci PLA) Ta baza mocno różni się od jej odpowiednika  
przelewów krajowych. Danych jest więcej i nie jest kontrolowana poprawność najstotoniejszych kwestii (jak to miało miejsce w PLI - f. CheckNBR)  
Po kolumnie indeks są rozrózniani kontrahenci - argument f. DaneKontrahentaPLA zwracającej strukturę dancyh dot. Kontrahenta wymagane  
w przelewie w kolejnych polach danego polecenia.  
 
 
    For j = 1 To UBound(tblDane)  
      
        With DaneKontrahentaPLA(tblDane(j, 1))  
                    '1:F01xxxxxxxxaxxxbbbbtttttt _  
                    xxxxxxxxxaxxx kod SWIFT banku zleceniodawcy _  
                    (numer jednostki w formacie KIR X(8) + wartość stała ‘XXXX’) _  
                    bbbb numer kolejny przesyłki _  
                    (tworzony automatycznie z zakresu 0001 – 9999) _  
                    tttttt numer kolejny polecenia w przesyłce _  
                    (tworzony automatycznie z zakresu 000001 – 999999)  
                    '{1:F01 xxxxxxxxaxxxbbbbtttttt}  
                      
                    '2:I100xxxxxxxxxaxxxy xxxxxxxxxaxxx _  
                    kod SWIFT banku kontrahenta rodzaj płatności – wartość stała N  
                    '{2:I100xxxxxxxxxaxxxY1}  
                      
                    'Początek szczegółów polecenia  
                    '{4:  
                  
                    '{1:F0110203453XXXX0001000001}{2:I100XXXXXXXXXXXXN1}{4:  
             vDane((j - 1) * 13 + 6 + 1) = "{1:F01" & vDaneNadawca.lngNrBanku & "XXXX" & _  
                                            Format(nrPrzesyłki, "0000") & _  
                                            Format(j, "000000") & "}" & _  
                                 "{2:I100" & .strBANKswift & String(12 - Len(.strBANKswift), "X") & "N1}{4:"  
           
Zakecone pole?? ;-) E tam… {1:F01 nr.Banku XXXX to tak naprawde element stały. Część dot. Nr.Banku Nadawcy określam z numeru konta  
Nadawcy z Arkusza PLA. Następnie nrPrzesyłki - i tu może pojawić się problem! W tej procedurze zmienna nrPrzesyłki to Static Integer  
numer przesyłki to numer kolejnego wykonania procedury (naliczane od nowa po resecie pliku). Gdyby jednak zdarzyło się że pierwsza przesyłka  
byłaby przesłana wykorzystując inną aplikację a następne przez Excela to numer przesyłki będzie naliczany pewnie nieprawidłowo. Jednak nie  
widzę możliwości pobrania z kodkolwiek informacji ile przesyłek było wykonanych do tej pory w danym dniu żeby określić prawidłowo numer  
kolejny. Zmienna statyczna powinna poradzić sobie dobrze - choć pewnie mogę być wyjątki :-)  
Dalej numer kolejnego polecenia po prostu uzależniony od kroku pętli i }. Następnie {2:I100 i kod Swift banku kontrahenta. Na ten kod  
jest 12 miejsc: Wpisuje się tu sam kod a pozostałe miejsca (do 12) dopełnia się X'ami. Dalej stałe N1}{4:  
 
                                   
                    'Referencje zleceniodawcy (opcjonalne)  
                    'X(16) V    ':20:  
            vDane((j - 1) * 13 + 6 + 2) = ":20:"  
          
                    'Data waluty (YYMMDD), _  
                    Kod waluty (ISO), _  
                    Kwota (Część dziesiętna (2 cyfry), oddzielona znakiem ‘,’ (przecinek))  
                    'N(6) F X(3) F N(15) V  
                    ':32A:130812PLN1920,00  
            vDane((j - 1) * 13 + 6 + 3) = ":32A:" & Format(xlWks.[B3], "YYMMDD") & _  
                                       tblDane(j, 4) & _  
                                       Format(tblDane(j, 3), "#.00")  
 
                    'Nazwa i adres zleceniodawcy  
                    '(wiersze po max. 35 znaków oddzielone znakiem <CR><LF>)  
                    '4*35(X) V  
            vDane((j - 1) * 13 + 6 + 4) = ":50:" & vDaneNadawca.strNazwa  
 
                    'Rachunek zleceniodawcy <CR><LF> 'N(34) V  
                    'Rachunek do opłat (dla kosztów) <CR><LF>   'N(34) V  
                    'Kod ISO waluty podstawowej (PLN) _  
                     Kwota w walucie podstawowej _  
                     (Część dziesiętna (2 cyfry), _  
                     oddzielona znakiem ‘,’ (przecinek))    'X(3) F N(15) V  
                       
                     'Kod statystyczny Stała wartość: _  
                     spacja (ASCII 32) _  
                     Kod kraju kontrahenta Stała wartość: _  
                     spacja (ASCII 32) _  
                     Kod kraju banku kontrahenta 'N(14) V X(1) X(2) F X(1) X(2) F  
                       
                     ':52D:24102034530000880201418961 _  
                           24102034530000880201418961 _  
                           PLN1920,00 _  
                           SK SK  
            vDane((j - 1) * 13 + 6 + 5) = ":52D:" & vDaneNadawca.strNrRach & vbCrLf & _  
                                           vDaneNadawca.strNrRach & vbCrLf & _  
                                           "PLN" & Format(tblDane(j, 5), "#.00") & vbCrLf & _  
                                           Space(15) & .strKRaj & " " & .strBANKKraj  
 
Następne "kwiatki" ;-) nr. Rachunku Nadawcy - proste. Rachunek dla kosztów związanych z transakcją: w moim przypadku proste :-) To "mój"  
koszt. Ponoszony z konta podstawowego. Nr konta więc powtarzam. Kod Waluty podstawowej: Konto z którego puszczam przelewy jest kontem  
złotówkowym więc walutą podstawową jest PLN. Gdyby ktoś chciał wykorzystywać tu konto Walutowe najprawdopodobniej trzeba by było to   
zmienić na odpowidni symbol odpowiedniej waluty. Teraz: "Wartość w walucie podstawowej"… no dobra ale po jakim kursie?? :-) Również nie  
wiem jaki kurs jest tu potrzebny jednak wartość trzeba przeliczyć i coś tu wpisać. Wartość tą podaję w Arkuszu PLA (niebieska kolumna na   Dane z tabeli HTML do tablicy
apocotenexcel.pl
obrazku powyżej) a kurs po jakim jest to przeliczone pobieram funkcją TabelaHTML ze strony banku. Jest to kurs bieżący i najprawdopodobniej  
nie ten kurs wykorzystuje np.: MultiCash ale… kurs ten realnie jest określany przez bank w momencie dokonywania przelewu i ja znam go   
widząc dopiero Wyciąg Bankowy. Kurs po jakim przeliczana jest wartość w walucie podstawowej jest zawsze inny a mimo wszystko przelew   Kursy Walut PKOBP
idzie. Nie przejmując się tym za bardzo podaję kurs ze strony banku.  
 
 
              
                    'Kod SWIFT banku kontrahenta lub identyfikator banku kontrahenta.  
                    'X(11) V        ':57A:8410  
            vDane((j - 1) * 13 + 6 + 6) = ":57A:" & .strBANKswift  
              
                    'Nazwa i adres banku kontrahenta. _  
                    Realizacja zlecenia następuje wyłączenia _  
                    na podstawie wartości pola 57A, pole 57D jest ignorowane.  
                    '(opcjonalne)  4*X(35) V  
                    ':57D:ZUNO BANK AG _  
                          PRIBINOVA 8 _  
                          811 09 BRATISLAVA  
            vDane((j - 1) * 13 + 6 + 7) = ":57D:" & .strBANKNazwa  
              
            'Numer konta, nazwa i adres kontrahenta  
                    'Znak ‘/’ (ukośnik) 'X(1) F  
                    'Numer rachunku kontrahenta. Znaki spacji będą usuwane. 'X(34) V  
                    '<CR><LF> Nazwa i adres kontrahenta _  
                    (max. 4 wiersze po 35 znaków. _  
                    Poszczególne wiersze są rozdzielone _  
                    <CR><LF>) '4*X(35) V  
                    '':59:/SK4284100000001100500699 _  
                           NREOA EMA _  
                           059 84 VYSNE HAGY 2 _  
                           VYSOKE TATRY  
            vDane((j - 1) * 13 + 6 + 8) = ":59:/" & .strNrRach & vbCrLf & _  
                                           .strNazwa  
        End With  
            'Szczegóły płatności _  
            (max. 4 wiersze po 35 znaków. _  
            Poszczególne wiersze są rozdzielone <CR><LF>)  
            '4*X(35) V  ':70:RACHUNEK 1  
            Dim strTytul As String  
                strTytul = tblDane(j, 6)  
                If Len(tblDane(j, 7)) > 0 Then strTytul = strTytul & vbCrLf & tblDane(j, 7)  
                If Len(tblDane(j, 8)) > 0 Then strTytul = strTytul & vbCrLf & tblDane(j, 8)  
                If Len(tblDane(j, 9)) > 0 Then strTytul = strTytul & vbCrLf & tblDane(j, 9)  
        vDane((j - 1) * 13 + 6 + 9) = ":70:" & strTytul  
          
            'Rozliczenie kosztów  
            'BN1: 0 Opłaty pobierane przez bank zleceniodawcy płaci zleceniodawca, pozostałe koszty obciążają beneficjenta płatności  
            'BN2: 1 opłaty bankowe obciążają kontrahenta  
            'OUR: 2 wszystkie koszty ponosi zleceniodawca.  
                'Poniższe mapowanie będzie używane w rozpoznawaniu rozliczeń kosztów:  
                'BN1 = SHA; BN2 = BEN; OUR = OUR  
        vDane((j - 1) * 13 + 6 + 10) = ":71A:BN1"  
          
            'Instrukcja płatnicza (6 wierszy) Poszczególne wiersze są rozdzielone <CR><LF>.  
            ' Pole1: Wskazówki (max. cztery dwucyfrowe oddzielone spacją (ASCII 32)) _  
                01 Płatność tylko dla kontrahenta, _  
                02 Płatność tylko czekiem, _  
                04 Płatność tylko po identyfikacji, _  
                06 Awizo dla banku kontrahenta telefonem, _  
                07 Awizo dla banku kontrahenta telefaksem, _  
                09 Awizo dla kontrahenta telefonem, _  
                10 Awizo dla kontrahenta telefaksem.  
                    '4*N(2) F  
            ' Pole2: Partner do rozmów (opcjonalne) X(35) V  
            ' Pole3: Informacje dodatkowe do pola :52D: _  
                    (max. cztery wiersze oddzielone znakiem <CR><LF>). (opcjonalne) 4*35(X) V  
        vDane((j - 1) * 13 + 6 + 11) = ":72:00 00 00 00"  
          
            '?? (u mnie tak wygladał przykładowy przelew z historii przelewów ;-)  
        vDane((j - 1) * 13 + 6 + 12) = "                                   "  
                  
                'Koniec szczegółów polecenia  
        vDane((j - 1) * 13 + 6 + 13) = "-}"  
    Next  
 
    TBL2TXT_ADO vDane, ThisWorkbook.Path & "\" & _  
                       strPLAname, _  
                       "IBM852" '"ISO 8859-2"  
    Set xlWks = Nothing  
      
    MsgBox "Plik utworzony"  
End Sub  
 
Po taki zapisie wszystkich poleceń sam plik tworze procedurą znaną z PLI poprzeza ADO określając kodowanie pliku docelowego zgodne z   
wytycznymi z pliku informacyjnego banku. W PKOBP IBM852  
 
 
Function DaneKontrahentaPLA(ByVal strIndex As String) As vDaneKontrPLA  
    Dim xlKontr As Excel.Worksheet, ostAG As Long  
    Dim tbl As Variant, i As Long  
      
    Set xlKontr = ThisWorkbook.Worksheets("Kontrahenci PLA")  
    ostAG = last(xlKontr.Columns("B:F"))  
    tbl = xlKontr.Range("B3:M" & ostAG)  
    For i = 1 To UBound(tbl)  
        If tbl(i, 1) = strIndex Then  
      
            DaneKontrahentaPLA.strNazwa = _  
                            Left(tbl(i, 2), 35) & vbCrLf & _  
                            Left(tbl(i, 3), 35) & vbCrLf & _  
                            Left(tbl(i, 4), 35)  
            DaneKontrahentaPLA.strKRaj = tbl(i, 5)  
            DaneKontrahentaPLA.strNrRach = tbl(i, 6)  
              
            DaneKontrahentaPLA.strBANKKraj = tbl(i, 8)  
            DaneKontrahentaPLA.strBANKNazwa = _  
                            Left(tbl(i, 10), 35) & vbCrLf & _  
                            Left(tbl(i, 11), 35) & vbCrLf & _  
                            Left(tbl(i, 12), 35)  
            DaneKontrahentaPLA.strBANKswift = tbl(i, 9)  
              
            Exit For  
        End If  
    Next  
End Function  
 
I to raczej wszysko co najważniejsze.  
Pomijam procedurę: TBL2TXT_ADO i funkcję TabelaHTML i UserForm "żywcem" z xls2pli.