Optimize the code

jerem488 · June 20, 2018

Hi all,

I have a script that works, but I think it's possible to optimize it. I find that the script takes a long time to process the files.

I need your opinion on the methods used and whether it is possible to optimize it.

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <WindowsConstants.au3>
#include <WinAPIShPath.au3>
#include <FontConstants.au3>
#include <StaticConstants.au3>

Global $aArrayShop[8][3] = [["424", "CAISSE COGR POST", "0008"], _
    ["337", "CAISSE COGR HLT", "0003"], _
    ["407", "CAISSE COGR XERT", "0009"], _
    ["431", "CAISSE COGR ZERCO", "0008"], _
    ["349", "CAISSE COGR 349", "0007"], _
    ["406", "CAISSE COGR DSOG", "0009"], _
    ["385", "CAISSE COGR VILLAR", "0004"], _
    ["420", "CAISSE COGR THORX", "0005"]]

Global $aArrayIndicator[31][5] = [["Mouvements de Caisse", "CA HT", "", "", ""], _
    ["Mouvements de Caisse", "CA TTC", "", "CREDIT", ""], _
    ["Mouvements de Caisse", "TVA", "", "", ""], _
    ["Modes de règlement", "Acompte hors place", "0COGR" & _StringRepeat(Chr(32), 1), "DEBIT", ""], _ ; ALERTE
    ["Modes de règlement", "American Express", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "Avoir hors place", "0AV", "DEBIT", "SC"], _
    ["Modes de règlement", "Bon cadeau hors place", "0COGR" & _StringRepeat(Chr(32), 1), "DEBIT", ""], _
    ["Modes de règlement", "BEST RECORD", "0BEST" & _StringRepeat(Chr(32), 3), "DEBIT", ""], _
    ["Modes de règlement", "CADHOC", "0CADH" & _StringRepeat(Chr(32), 3), "DEBIT", ""], _
    ["Modes de règlement", "HAVAS", "0HAVA" & _StringRepeat(Chr(32), 3), "DEBIT", ""], _
    ["Modes de règlement", "KADEOS", "0KADE" & _StringRepeat(Chr(32), 3), "DEBIT", ""], _
    ["Modes de règlement", "Shopping Pass", "0SHOP" & _StringRepeat(Chr(32), 3), "DEBIT", ""], _
    ["Modes de règlement", "TIR GROUPE", "0TIR" & _StringRepeat(Chr(32), 4), "DEBIT", ""], _
    ["Modes de règlement", "C.B. manuelle", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "CA DO Chèque Banque Postale", "0CADO" & _StringRepeat(Chr(32), 3), "DEBIT", ""], _
    ["Modes de règlement", "Carte bancaire", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "Carte Cadeau", "0CC", "DEBIT", "SC"], _
    ["Modes de règlement", "Carte ILLICADO", "0ILLI" & _StringRepeat(Chr(32), 3), "DEBIT", ""], _
    ["Modes de règlement", "CB BRANDALLEY", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "CB CFAO", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "CB REDOUTE", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "CB SPARTOO", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "CB Web", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "Chèque", "0CHQ" & _StringRepeat(Chr(32), 4), "DEBIT", ""], _
    ["Modes de règlement", "Reprise Acompte - P Diff", "0COGR" & _StringRepeat(Chr(32), 1), "", ""], _
    ["Modes de règlement", "Reprise Acompte Réservation", "0COGR" & _StringRepeat(Chr(32), 1), "", ""], _
    ["Modes de règlement", "Reprise d'acompte", "0COGR" & _StringRepeat(Chr(32), 1), "", ""], _
    ["Modes de règlement", "Reprise d'avoir", "0AV", "", "SC"], _
    ["Emissions (OSCC)", "Accompte", "0COGR" & _StringRepeat(Chr(32), 1), "CREDIT", ""], _
    ["Emissions (OSCC)", "Avoir", "0AV", "CREDIT", "SC"], _
    ["Emissions (OSCC)", "Carte cadeau", "0CC", "CREDIT", "SC"]]

Global $bWriteExcelExport = False
Global $bFirstLine = True

Window()
Func Window()
    Local $hGUI = GUICreate("Importation", 550, 147)
    Global $hFile = GUICtrlCreateLabel("Folder containing Excel files to import :", 20, 20, 280)
    GUICtrlSetFont(-1, 10)
    Global $hSelectFolderButton = GUICtrlCreateButton("Browse", 305, 17, 80)
    Global $idFolder = GUICtrlCreateInput("", 20, 60, 510)
    Global $hValider = GUICtrlCreateButton("OK", 20, 105, 80)
    GUICtrlSetState($hValider, $GUI_DISABLE)
    Local $sPath = @MyDocumentsDir & "\"

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hSelectFolderButton
                If FileExists(@TempDir & "\Conectis.txt") Then
                    $sPath = FileReadLine(@TempDir & "\Conectis.txt", 1)
                EndIf
                Global $sFileSelectFolder = FileSelectFolder("", $sPath)
                If Not @error Then
                    Local $sPathFile = @TempDir & "\Conectis.txt"
                    Local $hFileOpen = FileOpen($sPathFile, $FO_OVERWRITE)
                    FileWriteLine(@TempDir & "\Conectis.txt", $sFileSelectFolder)
                    GUICtrlSetData($idFolder, $sFileSelectFolder)
                    GUICtrlSetState($hValider, $GUI_ENABLE)
                EndIf
            Case $hValider
                GUICtrlSetState($hFile, $GUI_HIDE)
                GUICtrlSetState($hSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($idFolder, $GUI_HIDE)
                GUICtrlSetState($hSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($hValider, $GUI_HIDE)
                Global $sLabelWait = GUICtrlCreateLabel("Please wait...", 60, 52.5, 430, 25)
                GUICtrlSetFont(-1, 18)
                ListFiles($sFileSelectFolder)
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func ListFiles($sFolder)
    Local $aFileList = _FileListToArray($sFolder, "*", 1)
    If @error = 1 Then Exit MsgBox(64, "", "The path is invalid")
    If @error = 4 Then Exit MsgBox(64, "", "No Excel files were found in this folder.")

    FileDelete($sFileSelectFolder & "\Exclusions.xls")
    FileDelete($sFileSelectFolder & "\ImportQuadra.txt")

    ; Loops the list of files
    For $i = 1 To UBound($aFileList) - 1
        $sExtension = _WinAPI_PathFindExtension($aFileList[$i])
        If $sExtension = ".xls" Or $sExtension = ".xlsx" Then
            If $aFileList[$i] <> "Exclusions.xls" Then TransposeExcel($sFolder, $aFileList[$i])
        EndIf
    Next
    GUICtrlDelete($sLabelWait)
    GUICtrlSetState($hFile, $GUI_SHOW)
    GUICtrlSetState($hSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($idFolder, $GUI_SHOW)
    GUICtrlSetState($hSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($hValider, $GUI_SHOW)
    If ($i - 1) >= 1 And $bWriteExcelExport = True Then Window2($sFileSelectFolder & "\")
    If ($i - 1) >= 1 And $bWriteExcelExport = False Then Window3($sFileSelectFolder & "\")
EndFunc

Func Window2($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 150)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $sExclusions = GUICtrlCreateLabel("File of exclusions to be processed:", 30, 70, 200)
    GUICtrlSetFont(-1, 9)
    Local $sExclusionsFile = GUICtrlCreateLabel("Exclusions.xls", 207, 70, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sExclusionsFile, "0xAE0000")
    Local $hFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 115, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Open", 285, 111.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func Window3($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 125)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $hFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 90, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Ouvrir", 285, 86.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func TransposeExcel($sFolder, $sFile)
    Local $oExcel = _Excel_Open(False, False, False, False, True)
    If @error Then Exit
    Local $oWorkbookExport = _Excel_BookNew($oExcel)

    Local $oWorkbook = _Excel_BookOpen($oExcel, $sFolder & "\" & $sFile)
    If @error Then _Excel_Close($oExcel)

    Local $sResultA1 = _Excel_RangeRead($oWorkbook, Default, "A1")
    If Not @error Then
        Local $bNewFile = True
        Local $nLine = 2
        If $sResultA1 = "CODE MAGASIN" Then $sCodeMagasin = _Excel_RangeRead($oWorkbook, Default, "A2")
        Local $n = 2
        Do
            Local $sLigne = ""
            Local $nLongueurLigne = 0
            Local $sTypeEnregistrement = "M"    ; 1st character ; Length: 1 character
            Local $sNumeroCompte = ""           ; 2nd character ; Length: 8 characters
            Local $sLigneFolio = "000"          ; 12nd character ; Length: 3 characters
            Local $sDate = ""                   ; 15nd character ; Length: 6 characters
            Local $sLibelleCourt = ""           ; 22nd character ; Length: 20 characters
            Local $sLettreDebitCredit = ""      ; 23nd character ; Length: 1 character
            Local $sDebitCredit = ""            ; 43nd character ; Length: 13 characters
            Local $sDevise = "EUR"              ; 108nd character ; Length: 3 characters
            Local $sCodeJournal = "ODC"         ; 111nd character ; Length: 3 characters
            Local $sLibelleLong = ""            ; 117nd character ; Length: 30 characters

            ; ==> Recovery of Excel file cell contents
            Local $sColumA = _Excel_RangeRead($oWorkbook, Default, "A" & $n)
            If $sColumA = "" Then ExitLoop
            Local $sJour = _Excel_RangeRead($oWorkbook, Default, "B" & $n)
            Local $sCategorie = _Excel_RangeRead($oWorkbook, Default, "C" & $n)
            Local $sIndicateur = _Excel_RangeRead($oWorkbook, Default, "D" & $n)
            Local $sDebit = _Excel_RangeRead($oWorkbook, Default, "E" & $n)
            Local $sCredit = _Excel_RangeRead($oWorkbook, Default, "F" & $n)

            ; ==> Retrieving the account number
            $sNumeroCompte &= NumeroCompte($sCategorie, $sIndicateur, $sCodeMagasin)

            ; ==> Retrieving the date
            $sYear = StringRight(StringLeft($sJour, 4), 2)
            $sMonth = StringRight(StringLeft($sJour, 6), 2)
            $sDay = StringRight(StringLeft($sJour, 8), 2)
            $sDate &= $sDay & $sMonth & $sYear

            ; ==> Retrieving the short text
            $sLibelleCourt &= Libelle($sCodeMagasin, "court")

            ; ==> Retrieving the long text
            $sLibelleLong &= Libelle($sCodeMagasin, "long")

            ; ==> Retrieving the debit OR credit letter ('C' OU 'D')
            If $sDebit <> "" And $sCredit = "" Then
                If $sDebit <> 0 Then
                    $sLettreDebitCredit = "D"
                    ; ==> Recovery of debit amount
                    $sDebitCredit &= Formatage($sDebit)
                Else
                    $sDebitCredit = "ZERO"
                EndIf
            Else
                If $sCredit <> 0 Then
                    $sLettreDebitCredit = "C"
                    ; ==> Recovery of credit amount
                    $sDebitCredit &= Formatage($sCredit)
                Else
                    $sDebitCredit = "ZERO"
                EndIf
            EndIf

            ; ==> Complete line to write in the file
            $sLigne &= $sTypeEnregistrement & $sNumeroCompte & _StringRepeat(Chr(32), 2) & $sLigneFolio & $sDate & _StringRepeat(Chr(32), 1) & _
                $sLibelleCourt & $sLettreDebitCredit & $sDebitCredit & _StringRepeat(Chr(32), 52) & $sDevise & $sCodeJournal & _StringRepeat(Chr(32), 3) & $sLibelleLong

            $nLongueurLigne = StringLen($sLigne)
            $sLigne &= _StringRepeat(Chr(32), 256 - $nLongueurLigne)

            ; ==> Saving the results of the processed file
            If StringIsSpace($sNumeroCompte) Or $sNumeroCompte = "Acompte hors place" Or $sNumeroCompte = "Avoir hors place" Or $sDebitCredit = "ZERO" Then
                If $bFirstLine = True Then
                    _Excel_RangeWrite($oWorkbookExport, Default, "CODE MAGASIN", "A1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "JOUR", "B1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "CATEGORIE", "C1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "INDICATEUR", "D1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "DEBIT", "E1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "CREDIT", "F1")
                    $bWriteExcelExport = True
                    $bFirstLine = False
                EndIf

                _Excel_RangeCopyPaste($oWorkbook.Sheets(1), $oWorkbook.Sheets(1).Range("A" & $n).EntireRow, $oWorkbookExport.Sheets(1).Range("A" & $nLine))
                $nLine += 1
            Else
                Local $hFileOpen = FileOpen($sFileSelectFolder & "\ImportQuadra.txt", 1)
                If $hFileOpen = -1 Then
                    MsgBox(16, "", "Cannot create file " & $sFileSelectFolder & "\ImportQuadra.txt")
                    Return False
                EndIf
                FileWriteLine($sFileSelectFolder & "\ImportQuadra.txt", $sLigne)
            EndIf
            $n += 1
        Until $sColumA = ""
        If $bWriteExcelExport = True Then _Excel_BookSaveAs($oWorkbookExport, $sFileSelectFolder & "\Exclusions.xls", Default, True)
        _Excel_BookClose($oWorkbookExport)
        _Excel_BookClose($oWorkbook)
        _Excel_Close($oExcel)
    EndIf
EndFunc

Func Formatage($sCellule)
    ; ==> Function that formats in 12 characters the debit & credit columns of the rows
    Local $sMontant = ""
    If IsNumber($sCellule) Then
        ; ==> Any + and - characters that may be present are deleted
        $sCellule = StringRegExpReplace($sCellule, "[^\d\.]", "")

        Local $PositionPoint = StringInStr($sCellule, ".")
        Local $sDecimales = ""
        If $PositionPoint Then ; There's a point
            $sDecimales = StringTrimLeft($sCellule, $PositionPoint)
            $sNumber = StringLeft($sCellule, $PositionPoint - 1)
            If StringLen($sDecimales) < 2 Then
                $sDecimales &= "0"
            EndIf
            $sMontant &= $sNumber & $sDecimales
            $nLongueur = StringLen($sMontant)
            $sMontant = "+" & _StringRepeat("0", 12 - $nLongueur) & $sMontant
        Else ; There is no point
            $sDecimales &= $sCellule & "00"
            $nLongueur = StringLen($sDecimales)
            $sMontant = "+" & _StringRepeat("0", 12 - $nLongueur) & $sDecimales
        EndIf
    EndIf
    Return $sMontant
EndFunc

Func NumeroCompte($sCategorie, $sIndicateur, $sCodeMagasin)
    For $i = 0 To UBound($aArrayIndicator, 1) - 1
        Local $sNumeroCompte = ""
        Local $sSCompte = ""
        If $sCategorie == $aArrayIndicator[$i][0] And $sIndicateur == $aArrayIndicator[$i][1] Then
            $sNumeroCompte &= $aArrayIndicator[$i][2]
            If $aArrayIndicator[$i][1] = "Acompte hors place" Then
                $sNumeroCompte = "Acompte hors place"
                ExitLoop
            ElseIf $aArrayIndicator[$i][1] = "Avoir hors place" Then
                $sNumeroCompte = "Avoir hors place"
                ExitLoop
            EndIf
            If $aArrayIndicator[$i][4] = "SC" Then
                For $i = 0 To UBound($aArrayShop, 1) - 1
                    If $sCodeMagasin == $aArrayShop[$i][0] Then
                        $sSCompte = $aArrayShop[$i][2]
                        ExitLoop
                    EndIf
                Next
                $sNumeroCompte &= $sSCompte
            EndIf
            $nLongueur = StringLen($sNumeroCompte)
            $sNumeroCompte &= _StringRepeat(Chr(32), 8 - $nLongueur)
            ExitLoop
        EndIf
    Next
    Return $sNumeroCompte
EndFunc

Func Libelle($sCodeMagasin, $nLongueur)
    For $i = 0 To UBound($aArrayShop, 1) - 1
        Local $sLibelleCourt = ""
        Local $sLibelleLong = ""
        If $sCodeMagasin == $aArrayShop[$i][0] Then
            $sLibelleCourt &= StringLeft($aArrayShop[$i][1], 20)
            $sLibelleLong &= StringLeft($aArrayShop[$i][1], 30)
            ExitLoop
        EndIf
    Next
    If $nLongueur = "court" Then Return $sLibelleCourt
    If $nLongueur = "long" Then Return $sLibelleLong
EndFunc

Extraction.xls

Zedna · June 20, 2018

Here are some minor speed optimizations:

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <WindowsConstants.au3>
#include <WinAPIShPath.au3>
#include <FontConstants.au3>
#include <StaticConstants.au3>

Global $chr32_max = StringRepeat(Chr(32), 256)
Global $chr0_max = _StringRepeat("0", 12)

; https://www.autoitscript.com/forum/topic/140190-stringrepeat-very-fast-using-memset/
Func StringRepeat($sChar, $nCount)
    $tBuffer = DLLStructCreate("char[" & $nCount & "]")
    DllCall("msvcrt.dll", "ptr:cdecl", "memset", "ptr", DLLStructGetPtr($tBuffer), "int", Asc($sChar), "int", $nCount)
    Return DLLStructGetData($tBuffer, 1)
EndFunc

Func _StringRepeat32($n)
    Return StringLeft($chr32_max, $n)
EndFunc

Func _StringRepeat0($n)
    Return StringLeft($chr0_max, $n)
EndFunc

Global $aArrayShop[8][3] = [["424", "CAISSE COGR POST", "0008"], _
    ["337", "CAISSE COGR HLT", "0003"], _
    ["407", "CAISSE COGR XERT", "0009"], _
    ["431", "CAISSE COGR ZERCO", "0008"], _
    ["349", "CAISSE COGR 349", "0007"], _
    ["406", "CAISSE COGR DSOG", "0009"], _
    ["385", "CAISSE COGR VILLAR", "0004"], _
    ["420", "CAISSE COGR THORX", "0005"]]

Global $aArrayIndicator[31][5] = [["Mouvements de Caisse", "CA HT", "", "", ""], _
    ["Mouvements de Caisse", "CA TTC", "", "CREDIT", ""], _
    ["Mouvements de Caisse", "TVA", "", "", ""], _
    ["Modes de reglement", "Acompte hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _ ; ALERTE
    ["Modes de reglement", "American Express", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "Avoir hors place", "0AV", "DEBIT", "SC"], _
    ["Modes de reglement", "Bon cadeau hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _
    ["Modes de reglement", "BEST RECORD", "0BEST" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de reglement", "CADHOC", "0CADH" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de reglement", "HAVAS", "0HAVA" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de reglement", "KADEOS", "0KADE" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de reglement", "Shopping Pass", "0SHOP" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de reglement", "TIR GROUPE", "0TIR" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de reglement", "C.B. manuelle", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "CA DO Cheque Banque Postale", "0CADO" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de reglement", "Carte bancaire", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "Carte Cadeau", "0CC", "DEBIT", "SC"], _
    ["Modes de reglement", "Carte ILLICADO", "0ILLI" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de reglement", "CB BRANDALLEY", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "CB CFAO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "CB REDOUTE", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "CB SPARTOO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "CB Web", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "Cheque", "0CHQ" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de reglement", "Reprise Acompte - P Diff", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de reglement", "Reprise Acompte Réservation", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de reglement", "Reprise d'acompte", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de reglement", "Reprise d'avoir", "0AV", "", "SC"], _
    ["Emissions (OSCC)", "Accompte", "0COGR" & _StringRepeat32(1), "CREDIT", ""], _
    ["Emissions (OSCC)", "Avoir", "0AV", "CREDIT", "SC"], _
    ["Emissions (OSCC)", "Carte cadeau", "0CC", "CREDIT", "SC"]]

Global $bWriteExcelExport = False
Global $bFirstLine = True

Window()
Func Window()
    Local $hGUI = GUICreate("Importation", 550, 147)
    Global $hFile = GUICtrlCreateLabel("Folder containing Excel files to import :", 20, 20, 280)
    GUICtrlSetFont(-1, 10)
    Global $hSelectFolderButton = GUICtrlCreateButton("Browse", 305, 17, 80)
    Global $idFolder = GUICtrlCreateInput("", 20, 60, 510)
    Global $hValider = GUICtrlCreateButton("OK", 20, 105, 80)
    GUICtrlSetState($hValider, $GUI_DISABLE)
    Local $sPath = @MyDocumentsDir & "\"

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hSelectFolderButton
                If FileExists(@TempDir & "\Conectis.txt") Then
                    $sPath = FileReadLine(@TempDir & "\Conectis.txt", 1)
                EndIf
                Global $sFileSelectFolder = FileSelectFolder("", $sPath)
                If Not @error Then
                    Local $sPathFile = @TempDir & "\Conectis.txt"
                    Local $hFileOpen = FileOpen($sPathFile, $FO_OVERWRITE)
                    FileWriteLine(@TempDir & "\Conectis.txt", $sFileSelectFolder)
                    GUICtrlSetData($idFolder, $sFileSelectFolder)
                    GUICtrlSetState($hValider, $GUI_ENABLE)
                EndIf
            Case $hValider
                GUICtrlSetState($hFile, $GUI_HIDE)
                GUICtrlSetState($hSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($idFolder, $GUI_HIDE)
                GUICtrlSetState($hSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($hValider, $GUI_HIDE)
                Global $sLabelWait = GUICtrlCreateLabel("Please wait...", 60, 52.5, 430, 25)
                GUICtrlSetFont(-1, 18)
                ListFiles($sFileSelectFolder)
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func ListFiles($sFolder)
    Local $aFileList = _FileListToArray($sFolder, "*", 1)
    If @error = 1 Then Exit MsgBox(64, "", "The path is invalid")
    If @error = 4 Then Exit MsgBox(64, "", "No Excel files were found in this folder.")

    FileDelete($sFileSelectFolder & "\Exclusions.xls")
    FileDelete($sFileSelectFolder & "\ImportQuadra.txt")

    ; Loops the list of files
    For $i = 1 To UBound($aFileList) - 1
        $sExtension = _WinAPI_PathFindExtension($aFileList[$i])
        If $sExtension = ".xls" Or $sExtension = ".xlsx" Then
            If $aFileList[$i] <> "Exclusions.xls" Then TransposeExcel($sFolder, $aFileList[$i])
        EndIf
    Next
    GUICtrlDelete($sLabelWait)
    GUICtrlSetState($hFile, $GUI_SHOW)
    GUICtrlSetState($hSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($idFolder, $GUI_SHOW)
    GUICtrlSetState($hSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($hValider, $GUI_SHOW)
    If ($i - 1) >= 1 And $bWriteExcelExport = True Then Window2($sFileSelectFolder & "\")
    If ($i - 1) >= 1 And $bWriteExcelExport = False Then Window3($sFileSelectFolder & "\")
EndFunc

Func Window2($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 150)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $sExclusions = GUICtrlCreateLabel("File of exclusions to be processed:", 30, 70, 200)
    GUICtrlSetFont(-1, 9)
    Local $sExclusionsFile = GUICtrlCreateLabel("Exclusions.xls", 207, 70, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sExclusionsFile, "0xAE0000")
    Local $hFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 115, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Open", 285, 111.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func Window3($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 125)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $hFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 90, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Ouvrir", 285, 86.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func TransposeExcel($sFolder, $sFile)
    Local $oExcel = _Excel_Open(False, False, False, False, True)
    If @error Then Exit
    Local $oWorkbookExport = _Excel_BookNew($oExcel)

    Local $oWorkbook = _Excel_BookOpen($oExcel, $sFolder & "\" & $sFile)
    If @error Then _Excel_Close($oExcel)

    Local $sResultA1 = _Excel_RangeRead($oWorkbook, Default, "A1")
    If Not @error Then
        Local $bNewFile = True
        Local $nLine = 2
        If $sResultA1 = "CODE MAGASIN" Then $sCodeMagasin = _Excel_RangeRead($oWorkbook, Default, "A2")
        Local $n = 2
        Do
            Local $sLigne = ""
            Local $nLongueurLigne = 0
            Local $sTypeEnregistrement = "M"    ; 1st character ; Length: 1 character
            Local $sNumeroCompte = ""           ; 2nd character ; Length: 8 characters
            Local $sLigneFolio = "000"          ; 12nd character ; Length: 3 characters
            Local $sDate = ""                   ; 15nd character ; Length: 6 characters
            Local $sLibelleCourt = ""           ; 22nd character ; Length: 20 characters
            Local $sLettreDebitCredit = ""      ; 23nd character ; Length: 1 character
            Local $sDebitCredit = ""            ; 43nd character ; Length: 13 characters
            Local $sDevise = "EUR"              ; 108nd character ; Length: 3 characters
            Local $sCodeJournal = "ODC"         ; 111nd character ; Length: 3 characters
            Local $sLibelleLong = ""            ; 117nd character ; Length: 30 characters

            ; ==> Recovery of Excel file cell contents
            Local $sColumA = _Excel_RangeRead($oWorkbook, Default, "A" & $n)
            If $sColumA == "" Then ExitLoop
            Local $sJour = _Excel_RangeRead($oWorkbook, Default, "B" & $n)
            Local $sCategorie = _Excel_RangeRead($oWorkbook, Default, "C" & $n)
            Local $sIndicateur = _Excel_RangeRead($oWorkbook, Default, "D" & $n)
            Local $sDebit = _Excel_RangeRead($oWorkbook, Default, "E" & $n)
            Local $sCredit = _Excel_RangeRead($oWorkbook, Default, "F" & $n)

            ; ==> Retrieving the account number
            $sNumeroCompte &= NumeroCompte($sCategorie, $sIndicateur, $sCodeMagasin)

            ; ==> Retrieving the date
            $sYear = StringMid($sJour, 3, 2)
            $sMonth = StringMid($sJour, 5, 2)
            $sDay = StringMid($sJour, 7, 2)
            $sDate &= $sDay & $sMonth & $sYear

            ; ==> Retrieving the short text
            $sLibelleCourt &= Libelle($sCodeMagasin, "court")

            ; ==> Retrieving the long text
            $sLibelleLong &= Libelle($sCodeMagasin, "long")

            ; ==> Retrieving the debit OR credit letter ('C' OU 'D')
            If $sDebit <> "" And $sCredit == "" Then
                If $sDebit <> 0 Then
                    $sLettreDebitCredit = "D"
                    ; ==> Recovery of debit amount
                    $sDebitCredit &= Formatage($sDebit)
                Else
                    $sDebitCredit = "ZERO"
                EndIf
            Else
                If $sCredit <> 0 Then
                    $sLettreDebitCredit = "C"
                    ; ==> Recovery of credit amount
                    $sDebitCredit &= Formatage($sCredit)
                Else
                    $sDebitCredit = "ZERO"
                EndIf
            EndIf

            ; ==> Complete line to write in the file
            $sLigne &= $sTypeEnregistrement & $sNumeroCompte & _StringRepeat32(2) & $sLigneFolio & $sDate & _StringRepeat32(1) & _
                $sLibelleCourt & $sLettreDebitCredit & $sDebitCredit & _StringRepeat32(52) & $sDevise & $sCodeJournal & _StringRepeat32(3) & $sLibelleLong

            $nLongueurLigne = StringLen($sLigne)
            $sLigne &= _StringRepeat32(256 - $nLongueurLigne)

            ; ==> Saving the results of the processed file
            If StringIsSpace($sNumeroCompte) Or $sNumeroCompte == "Acompte hors place" Or $sNumeroCompte == "Avoir hors place" Or $sDebitCredit == "ZERO" Then
                If $bFirstLine = True Then
                    _Excel_RangeWrite($oWorkbookExport, Default, "CODE MAGASIN", "A1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "JOUR", "B1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "CATEGORIE", "C1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "INDICATEUR", "D1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "DEBIT", "E1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "CREDIT", "F1")
                    $bWriteExcelExport = True
                    $bFirstLine = False
                EndIf

                _Excel_RangeCopyPaste($oWorkbook.Sheets(1), $oWorkbook.Sheets(1).Range("A" & $n).EntireRow, $oWorkbookExport.Sheets(1).Range("A" & $nLine))
                $nLine += 1
            Else
                Local $hFileOpen = FileOpen($sFileSelectFolder & "\ImportQuadra.txt", 1)
                If $hFileOpen = -1 Then
                    MsgBox(16, "", "Cannot create file " & $sFileSelectFolder & "\ImportQuadra.txt")
                    Return False
                EndIf
                FileWriteLine($sFileSelectFolder & "\ImportQuadra.txt", $sLigne)
            EndIf
            $n += 1
        Until $sColumA == ""
        If $bWriteExcelExport = True Then _Excel_BookSaveAs($oWorkbookExport, $sFileSelectFolder & "\Exclusions.xls", Default, True)
        _Excel_BookClose($oWorkbookExport)
        _Excel_BookClose($oWorkbook)
        _Excel_Close($oExcel)
    EndIf
EndFunc

Func Formatage($sCellule)
    ; ==> Function that formats in 12 characters the debit & credit columns of the rows
    Local $sMontant = ""
    If IsNumber($sCellule) Then
        ; ==> Any + and - characters that may be present are deleted
        $sCellule = StringRegExpReplace($sCellule, "[^\d\.]", "")

        Local $PositionPoint = StringInStr($sCellule, ".", 1)
        Local $sDecimales = ""
        If $PositionPoint Then ; There's a point
            $sDecimales = StringTrimLeft($sCellule, $PositionPoint)
            $sNumber = StringLeft($sCellule, $PositionPoint - 1)
            If StringLen($sDecimales) < 2 Then
                $sDecimales &= "0"
            EndIf
            $sMontant &= $sNumber & $sDecimales
            $nLongueur = StringLen($sMontant)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sMontant
        Else ; There is no point
            $sDecimales &= $sCellule & "00"
            $nLongueur = StringLen($sDecimales)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sDecimales
        EndIf
    EndIf
    Return $sMontant
EndFunc

Func NumeroCompte($sCategorie, $sIndicateur, $sCodeMagasin)
    For $i = 0 To UBound($aArrayIndicator, 1) - 1
        Local $sNumeroCompte = ""
        Local $sSCompte = ""
        If $sCategorie == $aArrayIndicator[$i][0] And $sIndicateur == $aArrayIndicator[$i][1] Then
            $sNumeroCompte &= $aArrayIndicator[$i][2]
            If $aArrayIndicator[$i][1] == "Acompte hors place" Then
                $sNumeroCompte = "Acompte hors place"
                ExitLoop
            ElseIf $aArrayIndicator[$i][1] == "Avoir hors place" Then
                $sNumeroCompte = "Avoir hors place"
                ExitLoop
            EndIf
            If $aArrayIndicator[$i][4] == "SC" Then
                For $i = 0 To UBound($aArrayShop, 1) - 1
                    If $sCodeMagasin == $aArrayShop[$i][0] Then
                        $sSCompte = $aArrayShop[$i][2]
                        ExitLoop
                    EndIf
                Next
                $sNumeroCompte &= $sSCompte
            EndIf
            $nLongueur = StringLen($sNumeroCompte)
            $sNumeroCompte &= _StringRepeat32(8 - $nLongueur)
            ExitLoop
        EndIf
    Next
    Return $sNumeroCompte
EndFunc

Func Libelle($sCodeMagasin, $nLongueur)
    For $i = 0 To UBound($aArrayShop, 1) - 1
        Local $sLibelleCourt = ""
        Local $sLibelleLong = ""
        If $sCodeMagasin == $aArrayShop[$i][0] Then
            $sLibelleCourt &= StringLeft($aArrayShop[$i][1], 20)
            $sLibelleLong &= StringLeft($aArrayShop[$i][1], 30)
            ExitLoop
        EndIf
    Next
    If $nLongueur == "court" Then Return $sLibelleCourt
    If $nLongueur == "long" Then Return $sLibelleLong
EndFunc

jerem488 · June 21, 2018

I take, even small optimizations! Thank you

Subz · June 21, 2018

In edition to Zednas optimizations, have made a few other optimizations, which should make it run a bit faster, unfortunately haven't really had the time to debug, got to have some shuteye before work in a couple of hours.

Hope it helps:

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <WindowsConstants.au3>
#include <WinAPIShPath.au3>
#include <FontConstants.au3>
#include <StaticConstants.au3>
#include <Debug.au3>
#include <ExcelConstants.au3>

Global $chr32_max = StringRepeat(Chr(32), 256)
Global $chr0_max = _StringRepeat("0", 12)

; https://www.autoitscript.com/forum/topic/140190-stringrepeat-very-fast-using-memset/
Func StringRepeat($sChar, $nCount)
    $tBuffer = DLLStructCreate("char[" & $nCount & "]")
    DllCall("msvcrt.dll", "ptr:cdecl", "memset", "ptr", DLLStructGetPtr($tBuffer), "int", Asc($sChar), "int", $nCount)
    Return DLLStructGetData($tBuffer, 1)
EndFunc

Func _StringRepeat32($n)
    Return StringLeft($chr32_max, $n)
EndFunc

Func _StringRepeat0($n)
    Return StringLeft($chr0_max, $n)
EndFunc

Global $g_aShop[8][3] = [["424", "CAISSE COGR POST", "0008"], _
    ["337", "CAISSE COGR HLT", "0003"], _
    ["407", "CAISSE COGR XERT", "0009"], _
    ["431", "CAISSE COGR ZERCO", "0008"], _
    ["349", "CAISSE COGR 349", "0007"], _
    ["406", "CAISSE COGR DSOG", "0009"], _
    ["385", "CAISSE COGR VILLAR", "0004"], _
    ["420", "CAISSE COGR THORX", "0005"]]

Global $g_aIndicator[31][5] = [["Mouvements de Caisse", "CA HT", "", "", ""], _
    ["Mouvements de Caisse", "CA TTC", "", "CREDIT", ""], _
    ["Mouvements de Caisse", "TVA", "", "", ""], _
    ["Modes de règlement", "Acompte hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _ ; ALERTE
    ["Modes de règlement", "American Express", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Avoir hors place", "0AV", "DEBIT", "SC"], _
    ["Modes de règlement", "Bon cadeau hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _
    ["Modes de règlement", "BEST RECORD", "0BEST" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CADHOC", "0CADH" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "HAVAS", "0HAVA" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "KADEOS", "0KADE" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Shopping Pass", "0SHOP" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "TIR GROUPE", "0TIR" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "C.B. manuelle", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CA DO Cheque Banque Postale", "0CADO" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Carte bancaire", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Carte Cadeau", "0CC", "DEBIT", "SC"], _
    ["Modes de règlement", "Carte ILLICADO", "0ILLI" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CB BRANDALLEY", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB CFAO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB REDOUTE", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB SPARTOO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB Web", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Cheque", "0CHQ" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "Reprise Acompte - P Diff", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise Acompte Réservation", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'acompte", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'avoir", "0AV", "", "SC"], _
    ["Emissions (OSCC)", "Accompte", "0COGR" & _StringRepeat32(1), "CREDIT", ""], _
    ["Emissions (OSCC)", "Avoir", "0AV", "CREDIT", "SC"], _
    ["Emissions (OSCC)", "Carte cadeau", "0CC", "CREDIT", "SC"]]

Global $g_bWriteExcelExport = False
Global $bFirstLine = True
Global $g_idFile, $g_sFileSelectFolder, $g_idSelectFolderButton, $g_idFolder, $g_idOKButton, $g_idLabelWait

Window()

Func Window()
    Local $hGUI = GUICreate("Importation", 550, 147)
    $g_idFile = GUICtrlCreateLabel("Folder containing Excel files to import :", 20, 20, 280)
        GUICtrlSetFont(-1, 10)
    $g_idSelectFolderButton = GUICtrlCreateButton("Browse", 305, 17, 80)
    $g_idFolder = GUICtrlCreateInput("", 20, 60, 510)
    $g_idOKButton = GUICtrlCreateButton("OK", 20, 105, 80)
    GUICtrlSetState($g_idOKButton, $GUI_DISABLE)
    Local $sPath = @MyDocumentsDir & "\"

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $g_idSelectFolderButton
                If FileExists(@TempDir & "\Conectis.txt") Then
                    $sPath = FileReadLine(@TempDir & "\Conectis.txt", 1)
                EndIf
                $g_sFileSelectFolder = FileSelectFolder("", $sPath)
                If Not @error Then
                    Local $sPathFile = @TempDir & "\Conectis.txt"
                    Local $g_idFileOpen = FileOpen($sPathFile, $FO_OVERWRITE)
                    FileWriteLine(@TempDir & "\Conectis.txt", $g_sFileSelectFolder)
                    GUICtrlSetData($g_idFolder, $g_sFileSelectFolder)
                    GUICtrlSetState($g_idOKButton, $GUI_ENABLE)
                EndIf
            Case $g_idOKButton
                GUICtrlSetState($g_idFile, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idFolder, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idOKButton, $GUI_HIDE)
                $g_idLabelWait = GUICtrlCreateLabel("Please wait...", 60, 52.5, 430, 25)
                GUICtrlSetFont(-1, 18)
                ListFiles($g_sFileSelectFolder)
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func ListFiles($sFolder)
    Local $aFileList = _FileListToArrayRec($sFolder, "*.xls;*.xlsx", 1)
    If @error = 1 Then Exit MsgBox(64, "", "The path is invalid")
    If @error = 4 Then Exit MsgBox(64, "", "No Excel files were found in this folder.")

    FileDelete($g_sFileSelectFolder & "\Exclusions.xls")
    FileDelete($g_sFileSelectFolder & "\ImportQuadra.txt")

    ; Loops the list of files
    For $i = 1 To UBound($aFileList) - 1
        If $aFileList[$i] <> "Exclusions.xls" Then TransposeExcel($sFolder, $aFileList[$i])
    Next
    GUICtrlDelete($g_idLabelWait)
    GUICtrlSetState($g_idFile, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idFolder, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idOKButton, $GUI_SHOW)
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = True Then Window2($g_sFileSelectFolder & "\")
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = False Then Window3($g_sFileSelectFolder & "\")
EndFunc

Func Window2($sFolder)

    Local $hGUI = GUICreate("Importation Quadra", 380, 150)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 10, 40, 100)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 220, 40, 100)
        GUICtrlSetFont($sQuadraFile, 9)
        GUICtrlSetColor($sQuadraFile, "0x4800FF")
        GUICtrlSetCursor($sQuadraFile, 0)
    Local $sExclusions = GUICtrlCreateLabel("File of exclusions to be processed:", 10, 70, 200)
        GUICtrlSetFont(-1, 9)
    Local $sExclusionsFile = GUICtrlCreateLabel("Exclusions.xls", 220, 70, 100)
        GUICtrlSetFont($sExclusionsFile, 9)
        GUICtrlSetColor($sExclusionsFile, "0xAE0000")
        GUICtrlSetCursor($sExclusionsFile, 0)
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 115, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Open", 285, 111.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
            Case $sQuadraFile
                ShellExecute($sFolder & "\ImportQuadra.txt")
            Case $sExclusionsFile
                ShellExecute($sFolder & "\Exclusions.xlsx")
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func Window3($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 125)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 90, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Ouvrir", 285, 86.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func TransposeExcel($sFolder, $sFile)
    Local $_iIndex = 1
    Local $g_idFileOpen = FileOpen($g_sFileSelectFolder & "\ImportQuadra.txt", 1)
        If $g_idFileOpen = -1 Then
            MsgBox(16, "", "Cannot create file " & $g_sFileSelectFolder & "\ImportQuadra.txt")
            Return False
        EndIf
    Local $oExcel = _Excel_Open(False, False, False, False, True)
        If @error Then Return MsgBox(4096, "Excel Error", "Unable to open Excel.")
    Local $_oImport = _Excel_BookOpen($oExcel, $sFolder & "\" & $sFile)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Opening Workbook", "Unable to open : " & $sFolder & "\" & $sFile)
        EndIf
    Local $_aImport = _Excel_RangeRead($_oImport, Default, $_oImport.ActiveSheet.UsedRange)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel RangeRead Error",  "Error reading from workbook:" & @CRLF & "Filename : " & $sFolder & "\" & $sFile & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        EndIf
        _Excel_BookClose($_oImport)

    Local $_aExport[1][6] = [["CODE MAGASIN", "JOUR", "CATEGORIE", "INDICATEUR", "DEBIT", "CREDIT"]]
    Local $_aDefault_ExportInfo[12], $_aDefault_ExportInfo
            $_aDefault_ExportInfo[0] = ""       ;~ $sLigne
            $_aDefault_ExportInfo[1] = 0        ;~ $nLongueurLigne = 0
            $_aDefault_ExportInfo[2] = "M"      ;~ $sTypeEnregistrement = "M"    ; 1st character ; Length: 1 character
            $_aDefault_ExportInfo[3] = ""       ;~ $sNumeroCompte = ""           ; 2nd character ; Length: 8 characters
            $_aDefault_ExportInfo[4] = "000"    ;~ $sLigneFolio = "000"          ; 12nd character ; Length: 3 characters
            $_aDefault_ExportInfo[5] = ""       ;~ $sDate = ""                   ; 15nd character ; Length: 6 characters
            $_aDefault_ExportInfo[6] = ""       ;~ $sLibelleCourt = ""           ; 22nd character ; Length: 20 characters
            $_aDefault_ExportInfo[7] = ""       ;~ $sLettreDebitCredit = ""      ; 23nd character ; Length: 1 character
            $_aDefault_ExportInfo[8] = ""       ;~ $sDebitCredit = ""            ; 43nd character ; Length: 13 characters
            $_aDefault_ExportInfo[9] = "EUR"    ;~ $sDevise = "EUR"              ; 108nd character ; Length: 3 characters
            $_aDefault_ExportInfo[10] = "ODC"   ;~ $sCodeJournal = "ODC"         ; 111nd character ; Length: 3 characters
            $_aDefault_ExportInfo[11] = ""      ;~ $sLibelleLong = ""            ; 117nd character ; Length: 30 characters

    For $i = 0 To UBound($_aImport) - 1
        If $_aImport[$i][0] = "CODE MAGASIN" Then ContinueLoop
        If $_aImport[$i][0] = "" Then ContinueLoop
        $_iIndex += 1
        $_aExportInfo = $_aDefault_ExportInfo
    ;~ Reformat Date
        $_aExportInfo[5] = StringFormat("%02i%02i%02i", StringMid($_aImport[$i][1], 7, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 3, 2))
        $_aImport[$i][1] = StringFormat("%2i/%2i/%4i", StringMid($_aImport[$i][1], 3, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 7, 2))
    ;~ Configure Column c Categorie - Retrieving the account number
        $_aExportInfo[3] &= NumeroCompte($_aImport[$i][2], $_aImport[$i][3], $_aImport[$i][0])

    ; ==> Retrieving the short text
        $_aExportInfo[6] &= Libelle($_aImport[$i][0], "court")
    ; ==> Retrieving the long text
        $_aExportInfo[11] &= Libelle($_aImport[$i][0], "long")
    ; ==> Retrieving the debit OR credit letter ('C' OU 'D')
        If $_aImport[$i][4] <> "" And $_aImport[$i][5] == "" Then
            If $_aImport[$i][4] <> 0 Then
                $_aExportInfo[7] = "D"
            ; ==> Recovery of debit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][4])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        Else
            If $_aImport[$i][5] <> 0 Then
                $_aExportInfo[7] = "C"
            ; ==> Recovery of credit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][5])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        EndIf
    ; ==> Complete line to write in the file
        $_aExportInfo[0] &= $_aExportInfo[2] & $_aExportInfo[3] & _StringRepeat32(2) & $_aExportInfo[4] & $_aExportInfo[5] & _StringRepeat32(1) & _
        $_aExportInfo[6] & $_aExportInfo[7] & $_aExportInfo[8] & _StringRepeat32(52) & $_aExportInfo[9] & $_aExportInfo[10] & _StringRepeat32(3) & $_aExportInfo[11]
        $_aExportInfo[1] = StringLen($_aExportInfo[0])
        $_aExportInfo[0] &= _StringRepeat32(256 - $_aExportInfo[1])
    ; ==> Saving the results of the processed file
        If StringIsSpace($_aExportInfo[3]) Or $_aExportInfo[3] == "Acompte hors place" Or $_aExportInfo[3] == "Avoir hors place" Or $_aExportInfo[8] == "ZERO" Then
            _ArrayAdd($_aExport, _ArrayToString($_aImport, "|", $i, $i))
            ContinueLoop
        Else
            FileWriteLine($g_sFileSelectFolder & "\ImportQuadra.txt", $_aExportInfo[0])
        EndIf
    Next
    Local $_oExport = _Excel_BookNew($oExcel)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Create Workbook", "Unable to create new Workbook")
        EndIf
    _Excel_RangeWrite($_oExport, $_oExport.ActiveSheet, $_aExport)
    _Excel_BookSaveAs($_oExport, $g_sFileSelectFolder & "\Exclusions.xlsx", $xlWorkbookDefault)
    If @error Then $g_bWriteExcelExport = False
    $g_bWriteExcelExport = True
    _Excel_BookClose($_oExport)
    _Excel_Close($oExcel)
EndFunc

Func Formatage($sCellule)
    ; ==> Function that formats in 12 characters the debit & credit columns of the rows
    Local $sMontant = ""
    If IsNumber($sCellule) Then
        ; ==> Any + and - characters that may be present are deleted
        $sCellule = StringRegExpReplace($sCellule, "[^\d\.]", "")

        Local $PositionPoint = StringInStr($sCellule, ".", 1)
        Local $sDecimales = ""
        If $PositionPoint Then ; There's a point
            $sDecimales = StringTrimLeft($sCellule, $PositionPoint)
            $sNumber = StringLeft($sCellule, $PositionPoint - 1)
            If StringLen($sDecimales) < 2 Then
                $sDecimales &= "0"
            EndIf
            $sMontant &= $sNumber & $sDecimales
            $nLongueur = StringLen($sMontant)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sMontant
        Else ; There is no point
            $sDecimales &= $sCellule & "00"
            $nLongueur = StringLen($sDecimales)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sDecimales
        EndIf
    EndIf
    Return $sMontant
EndFunc

Func NumeroCompte($p_sCategorie, $p_sIndicateur, $p_sCodeMagasin)
    Local $_iSearchShop
    Local $_iFindIndicator = _ArrayFindAll($g_aIndicator, $p_sCategorie, 0, 0, 0, 2, 0)
    Local $sNumeroCompte = "", $sSCompte = ""
    For $i = 0 To UBound($_iFindIndicator) - 1
        If $p_sIndicateur == $g_aIndicator[$_iFindIndicator[$i]][1] Then
            $sNumeroCompte &= $g_aIndicator[$_iFindIndicator[$i]][2]
            If $g_aIndicator[$_iFindIndicator[$i]][1] == "Acompte hors place" Then
                $sNumeroCompte = "Acompte hors place"
                ExitLoop
            ElseIf $g_aIndicator[$_iFindIndicator[$i]][1] == "Avoir hors place" Then
                $sNumeroCompte = "Avoir hors place"
                ExitLoop
            EndIf
            If $g_aIndicator[$_iFindIndicator[$i]][4] == "SC" Then
                $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
                    If $_iSearchShop <> -1 Then $sNumeroCompte &= $g_aShop[$_iSearchShop][2]
            EndIf
            $nLongueur = StringLen($sNumeroCompte)
            $sNumeroCompte &= _StringRepeat32(8 - $nLongueur)
            ExitLoop
        EndIf
    Next
    Return $sNumeroCompte
EndFunc

Func Libelle($p_sCodeMagasin, $p_nLongueur)
    Local $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
    If $_iSearchShop = -1 Then Return
    Switch $p_nLongueur
        Case "court"
            Return StringLeft($g_aShop[$_iSearchShop][1], 20)
        Case "long"
            Return StringLeft($g_aShop[$_iSearchShop][1], 30)
    EndSwitch
EndFunc

jerem488 · June 21, 2018

4 hours ago, Subz said:

In edition to Zednas optimizations, have made a few other optimizations, which should make it run a bit faster, unfortunately haven't really had the time to debug, got to have some shuteye before work in a couple of hours.

Hope it helps:

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <WindowsConstants.au3>
#include <WinAPIShPath.au3>
#include <FontConstants.au3>
#include <StaticConstants.au3>
#include <Debug.au3>
#include <ExcelConstants.au3>

Global $chr32_max = StringRepeat(Chr(32), 256)
Global $chr0_max = _StringRepeat("0", 12)

; https://www.autoitscript.com/forum/topic/140190-stringrepeat-very-fast-using-memset/
Func StringRepeat($sChar, $nCount)
    $tBuffer = DLLStructCreate("char[" & $nCount & "]")
    DllCall("msvcrt.dll", "ptr:cdecl", "memset", "ptr", DLLStructGetPtr($tBuffer), "int", Asc($sChar), "int", $nCount)
    Return DLLStructGetData($tBuffer, 1)
EndFunc

Func _StringRepeat32($n)
    Return StringLeft($chr32_max, $n)
EndFunc

Func _StringRepeat0($n)
    Return StringLeft($chr0_max, $n)
EndFunc

Global $g_aShop[8][3] = [["424", "CAISSE COGR POST", "0008"], _
    ["337", "CAISSE COGR HLT", "0003"], _
    ["407", "CAISSE COGR XERT", "0009"], _
    ["431", "CAISSE COGR ZERCO", "0008"], _
    ["349", "CAISSE COGR 349", "0007"], _
    ["406", "CAISSE COGR DSOG", "0009"], _
    ["385", "CAISSE COGR VILLAR", "0004"], _
    ["420", "CAISSE COGR THORX", "0005"]]

Global $g_aIndicator[31][5] = [["Mouvements de Caisse", "CA HT", "", "", ""], _
    ["Mouvements de Caisse", "CA TTC", "", "CREDIT", ""], _
    ["Mouvements de Caisse", "TVA", "", "", ""], _
    ["Modes de règlement", "Acompte hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _ ; ALERTE
    ["Modes de règlement", "American Express", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Avoir hors place", "0AV", "DEBIT", "SC"], _
    ["Modes de règlement", "Bon cadeau hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _
    ["Modes de règlement", "BEST RECORD", "0BEST" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CADHOC", "0CADH" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "HAVAS", "0HAVA" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "KADEOS", "0KADE" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Shopping Pass", "0SHOP" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "TIR GROUPE", "0TIR" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "C.B. manuelle", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CA DO Cheque Banque Postale", "0CADO" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Carte bancaire", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Carte Cadeau", "0CC", "DEBIT", "SC"], _
    ["Modes de règlement", "Carte ILLICADO", "0ILLI" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CB BRANDALLEY", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB CFAO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB REDOUTE", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB SPARTOO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB Web", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Cheque", "0CHQ" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "Reprise Acompte - P Diff", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise Acompte Réservation", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'acompte", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'avoir", "0AV", "", "SC"], _
    ["Emissions (OSCC)", "Accompte", "0COGR" & _StringRepeat32(1), "CREDIT", ""], _
    ["Emissions (OSCC)", "Avoir", "0AV", "CREDIT", "SC"], _
    ["Emissions (OSCC)", "Carte cadeau", "0CC", "CREDIT", "SC"]]

Global $g_bWriteExcelExport = False
Global $bFirstLine = True
Global $g_idFile, $g_sFileSelectFolder, $g_idSelectFolderButton, $g_idFolder, $g_idOKButton, $g_idLabelWait

Window()

Func Window()
    Local $hGUI = GUICreate("Importation", 550, 147)
    $g_idFile = GUICtrlCreateLabel("Folder containing Excel files to import :", 20, 20, 280)
        GUICtrlSetFont(-1, 10)
    $g_idSelectFolderButton = GUICtrlCreateButton("Browse", 305, 17, 80)
    $g_idFolder = GUICtrlCreateInput("", 20, 60, 510)
    $g_idOKButton = GUICtrlCreateButton("OK", 20, 105, 80)
    GUICtrlSetState($g_idOKButton, $GUI_DISABLE)
    Local $sPath = @MyDocumentsDir & "\"

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $g_idSelectFolderButton
                If FileExists(@TempDir & "\Conectis.txt") Then
                    $sPath = FileReadLine(@TempDir & "\Conectis.txt", 1)
                EndIf
                $g_sFileSelectFolder = FileSelectFolder("", $sPath)
                If Not @error Then
                    Local $sPathFile = @TempDir & "\Conectis.txt"
                    Local $g_idFileOpen = FileOpen($sPathFile, $FO_OVERWRITE)
                    FileWriteLine(@TempDir & "\Conectis.txt", $g_sFileSelectFolder)
                    GUICtrlSetData($g_idFolder, $g_sFileSelectFolder)
                    GUICtrlSetState($g_idOKButton, $GUI_ENABLE)
                EndIf
            Case $g_idOKButton
                GUICtrlSetState($g_idFile, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idFolder, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idOKButton, $GUI_HIDE)
                $g_idLabelWait = GUICtrlCreateLabel("Please wait...", 60, 52.5, 430, 25)
                GUICtrlSetFont(-1, 18)
                ListFiles($g_sFileSelectFolder)
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func ListFiles($sFolder)
    Local $aFileList = _FileListToArrayRec($sFolder, "*.xls;*.xlsx", 1)
    If @error = 1 Then Exit MsgBox(64, "", "The path is invalid")
    If @error = 4 Then Exit MsgBox(64, "", "No Excel files were found in this folder.")

    FileDelete($g_sFileSelectFolder & "\Exclusions.xls")
    FileDelete($g_sFileSelectFolder & "\ImportQuadra.txt")

    ; Loops the list of files
    For $i = 1 To UBound($aFileList) - 1
        If $aFileList[$i] <> "Exclusions.xls" Then TransposeExcel($sFolder, $aFileList[$i])
    Next
    GUICtrlDelete($g_idLabelWait)
    GUICtrlSetState($g_idFile, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idFolder, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idOKButton, $GUI_SHOW)
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = True Then Window2($g_sFileSelectFolder & "\")
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = False Then Window3($g_sFileSelectFolder & "\")
EndFunc

Func Window2($sFolder)

    Local $hGUI = GUICreate("Importation Quadra", 380, 150)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 10, 40, 100)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 220, 40, 100)
        GUICtrlSetFont($sQuadraFile, 9)
        GUICtrlSetColor($sQuadraFile, "0x4800FF")
        GUICtrlSetCursor($sQuadraFile, 0)
    Local $sExclusions = GUICtrlCreateLabel("File of exclusions to be processed:", 10, 70, 200)
        GUICtrlSetFont(-1, 9)
    Local $sExclusionsFile = GUICtrlCreateLabel("Exclusions.xls", 220, 70, 100)
        GUICtrlSetFont($sExclusionsFile, 9)
        GUICtrlSetColor($sExclusionsFile, "0xAE0000")
        GUICtrlSetCursor($sExclusionsFile, 0)
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 115, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Open", 285, 111.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
            Case $sQuadraFile
                ShellExecute($sFolder & "\ImportQuadra.txt")
            Case $sExclusionsFile
                ShellExecute($sFolder & "\Exclusions.xlsx")
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func Window3($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 125)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 90, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Ouvrir", 285, 86.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func TransposeExcel($sFolder, $sFile)
    Local $_iIndex = 1
    Local $g_idFileOpen = FileOpen($g_sFileSelectFolder & "\ImportQuadra.txt", 1)
        If $g_idFileOpen = -1 Then
            MsgBox(16, "", "Cannot create file " & $g_sFileSelectFolder & "\ImportQuadra.txt")
            Return False
        EndIf
    Local $oExcel = _Excel_Open(False, False, False, False, True)
        If @error Then Return MsgBox(4096, "Excel Error", "Unable to open Excel.")
    Local $_oImport = _Excel_BookOpen($oExcel, $sFolder & "\" & $sFile)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Opening Workbook", "Unable to open : " & $sFolder & "\" & $sFile)
        EndIf
    Local $_aImport = _Excel_RangeRead($_oImport, Default, $_oImport.ActiveSheet.UsedRange)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel RangeRead Error",  "Error reading from workbook:" & @CRLF & "Filename : " & $sFolder & "\" & $sFile & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        EndIf
        _Excel_BookClose($_oImport)

    Local $_aExport[1][6] = [["CODE MAGASIN", "JOUR", "CATEGORIE", "INDICATEUR", "DEBIT", "CREDIT"]]
    Local $_aDefault_ExportInfo[12], $_aDefault_ExportInfo
            $_aDefault_ExportInfo[0] = ""       ;~ $sLigne
            $_aDefault_ExportInfo[1] = 0        ;~ $nLongueurLigne = 0
            $_aDefault_ExportInfo[2] = "M"      ;~ $sTypeEnregistrement = "M"    ; 1st character ; Length: 1 character
            $_aDefault_ExportInfo[3] = ""       ;~ $sNumeroCompte = ""           ; 2nd character ; Length: 8 characters
            $_aDefault_ExportInfo[4] = "000"    ;~ $sLigneFolio = "000"          ; 12nd character ; Length: 3 characters
            $_aDefault_ExportInfo[5] = ""       ;~ $sDate = ""                   ; 15nd character ; Length: 6 characters
            $_aDefault_ExportInfo[6] = ""       ;~ $sLibelleCourt = ""           ; 22nd character ; Length: 20 characters
            $_aDefault_ExportInfo[7] = ""       ;~ $sLettreDebitCredit = ""      ; 23nd character ; Length: 1 character
            $_aDefault_ExportInfo[8] = ""       ;~ $sDebitCredit = ""            ; 43nd character ; Length: 13 characters
            $_aDefault_ExportInfo[9] = "EUR"    ;~ $sDevise = "EUR"              ; 108nd character ; Length: 3 characters
            $_aDefault_ExportInfo[10] = "ODC"   ;~ $sCodeJournal = "ODC"         ; 111nd character ; Length: 3 characters
            $_aDefault_ExportInfo[11] = ""      ;~ $sLibelleLong = ""            ; 117nd character ; Length: 30 characters

    For $i = 0 To UBound($_aImport) - 1
        If $_aImport[$i][0] = "CODE MAGASIN" Then ContinueLoop
        If $_aImport[$i][0] = "" Then ContinueLoop
        $_iIndex += 1
        $_aExportInfo = $_aDefault_ExportInfo
    ;~ Reformat Date
        $_aExportInfo[5] = StringFormat("%02i%02i%02i", StringMid($_aImport[$i][1], 7, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 3, 2))
        $_aImport[$i][1] = StringFormat("%2i/%2i/%4i", StringMid($_aImport[$i][1], 3, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 7, 2))
    ;~ Configure Column c Categorie - Retrieving the account number
        $_aExportInfo[3] &= NumeroCompte($_aImport[$i][2], $_aImport[$i][3], $_aImport[$i][0])

    ; ==> Retrieving the short text
        $_aExportInfo[6] &= Libelle($_aImport[$i][0], "court")
    ; ==> Retrieving the long text
        $_aExportInfo[11] &= Libelle($_aImport[$i][0], "long")
    ; ==> Retrieving the debit OR credit letter ('C' OU 'D')
        If $_aImport[$i][4] <> "" And $_aImport[$i][5] == "" Then
            If $_aImport[$i][4] <> 0 Then
                $_aExportInfo[7] = "D"
            ; ==> Recovery of debit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][4])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        Else
            If $_aImport[$i][5] <> 0 Then
                $_aExportInfo[7] = "C"
            ; ==> Recovery of credit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][5])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        EndIf
    ; ==> Complete line to write in the file
        $_aExportInfo[0] &= $_aExportInfo[2] & $_aExportInfo[3] & _StringRepeat32(2) & $_aExportInfo[4] & $_aExportInfo[5] & _StringRepeat32(1) & _
        $_aExportInfo[6] & $_aExportInfo[7] & $_aExportInfo[8] & _StringRepeat32(52) & $_aExportInfo[9] & $_aExportInfo[10] & _StringRepeat32(3) & $_aExportInfo[11]
        $_aExportInfo[1] = StringLen($_aExportInfo[0])
        $_aExportInfo[0] &= _StringRepeat32(256 - $_aExportInfo[1])
    ; ==> Saving the results of the processed file
        If StringIsSpace($_aExportInfo[3]) Or $_aExportInfo[3] == "Acompte hors place" Or $_aExportInfo[3] == "Avoir hors place" Or $_aExportInfo[8] == "ZERO" Then
            _ArrayAdd($_aExport, _ArrayToString($_aImport, "|", $i, $i))
            ContinueLoop
        Else
            FileWriteLine($g_sFileSelectFolder & "\ImportQuadra.txt", $_aExportInfo[0])
        EndIf
    Next
    Local $_oExport = _Excel_BookNew($oExcel)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Create Workbook", "Unable to create new Workbook")
        EndIf
    _Excel_RangeWrite($_oExport, $_oExport.ActiveSheet, $_aExport)
    _Excel_BookSaveAs($_oExport, $g_sFileSelectFolder & "\Exclusions.xlsx", $xlWorkbookDefault)
    If @error Then $g_bWriteExcelExport = False
    $g_bWriteExcelExport = True
    _Excel_BookClose($_oExport)
    _Excel_Close($oExcel)
EndFunc

Func Formatage($sCellule)
    ; ==> Function that formats in 12 characters the debit & credit columns of the rows
    Local $sMontant = ""
    If IsNumber($sCellule) Then
        ; ==> Any + and - characters that may be present are deleted
        $sCellule = StringRegExpReplace($sCellule, "[^\d\.]", "")

        Local $PositionPoint = StringInStr($sCellule, ".", 1)
        Local $sDecimales = ""
        If $PositionPoint Then ; There's a point
            $sDecimales = StringTrimLeft($sCellule, $PositionPoint)
            $sNumber = StringLeft($sCellule, $PositionPoint - 1)
            If StringLen($sDecimales) < 2 Then
                $sDecimales &= "0"
            EndIf
            $sMontant &= $sNumber & $sDecimales
            $nLongueur = StringLen($sMontant)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sMontant
        Else ; There is no point
            $sDecimales &= $sCellule & "00"
            $nLongueur = StringLen($sDecimales)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sDecimales
        EndIf
    EndIf
    Return $sMontant
EndFunc

Func NumeroCompte($p_sCategorie, $p_sIndicateur, $p_sCodeMagasin)
    Local $_iSearchShop
    Local $_iFindIndicator = _ArrayFindAll($g_aIndicator, $p_sCategorie, 0, 0, 0, 2, 0)
    Local $sNumeroCompte = "", $sSCompte = ""
    For $i = 0 To UBound($_iFindIndicator) - 1
        If $p_sIndicateur == $g_aIndicator[$_iFindIndicator[$i]][1] Then
            $sNumeroCompte &= $g_aIndicator[$_iFindIndicator[$i]][2]
            If $g_aIndicator[$_iFindIndicator[$i]][1] == "Acompte hors place" Then
                $sNumeroCompte = "Acompte hors place"
                ExitLoop
            ElseIf $g_aIndicator[$_iFindIndicator[$i]][1] == "Avoir hors place" Then
                $sNumeroCompte = "Avoir hors place"
                ExitLoop
            EndIf
            If $g_aIndicator[$_iFindIndicator[$i]][4] == "SC" Then
                $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
                    If $_iSearchShop <> -1 Then $sNumeroCompte &= $g_aShop[$_iSearchShop][2]
            EndIf
            $nLongueur = StringLen($sNumeroCompte)
            $sNumeroCompte &= _StringRepeat32(8 - $nLongueur)
            ExitLoop
        EndIf
    Next
    Return $sNumeroCompte
EndFunc

Func Libelle($p_sCodeMagasin, $p_nLongueur)
    Local $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
    If $_iSearchShop = -1 Then Return
    Switch $p_nLongueur
        Case "court"
            Return StringLeft($g_aShop[$_iSearchShop][1], 20)
        Case "long"
            Return StringLeft($g_aShop[$_iSearchShop][1], 30)
    EndSwitch
EndFunc

Hi Subz,

Did you change anything ?

Subz · June 21, 2018

Weren't you able to test? It should take about six seconds to run now, your earlier code took about 2 minutes to run on my system.

jerem488 · June 21, 2018

16 minutes ago, Subz said:

Weren't you able to test? It should take about six seconds to run now, your earlier code took about 2 minutes to run on my system.

It works fine and very fast ! You are a genious ! :lmao: Thanks a lot !

I'm going to see the code changes

Subz · June 21, 2018

You'll noticed that imported the entire sheet into an array and then just used this rather than reading/writing to Excel, once it finishes the loop it writes the $_oExport array to Exclusions Excel file. I also added links to your final Window so you can open the .txt and Exclusions.xlsx file directly. You may want to change the iFormat of the _Excel_BookSaveAs function when I ran through your code, I was given an error that it was incorrectly formatted, so changed it to xlsx.

jerem488 · June 21, 2018

Oh right, I saw that big mistake

Quote

I also added links to your final Window so you can open the .txt and Exclusions.xlsx file directly.

Thank you, it's a good idea.

I just have one thing that doesn't work. The writing of the file "exclusions.xls" only works for the processing of the 1st file

Subz · June 21, 2018

Is it suppose to create a new file or just append to the previous document or added as a different sheet?

jerem488 · June 21, 2018

Append to the previous document, successive

Edited June 21, 2018 by jerem488

Subz · June 21, 2018

Haven't tested fully, but should work, if you want to move row up or down, just change the $_iLastRow + 2 to something lower. If you want to remove the header then just do an _ArrayDelete($_aExport, 0) above or below line 297.

Anyway gotta go.

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <WindowsConstants.au3>
#include <WinAPIShPath.au3>
#include <FontConstants.au3>
#include <StaticConstants.au3>
#include <Debug.au3>
#include <ExcelConstants.au3>

Global $chr32_max = StringRepeat(Chr(32), 256)
Global $chr0_max = _StringRepeat("0", 12)

; https://www.autoitscript.com/forum/topic/140190-stringrepeat-very-fast-using-memset/
Func StringRepeat($sChar, $nCount)
    $tBuffer = DLLStructCreate("char[" & $nCount & "]")
    DllCall("msvcrt.dll", "ptr:cdecl", "memset", "ptr", DLLStructGetPtr($tBuffer), "int", Asc($sChar), "int", $nCount)
    Return DLLStructGetData($tBuffer, 1)
EndFunc

Func _StringRepeat32($n)
    Return StringLeft($chr32_max, $n)
EndFunc

Func _StringRepeat0($n)
    Return StringLeft($chr0_max, $n)
EndFunc

Global $g_aShop[8][3] = [["424", "CAISSE COGR POST", "0008"], _
    ["337", "CAISSE COGR HLT", "0003"], _
    ["407", "CAISSE COGR XERT", "0009"], _
    ["431", "CAISSE COGR ZERCO", "0008"], _
    ["349", "CAISSE COGR 349", "0007"], _
    ["406", "CAISSE COGR DSOG", "0009"], _
    ["385", "CAISSE COGR VILLAR", "0004"], _
    ["420", "CAISSE COGR THORX", "0005"]]

Global $g_aIndicator[31][5] = [["Mouvements de Caisse", "CA HT", "", "", ""], _
    ["Mouvements de Caisse", "CA TTC", "", "CREDIT", ""], _
    ["Mouvements de Caisse", "TVA", "", "", ""], _
    ["Modes de règlement", "Acompte hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _ ; ALERTE
    ["Modes de règlement", "American Express", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Avoir hors place", "0AV", "DEBIT", "SC"], _
    ["Modes de règlement", "Bon cadeau hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _
    ["Modes de règlement", "BEST RECORD", "0BEST" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CADHOC", "0CADH" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "HAVAS", "0HAVA" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "KADEOS", "0KADE" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Shopping Pass", "0SHOP" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "TIR GROUPE", "0TIR" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "C.B. manuelle", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CA DO Cheque Banque Postale", "0CADO" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Carte bancaire", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Carte Cadeau", "0CC", "DEBIT", "SC"], _
    ["Modes de règlement", "Carte ILLICADO", "0ILLI" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CB BRANDALLEY", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB CFAO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB REDOUTE", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB SPARTOO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB Web", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Cheque", "0CHQ" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "Reprise Acompte - P Diff", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise Acompte Réservation", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'acompte", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'avoir", "0AV", "", "SC"], _
    ["Emissions (OSCC)", "Accompte", "0COGR" & _StringRepeat32(1), "CREDIT", ""], _
    ["Emissions (OSCC)", "Avoir", "0AV", "CREDIT", "SC"], _
    ["Emissions (OSCC)", "Carte cadeau", "0CC", "CREDIT", "SC"]]

Global $g_bWriteExcelExport = False
Global $bFirstLine = True
Global $g_idFile, $g_sFileSelectFolder, $g_idSelectFolderButton, $g_idFolder, $g_idOKButton, $g_idLabelWait

Window()

Func Window()
    Local $hGUI = GUICreate("Importation", 550, 147)
    $g_idFile = GUICtrlCreateLabel("Folder containing Excel files to import :", 20, 20, 280)
        GUICtrlSetFont(-1, 10)
    $g_idSelectFolderButton = GUICtrlCreateButton("Browse", 305, 17, 80)
    $g_idFolder = GUICtrlCreateInput("", 20, 60, 510)
    $g_idOKButton = GUICtrlCreateButton("OK", 20, 105, 80)
    GUICtrlSetState($g_idOKButton, $GUI_DISABLE)
    Local $sPath = @MyDocumentsDir & "\"

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $g_idSelectFolderButton
                If FileExists(@TempDir & "\Conectis.txt") Then
                    $sPath = FileReadLine(@TempDir & "\Conectis.txt", 1)
                EndIf
                $g_sFileSelectFolder = FileSelectFolder("", $sPath)
                If Not @error Then
                    Local $sPathFile = @TempDir & "\Conectis.txt"
                    Local $g_idFileOpen = FileOpen($sPathFile, $FO_OVERWRITE)
                    FileWriteLine(@TempDir & "\Conectis.txt", $g_sFileSelectFolder)
                    GUICtrlSetData($g_idFolder, $g_sFileSelectFolder)
                    GUICtrlSetState($g_idOKButton, $GUI_ENABLE)
                EndIf
            Case $g_idOKButton
                GUICtrlSetState($g_idFile, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idFolder, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idOKButton, $GUI_HIDE)
                $g_idLabelWait = GUICtrlCreateLabel("Please wait...", 60, 52.5, 430, 25)
                GUICtrlSetFont(-1, 18)
                ListFiles($g_sFileSelectFolder)
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func ListFiles($sFolder)
    Local $aFileList = _FileListToArrayRec($sFolder, "*.xls;*.xlsx", 1)
    If @error = 1 Then Exit MsgBox(64, "", "The path is invalid")
    If @error = 4 Then Exit MsgBox(64, "", "No Excel files were found in this folder.")

    FileDelete($g_sFileSelectFolder & "\Exclusions.xlsx")
    FileDelete($g_sFileSelectFolder & "\ImportQuadra.txt")

    ; Loops the list of files
    For $i = 1 To UBound($aFileList) - 1
        If $aFileList[$i] <> "Exclusions.xlsx" Then TransposeExcel($sFolder, $aFileList[$i])
    Next
    GUICtrlDelete($g_idLabelWait)
    GUICtrlSetState($g_idFile, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idFolder, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idOKButton, $GUI_SHOW)
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = True Then Window2($g_sFileSelectFolder & "\")
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = False Then Window3($g_sFileSelectFolder & "\")
EndFunc

Func Window2($sFolder)

    Local $hGUI = GUICreate("Importation Quadra", 380, 150)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 10, 40, 100)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 220, 40, 100)
        GUICtrlSetFont($sQuadraFile, 9)
        GUICtrlSetColor($sQuadraFile, "0x4800FF")
        GUICtrlSetCursor($sQuadraFile, 0)
    Local $sExclusions = GUICtrlCreateLabel("File of exclusions to be processed:", 10, 70, 200)
        GUICtrlSetFont(-1, 9)
    Local $sExclusionsFile = GUICtrlCreateLabel("Exclusions.xlsx", 220, 70, 100)
        GUICtrlSetFont($sExclusionsFile, 9)
        GUICtrlSetColor($sExclusionsFile, "0xAE0000")
        GUICtrlSetCursor($sExclusionsFile, 0)
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 115, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Open", 285, 111.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
            Case $sQuadraFile
                ShellExecute($sFolder & "\ImportQuadra.txt")
            Case $sExclusionsFile
                ShellExecute($sFolder & "\Exclusions.xlsx")
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func Window3($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 125)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 90, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Ouvrir", 285, 86.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func TransposeExcel($sFolder, $sFile)
    Local $_iIndex = 1, $_oExport, $_iLastRow
    Local $g_idFileOpen = FileOpen($g_sFileSelectFolder & "\ImportQuadra.txt", 1)
        If $g_idFileOpen = -1 Then
            MsgBox(16, "", "Cannot create file " & $g_sFileSelectFolder & "\ImportQuadra.txt")
            Return False
        EndIf
    Local $oExcel = _Excel_Open(False, False, False, False, True)
        If @error Then Return MsgBox(4096, "Excel Error", "Unable to open Excel.")
    Local $_oImport = _Excel_BookOpen($oExcel, $sFolder & "\" & $sFile)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Opening Workbook", "Unable to open : " & $sFolder & "\" & $sFile)
        EndIf
    Local $_aImport = _Excel_RangeRead($_oImport, Default, $_oImport.ActiveSheet.UsedRange)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel RangeRead Error",  "Error reading from workbook:" & @CRLF & "Filename : " & $sFolder & "\" & $sFile & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        EndIf
        _Excel_BookClose($_oImport)

    Local $_aExport[1][6] = [["CODE MAGASIN", "JOUR", "CATEGORIE", "INDICATEUR", "DEBIT", "CREDIT"]]
    Local $_aDefault_ExportInfo[12], $_aDefault_ExportInfo
            $_aDefault_ExportInfo[0] = ""       ;~ $sLigne
            $_aDefault_ExportInfo[1] = 0        ;~ $nLongueurLigne = 0
            $_aDefault_ExportInfo[2] = "M"      ;~ $sTypeEnregistrement = "M"    ; 1st character ; Length: 1 character
            $_aDefault_ExportInfo[3] = ""       ;~ $sNumeroCompte = ""           ; 2nd character ; Length: 8 characters
            $_aDefault_ExportInfo[4] = "000"    ;~ $sLigneFolio = "000"          ; 12nd character ; Length: 3 characters
            $_aDefault_ExportInfo[5] = ""       ;~ $sDate = ""                   ; 15nd character ; Length: 6 characters
            $_aDefault_ExportInfo[6] = ""       ;~ $sLibelleCourt = ""           ; 22nd character ; Length: 20 characters
            $_aDefault_ExportInfo[7] = ""       ;~ $sLettreDebitCredit = ""      ; 23nd character ; Length: 1 character
            $_aDefault_ExportInfo[8] = ""       ;~ $sDebitCredit = ""            ; 43nd character ; Length: 13 characters
            $_aDefault_ExportInfo[9] = "EUR"    ;~ $sDevise = "EUR"              ; 108nd character ; Length: 3 characters
            $_aDefault_ExportInfo[10] = "ODC"   ;~ $sCodeJournal = "ODC"         ; 111nd character ; Length: 3 characters
            $_aDefault_ExportInfo[11] = ""      ;~ $sLibelleLong = ""            ; 117nd character ; Length: 30 characters

    For $i = 0 To UBound($_aImport) - 1
        If $_aImport[$i][0] = "CODE MAGASIN" Then ContinueLoop
        If $_aImport[$i][0] = "" Then ContinueLoop
        $_iIndex += 1
        $_aExportInfo = $_aDefault_ExportInfo
    ;~ Reformat Date
        $_aExportInfo[5] = StringFormat("%02i%02i%02i", StringMid($_aImport[$i][1], 7, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 3, 2))
        $_aImport[$i][1] = StringFormat("%2i/%2i/%4i", StringMid($_aImport[$i][1], 3, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 7, 2))
    ;~ Configure Column c Categorie - Retrieving the account number
        $_aExportInfo[3] &= NumeroCompte($_aImport[$i][2], $_aImport[$i][3], $_aImport[$i][0])
    ; ==> Retrieving the short text
        $_aExportInfo[6] &= Libelle($_aImport[$i][0], "court")
    ; ==> Retrieving the long text
        $_aExportInfo[11] &= Libelle($_aImport[$i][0], "long")
    ; ==> Retrieving the debit OR credit letter ('C' OU 'D')
        If $_aImport[$i][4] <> "" And $_aImport[$i][5] == "" Then
            If $_aImport[$i][4] <> 0 Then
                $_aExportInfo[7] = "D"
            ; ==> Recovery of debit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][4])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        Else
            If $_aImport[$i][5] <> 0 Then
                $_aExportInfo[7] = "C"
            ; ==> Recovery of credit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][5])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        EndIf
    ; ==> Complete line to write in the file
        $_aExportInfo[0] &= $_aExportInfo[2] & $_aExportInfo[3] & _StringRepeat32(2) & $_aExportInfo[4] & $_aExportInfo[5] & _StringRepeat32(1) & _
        $_aExportInfo[6] & $_aExportInfo[7] & $_aExportInfo[8] & _StringRepeat32(52) & $_aExportInfo[9] & $_aExportInfo[10] & _StringRepeat32(3) & $_aExportInfo[11]
        $_aExportInfo[1] = StringLen($_aExportInfo[0])
        $_aExportInfo[0] &= _StringRepeat32(256 - $_aExportInfo[1])
    ; ==> Saving the results of the processed file
        If StringIsSpace($_aExportInfo[3]) Or $_aExportInfo[3] == "Acompte hors place" Or $_aExportInfo[3] == "Avoir hors place" Or $_aExportInfo[8] == "ZERO" Then
            _ArrayAdd($_aExport, _ArrayToString($_aImport, "|", $i, $i))
            ContinueLoop
        Else
            FileWriteLine($g_sFileSelectFolder & "\ImportQuadra.txt", $_aExportInfo[0])
        EndIf
    Next
    Local $_bFileExists = FileExists($g_sFileSelectFolder & "\Exclusions.xlsx")
    If $_bFileExists Then
        $_oExport = _Excel_BookOpen($oExcel, $g_sFileSelectFolder & "\Exclusions.xlsx")
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Error Opening Workbook", "Unable to open: " & $g_sFileSelectFolder & "\Exclusions.xlsx")
        EndIf
        $_iLastRow = $_oExport.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row + 2
    Else
        $_oExport = _Excel_BookNew($oExcel)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Create Workbook", "Unable to create new Workbook")
        EndIf
        $_iLastRow = 1
    EndIf
    _Excel_RangeWrite($_oExport, $_oExport.ActiveSheet, $_aExport, "A" & $_iLastRow)
    If $_bFileExists Then
        _Excel_BookSave($_oExport)
        If @error Then $g_bWriteExcelExport = False
    Else
        _Excel_BookSaveAs($_oExport, $g_sFileSelectFolder & "\Exclusions.xlsx", $xlWorkbookDefault)
        If @error Then $g_bWriteExcelExport = False
    Endif
    $g_bWriteExcelExport = True
    _Excel_BookClose($_oExport)
    _Excel_Close($oExcel)
EndFunc

Func Formatage($sCellule)
    ; ==> Function that formats in 12 characters the debit & credit columns of the rows
    Local $sMontant = ""
    If IsNumber($sCellule) Then
        ; ==> Any + and - characters that may be present are deleted
        $sCellule = StringRegExpReplace($sCellule, "[^\d\.]", "")

        Local $PositionPoint = StringInStr($sCellule, ".", 1)
        Local $sDecimales = ""
        If $PositionPoint Then ; There's a point
            $sDecimales = StringTrimLeft($sCellule, $PositionPoint)
            $sNumber = StringLeft($sCellule, $PositionPoint - 1)
            If StringLen($sDecimales) < 2 Then
                $sDecimales &= "0"
            EndIf
            $sMontant &= $sNumber & $sDecimales
            $nLongueur = StringLen($sMontant)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sMontant
        Else ; There is no point
            $sDecimales &= $sCellule & "00"
            $nLongueur = StringLen($sDecimales)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sDecimales
        EndIf
    EndIf
    Return $sMontant
EndFunc

Func NumeroCompte($p_sCategorie, $p_sIndicateur, $p_sCodeMagasin)
    Local $_iSearchShop
    Local $_iFindIndicator = _ArrayFindAll($g_aIndicator, $p_sCategorie, 0, 0, 0, 2, 0)
    Local $sNumeroCompte = "", $sSCompte = ""
    For $i = 0 To UBound($_iFindIndicator) - 1
        If $p_sIndicateur == $g_aIndicator[$_iFindIndicator[$i]][1] Then
            $sNumeroCompte &= $g_aIndicator[$_iFindIndicator[$i]][2]
            If $g_aIndicator[$_iFindIndicator[$i]][1] == "Acompte hors place" Then
                $sNumeroCompte = "Acompte hors place"
                ExitLoop
            ElseIf $g_aIndicator[$_iFindIndicator[$i]][1] == "Avoir hors place" Then
                $sNumeroCompte = "Avoir hors place"
                ExitLoop
            EndIf
            If $g_aIndicator[$_iFindIndicator[$i]][4] == "SC" Then
                $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
                    If $_iSearchShop <> -1 Then $sNumeroCompte &= $g_aShop[$_iSearchShop][2]
            EndIf
            $nLongueur = StringLen($sNumeroCompte)
            $sNumeroCompte &= _StringRepeat32(8 - $nLongueur)
            ExitLoop
        EndIf
    Next
    Return $sNumeroCompte
EndFunc

Func Libelle($p_sCodeMagasin, $p_nLongueur)
    Local $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
    If $_iSearchShop = -1 Then Return
    Switch $p_nLongueur
        Case "court"
            Return StringLeft($g_aShop[$_iSearchShop][1], 20)
        Case "long"
            Return StringLeft($g_aShop[$_iSearchShop][1], 30)
    EndSwitch
EndFunc

jerem488 · June 22, 2018

10 hours ago, Subz said:

Haven't tested fully, but should work, if you want to move row up or down, just change the $_iLastRow + 2 to something lower. If you want to remove the header then just do an _ArrayDelete($_aExport, 0) above or below line 297.

Anyway gotta go.

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <WindowsConstants.au3>
#include <WinAPIShPath.au3>
#include <FontConstants.au3>
#include <StaticConstants.au3>
#include <Debug.au3>
#include <ExcelConstants.au3>

Global $chr32_max = StringRepeat(Chr(32), 256)
Global $chr0_max = _StringRepeat("0", 12)

; https://www.autoitscript.com/forum/topic/140190-stringrepeat-very-fast-using-memset/
Func StringRepeat($sChar, $nCount)
    $tBuffer = DLLStructCreate("char[" & $nCount & "]")
    DllCall("msvcrt.dll", "ptr:cdecl", "memset", "ptr", DLLStructGetPtr($tBuffer), "int", Asc($sChar), "int", $nCount)
    Return DLLStructGetData($tBuffer, 1)
EndFunc

Func _StringRepeat32($n)
    Return StringLeft($chr32_max, $n)
EndFunc

Func _StringRepeat0($n)
    Return StringLeft($chr0_max, $n)
EndFunc

Global $g_aShop[8][3] = [["424", "CAISSE COGR POST", "0008"], _
    ["337", "CAISSE COGR HLT", "0003"], _
    ["407", "CAISSE COGR XERT", "0009"], _
    ["431", "CAISSE COGR ZERCO", "0008"], _
    ["349", "CAISSE COGR 349", "0007"], _
    ["406", "CAISSE COGR DSOG", "0009"], _
    ["385", "CAISSE COGR VILLAR", "0004"], _
    ["420", "CAISSE COGR THORX", "0005"]]

Global $g_aIndicator[31][5] = [["Mouvements de Caisse", "CA HT", "", "", ""], _
    ["Mouvements de Caisse", "CA TTC", "", "CREDIT", ""], _
    ["Mouvements de Caisse", "TVA", "", "", ""], _
    ["Modes de règlement", "Acompte hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _ ; ALERTE
    ["Modes de règlement", "American Express", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Avoir hors place", "0AV", "DEBIT", "SC"], _
    ["Modes de règlement", "Bon cadeau hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _
    ["Modes de règlement", "BEST RECORD", "0BEST" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CADHOC", "0CADH" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "HAVAS", "0HAVA" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "KADEOS", "0KADE" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Shopping Pass", "0SHOP" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "TIR GROUPE", "0TIR" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "C.B. manuelle", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CA DO Cheque Banque Postale", "0CADO" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Carte bancaire", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Carte Cadeau", "0CC", "DEBIT", "SC"], _
    ["Modes de règlement", "Carte ILLICADO", "0ILLI" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CB BRANDALLEY", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB CFAO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB REDOUTE", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB SPARTOO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB Web", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Cheque", "0CHQ" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "Reprise Acompte - P Diff", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise Acompte Réservation", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'acompte", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'avoir", "0AV", "", "SC"], _
    ["Emissions (OSCC)", "Accompte", "0COGR" & _StringRepeat32(1), "CREDIT", ""], _
    ["Emissions (OSCC)", "Avoir", "0AV", "CREDIT", "SC"], _
    ["Emissions (OSCC)", "Carte cadeau", "0CC", "CREDIT", "SC"]]

Global $g_bWriteExcelExport = False
Global $bFirstLine = True
Global $g_idFile, $g_sFileSelectFolder, $g_idSelectFolderButton, $g_idFolder, $g_idOKButton, $g_idLabelWait

Window()

Func Window()
    Local $hGUI = GUICreate("Importation", 550, 147)
    $g_idFile = GUICtrlCreateLabel("Folder containing Excel files to import :", 20, 20, 280)
        GUICtrlSetFont(-1, 10)
    $g_idSelectFolderButton = GUICtrlCreateButton("Browse", 305, 17, 80)
    $g_idFolder = GUICtrlCreateInput("", 20, 60, 510)
    $g_idOKButton = GUICtrlCreateButton("OK", 20, 105, 80)
    GUICtrlSetState($g_idOKButton, $GUI_DISABLE)
    Local $sPath = @MyDocumentsDir & "\"

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $g_idSelectFolderButton
                If FileExists(@TempDir & "\Conectis.txt") Then
                    $sPath = FileReadLine(@TempDir & "\Conectis.txt", 1)
                EndIf
                $g_sFileSelectFolder = FileSelectFolder("", $sPath)
                If Not @error Then
                    Local $sPathFile = @TempDir & "\Conectis.txt"
                    Local $g_idFileOpen = FileOpen($sPathFile, $FO_OVERWRITE)
                    FileWriteLine(@TempDir & "\Conectis.txt", $g_sFileSelectFolder)
                    GUICtrlSetData($g_idFolder, $g_sFileSelectFolder)
                    GUICtrlSetState($g_idOKButton, $GUI_ENABLE)
                EndIf
            Case $g_idOKButton
                GUICtrlSetState($g_idFile, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idFolder, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idOKButton, $GUI_HIDE)
                $g_idLabelWait = GUICtrlCreateLabel("Please wait...", 60, 52.5, 430, 25)
                GUICtrlSetFont(-1, 18)
                ListFiles($g_sFileSelectFolder)
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func ListFiles($sFolder)
    Local $aFileList = _FileListToArrayRec($sFolder, "*.xls;*.xlsx", 1)
    If @error = 1 Then Exit MsgBox(64, "", "The path is invalid")
    If @error = 4 Then Exit MsgBox(64, "", "No Excel files were found in this folder.")

    FileDelete($g_sFileSelectFolder & "\Exclusions.xlsx")
    FileDelete($g_sFileSelectFolder & "\ImportQuadra.txt")

    ; Loops the list of files
    For $i = 1 To UBound($aFileList) - 1
        If $aFileList[$i] <> "Exclusions.xlsx" Then TransposeExcel($sFolder, $aFileList[$i])
    Next
    GUICtrlDelete($g_idLabelWait)
    GUICtrlSetState($g_idFile, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idFolder, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idOKButton, $GUI_SHOW)
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = True Then Window2($g_sFileSelectFolder & "\")
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = False Then Window3($g_sFileSelectFolder & "\")
EndFunc

Func Window2($sFolder)

    Local $hGUI = GUICreate("Importation Quadra", 380, 150)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 10, 40, 100)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 220, 40, 100)
        GUICtrlSetFont($sQuadraFile, 9)
        GUICtrlSetColor($sQuadraFile, "0x4800FF")
        GUICtrlSetCursor($sQuadraFile, 0)
    Local $sExclusions = GUICtrlCreateLabel("File of exclusions to be processed:", 10, 70, 200)
        GUICtrlSetFont(-1, 9)
    Local $sExclusionsFile = GUICtrlCreateLabel("Exclusions.xlsx", 220, 70, 100)
        GUICtrlSetFont($sExclusionsFile, 9)
        GUICtrlSetColor($sExclusionsFile, "0xAE0000")
        GUICtrlSetCursor($sExclusionsFile, 0)
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 115, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Open", 285, 111.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
            Case $sQuadraFile
                ShellExecute($sFolder & "\ImportQuadra.txt")
            Case $sExclusionsFile
                ShellExecute($sFolder & "\Exclusions.xlsx")
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func Window3($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 125)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 90, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Ouvrir", 285, 86.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func TransposeExcel($sFolder, $sFile)
    Local $_iIndex = 1, $_oExport, $_iLastRow
    Local $g_idFileOpen = FileOpen($g_sFileSelectFolder & "\ImportQuadra.txt", 1)
        If $g_idFileOpen = -1 Then
            MsgBox(16, "", "Cannot create file " & $g_sFileSelectFolder & "\ImportQuadra.txt")
            Return False
        EndIf
    Local $oExcel = _Excel_Open(False, False, False, False, True)
        If @error Then Return MsgBox(4096, "Excel Error", "Unable to open Excel.")
    Local $_oImport = _Excel_BookOpen($oExcel, $sFolder & "\" & $sFile)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Opening Workbook", "Unable to open : " & $sFolder & "\" & $sFile)
        EndIf
    Local $_aImport = _Excel_RangeRead($_oImport, Default, $_oImport.ActiveSheet.UsedRange)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel RangeRead Error",  "Error reading from workbook:" & @CRLF & "Filename : " & $sFolder & "\" & $sFile & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        EndIf
        _Excel_BookClose($_oImport)

    Local $_aExport[1][6] = [["CODE MAGASIN", "JOUR", "CATEGORIE", "INDICATEUR", "DEBIT", "CREDIT"]]
    Local $_aDefault_ExportInfo[12], $_aDefault_ExportInfo
            $_aDefault_ExportInfo[0] = ""       ;~ $sLigne
            $_aDefault_ExportInfo[1] = 0        ;~ $nLongueurLigne = 0
            $_aDefault_ExportInfo[2] = "M"      ;~ $sTypeEnregistrement = "M"    ; 1st character ; Length: 1 character
            $_aDefault_ExportInfo[3] = ""       ;~ $sNumeroCompte = ""           ; 2nd character ; Length: 8 characters
            $_aDefault_ExportInfo[4] = "000"    ;~ $sLigneFolio = "000"          ; 12nd character ; Length: 3 characters
            $_aDefault_ExportInfo[5] = ""       ;~ $sDate = ""                   ; 15nd character ; Length: 6 characters
            $_aDefault_ExportInfo[6] = ""       ;~ $sLibelleCourt = ""           ; 22nd character ; Length: 20 characters
            $_aDefault_ExportInfo[7] = ""       ;~ $sLettreDebitCredit = ""      ; 23nd character ; Length: 1 character
            $_aDefault_ExportInfo[8] = ""       ;~ $sDebitCredit = ""            ; 43nd character ; Length: 13 characters
            $_aDefault_ExportInfo[9] = "EUR"    ;~ $sDevise = "EUR"              ; 108nd character ; Length: 3 characters
            $_aDefault_ExportInfo[10] = "ODC"   ;~ $sCodeJournal = "ODC"         ; 111nd character ; Length: 3 characters
            $_aDefault_ExportInfo[11] = ""      ;~ $sLibelleLong = ""            ; 117nd character ; Length: 30 characters

    For $i = 0 To UBound($_aImport) - 1
        If $_aImport[$i][0] = "CODE MAGASIN" Then ContinueLoop
        If $_aImport[$i][0] = "" Then ContinueLoop
        $_iIndex += 1
        $_aExportInfo = $_aDefault_ExportInfo
    ;~ Reformat Date
        $_aExportInfo[5] = StringFormat("%02i%02i%02i", StringMid($_aImport[$i][1], 7, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 3, 2))
        $_aImport[$i][1] = StringFormat("%2i/%2i/%4i", StringMid($_aImport[$i][1], 3, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 7, 2))
    ;~ Configure Column c Categorie - Retrieving the account number
        $_aExportInfo[3] &= NumeroCompte($_aImport[$i][2], $_aImport[$i][3], $_aImport[$i][0])
    ; ==> Retrieving the short text
        $_aExportInfo[6] &= Libelle($_aImport[$i][0], "court")
    ; ==> Retrieving the long text
        $_aExportInfo[11] &= Libelle($_aImport[$i][0], "long")
    ; ==> Retrieving the debit OR credit letter ('C' OU 'D')
        If $_aImport[$i][4] <> "" And $_aImport[$i][5] == "" Then
            If $_aImport[$i][4] <> 0 Then
                $_aExportInfo[7] = "D"
            ; ==> Recovery of debit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][4])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        Else
            If $_aImport[$i][5] <> 0 Then
                $_aExportInfo[7] = "C"
            ; ==> Recovery of credit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][5])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        EndIf
    ; ==> Complete line to write in the file
        $_aExportInfo[0] &= $_aExportInfo[2] & $_aExportInfo[3] & _StringRepeat32(2) & $_aExportInfo[4] & $_aExportInfo[5] & _StringRepeat32(1) & _
        $_aExportInfo[6] & $_aExportInfo[7] & $_aExportInfo[8] & _StringRepeat32(52) & $_aExportInfo[9] & $_aExportInfo[10] & _StringRepeat32(3) & $_aExportInfo[11]
        $_aExportInfo[1] = StringLen($_aExportInfo[0])
        $_aExportInfo[0] &= _StringRepeat32(256 - $_aExportInfo[1])
    ; ==> Saving the results of the processed file
        If StringIsSpace($_aExportInfo[3]) Or $_aExportInfo[3] == "Acompte hors place" Or $_aExportInfo[3] == "Avoir hors place" Or $_aExportInfo[8] == "ZERO" Then
            _ArrayAdd($_aExport, _ArrayToString($_aImport, "|", $i, $i))
            ContinueLoop
        Else
            FileWriteLine($g_sFileSelectFolder & "\ImportQuadra.txt", $_aExportInfo[0])
        EndIf
    Next
    Local $_bFileExists = FileExists($g_sFileSelectFolder & "\Exclusions.xlsx")
    If $_bFileExists Then
        $_oExport = _Excel_BookOpen($oExcel, $g_sFileSelectFolder & "\Exclusions.xlsx")
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Error Opening Workbook", "Unable to open: " & $g_sFileSelectFolder & "\Exclusions.xlsx")
        EndIf
        $_iLastRow = $_oExport.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row + 2
    Else
        $_oExport = _Excel_BookNew($oExcel)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Create Workbook", "Unable to create new Workbook")
        EndIf
        $_iLastRow = 1
    EndIf
    _Excel_RangeWrite($_oExport, $_oExport.ActiveSheet, $_aExport, "A" & $_iLastRow)
    If $_bFileExists Then
        _Excel_BookSave($_oExport)
        If @error Then $g_bWriteExcelExport = False
    Else
        _Excel_BookSaveAs($_oExport, $g_sFileSelectFolder & "\Exclusions.xlsx", $xlWorkbookDefault)
        If @error Then $g_bWriteExcelExport = False
    Endif
    $g_bWriteExcelExport = True
    _Excel_BookClose($_oExport)
    _Excel_Close($oExcel)
EndFunc

Func Formatage($sCellule)
    ; ==> Function that formats in 12 characters the debit & credit columns of the rows
    Local $sMontant = ""
    If IsNumber($sCellule) Then
        ; ==> Any + and - characters that may be present are deleted
        $sCellule = StringRegExpReplace($sCellule, "[^\d\.]", "")

        Local $PositionPoint = StringInStr($sCellule, ".", 1)
        Local $sDecimales = ""
        If $PositionPoint Then ; There's a point
            $sDecimales = StringTrimLeft($sCellule, $PositionPoint)
            $sNumber = StringLeft($sCellule, $PositionPoint - 1)
            If StringLen($sDecimales) < 2 Then
                $sDecimales &= "0"
            EndIf
            $sMontant &= $sNumber & $sDecimales
            $nLongueur = StringLen($sMontant)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sMontant
        Else ; There is no point
            $sDecimales &= $sCellule & "00"
            $nLongueur = StringLen($sDecimales)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sDecimales
        EndIf
    EndIf
    Return $sMontant
EndFunc

Func NumeroCompte($p_sCategorie, $p_sIndicateur, $p_sCodeMagasin)
    Local $_iSearchShop
    Local $_iFindIndicator = _ArrayFindAll($g_aIndicator, $p_sCategorie, 0, 0, 0, 2, 0)
    Local $sNumeroCompte = "", $sSCompte = ""
    For $i = 0 To UBound($_iFindIndicator) - 1
        If $p_sIndicateur == $g_aIndicator[$_iFindIndicator[$i]][1] Then
            $sNumeroCompte &= $g_aIndicator[$_iFindIndicator[$i]][2]
            If $g_aIndicator[$_iFindIndicator[$i]][1] == "Acompte hors place" Then
                $sNumeroCompte = "Acompte hors place"
                ExitLoop
            ElseIf $g_aIndicator[$_iFindIndicator[$i]][1] == "Avoir hors place" Then
                $sNumeroCompte = "Avoir hors place"
                ExitLoop
            EndIf
            If $g_aIndicator[$_iFindIndicator[$i]][4] == "SC" Then
                $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
                    If $_iSearchShop <> -1 Then $sNumeroCompte &= $g_aShop[$_iSearchShop][2]
            EndIf
            $nLongueur = StringLen($sNumeroCompte)
            $sNumeroCompte &= _StringRepeat32(8 - $nLongueur)
            ExitLoop
        EndIf
    Next
    Return $sNumeroCompte
EndFunc

Func Libelle($p_sCodeMagasin, $p_nLongueur)
    Local $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
    If $_iSearchShop = -1 Then Return
    Switch $p_nLongueur
        Case "court"
            Return StringLeft($g_aShop[$_iSearchShop][1], 20)
        Case "long"
            Return StringLeft($g_aShop[$_iSearchShop][1], 30)
    EndSwitch
EndFunc

It works fine

The "$_iIndex" variable is useless?

And where can I replace points ".", with a decimal point "," when we save in the exclusions.xlsx file only ? (Colums "DEBIT" and "CREDIT")

Subz · June 22, 2018

When I started I was trying to follow your functions so there are bound to be entries that shouldn't be there, as I mentioned above it was rather rough optimisation as I didn't have time to debug. So feel free to remove "$_iIndex", with regards to changing decimals, you want to change decimal point "." to a comma? Remember that $_aImport is just an array of the "Extraction.xls" file or the ones being processed. So below "; ==> Complete line to write in the file" just add the following, remember the "If" statement above uses both $_aImport[$i][4] and $_aImport[$i][5] to update $_aExportInfo[$i][8] so if you move the lines anywhere above $_aExportInfo[$i][8] would return incorrect data.

;~ Replace Decimals
        $_aImport[$i][4] = StringReplace($_aImport[$i][4], ".", ",")
        $_aImport[$i][5] = StringReplace($_aImport[$i][5], ".", ",")
    ; ==> Complete line to write in the file

jerem488 · June 22, 2018

Yes, I had just done it!

Thanks for the quickly response

Optimize the code

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members