Manipulation de fichiers sous VBA

Bonjour,

J'ai réalisé une macro qui permet de mettre à jour des fichiers via d'autres fichiers et d'écraser les données présentes.

L'objectif au lieu d'écraser les données serait de créer les fichiers, y insérer les données et les enregistrer (les fichiers fn_traite), les chemins d'enregistrement sont précisés dans la feuille "param" cells(12,2 ; 13,2; 14,2; 16,2; 17,2) et les noms des fichiers dans la colonne précédente cells(12,1 ; 13,1; 14,1; 16,1; 17,1).

En vous souhaitant à tous une très belle journée :)

Merci.

Function fill_courbe_refi(fn_dest As String, fs_dest As String, fn_src As String, fs_src As String, dateApplication As Long, col As String)

    Dim i, lastLine As Long

    lastLine = Workbooks(fn_dest).Sheets(fs_dest).Range("A" & lineMax).End(xlUp).Row - nbSpread1 + 1

    For i = 0 To 7 's'execute 8 fois

        Workbooks(fn_dest).Sheets(fs_dest).Range("A" & (lastLine + nbSpread1 * i) & ":A" & (lastLine + nbSpread1 * (i + 1) - 1)).value = dateApplication + i

        Workbooks(fn_dest).Sheets(fs_dest).Range("B" & (lastLine + nbSpread1 * i) & ":B" & (lastLine + nbSpread1 * (i + 1) - 1)).value = Workbooks(fn_src).Sheets(fs_src).Range("A10:A47").value

        Workbooks(fn_dest).Sheets(fs_dest).Range("C" & (lastLine + nbSpread1 * i) & ":C" & (lastLine + nbSpread1 * (i + 1) - 1)).value = Workbooks(fn_src).Sheets(fs_src).Range(col & "10:" & col & "47").value
    Next

End Function

Sub maj_courbe_refi()

' Initialisation de l'assistant

    Dim i As Integer, lineLog As Integer, numColonne As Integer

    Dim dateApplication As Long
    Dim semaine As String

    Dim lastLineFiliale, lastLineBei, lastLinePubl, lastLinePriv, lastLineLocindus As Long
    Dim lastLine As Long, lastCol As Long

    Dim fd_evolution_spread As String, fd_maquette As String, fd_courbe_refi As String
    Dim fd_traite_filiale As String, fd_traite_scf_cff As String, fd_traite_specifique As String, fd_traite_bei As String
    Dim fd_historique_spread As String

    Dim fn_evolution_spread As String, fn_maquette As String, fn_courbe_refi As String
    Dim fn_traite_filiale As String, fn_traite_scf_cff As String, fn_traite_specifique As String, fn_traite_bei As String
    Dim fn_historique_spread As Strin
    lineLog = 1

    semaine = ThisWorkbook.Sheets("param").Cells(1, 4).value
    dateApplication = ThisWorkbook.Sheets("param").Cells(3, 2).value

    fd_evolution_spread = ThisWorkbook.Sheets("param").Cells(10, 2).value
    fd_maquette = ThisWorkbook.Sheets("param").Cells(11, 2).value
    fd_traite_filiale = ThisWorkbook.Sheets("param").Cells(12, 2).value
    fd_traite_scf_cff = ThisWorkbook.Sheets("param").Cells(13, 2).value
    fd_traite_specifique = ThisWorkbook.Sheets("param").Cells(14, 2).value
    fd_topage = ThisWorkbook.Sheets("param").Cells(15, 2).value
    fd_traite_E6M = ThisWorkbook.Sheets("param").Cells(16, 2).value
    fd_traite_E3M = ThisWorkbook.Sheets("param").Cells(17, 2).value

    fn_evolution_spread = ThisWorkbook.Sheets("param").Cells(10, 1).value
    fn_maquette = ThisWorkbook.Sheets("param").Cells(11, 1).value
    fn_traite_filiale = ThisWorkbook.Sheets("param").Cells(12, 1).value
    fn_traite_scf_cff = ThisWorkbook.Sheets("param").Cells(13, 1).value
    fn_traite_specifique = ThisWorkbook.Sheets("param").Cells(14, 1).value
    fn_topage = ThisWorkbook.Sheets("param").Cells(15, 1).value
    fn_traite_E6M = ThisWorkbook.Sheets("param").Cells(16, 1).value
    fn_traite_E3M = ThisWorkbook.Sheets("param").Cells(17, 1).value

    lastLineFiliale = Cells(6, 2).value
    lastLineBei = Cells(6, 3).value
    lastLinePubl = Cells(6, 4).value
    lastLinePriv = Cells(6, 5).value
    lastLineLocindus = Cells(6, 6).value

    Sheets("logs").Select
    Columns("A:B").Select
    Selection.ClearContents

    Workbooks.Open Filename:=fd_evolution_spread
    lineLog = ecrireLogs(1, "Ouverture du fichier : " & fd_evolution_spread, lineLog)

    Workbooks.Open Filename:=fd_maquette
    lineLog = ecrireLogs(1, "Ouverture du fichier : " & fd_maquette, lineLog)

    Workbooks.Open Filename:=fd_courbe_refi
    lineLog = ecrireLogs(1, "Ouverture du fichier : " & fd_courbe_refi, lineLog)

    Workbooks.Open Filename:=fd_topage
    lineLog = ecrireLogs(1, "Ouverture du fichier : " & fd_topage, lineLog)

    Workbooks(fn_maquette).Sheets("Données").Range("A2:S36").value = Workbooks(fn_evolution_spread).Sheets("Données").Range("A2:S36").value

    lineLog = ecrireLogs(1, "Mise à jour de l'onglet données de " & fn_maquette, lineLog)

    Workbooks(fn_maquette).Sheets("auto").Range("B1").value = dateApplication
    Workbooks(fn_maquette).Sheets("auto").Range("B1").value = dateApplication

    lineLog = ecrireLogs(1, "Mise à jour de la date d'application dans " & fn_maquette, lineLog)

    Call fill_courbe_refi(fn_courbe_refi, "Filiales", fn_maquette, "auto", dateApplication, "B")

    lineLog = ecrireLogs(1, "Mise à jour des spreads filiales dans " & fn_courbe_refi, lineLog)

    Call fill_courbe_refi(fn_courbe_refi, "Corp_Public", fn_maquette, "auto", dateApplication, "D")

    Call fill_courbe_refi(fn_courbe_refi, "Corp_Privé", fn_maquette, "auto", dateApplication, "D")

    lastLine = Workbooks(fn_courbe_refi).Sheets("Corp_Public").Range("D" & lineMax).End(xlUp).Row - nbSpread1 + 1

    For i = 0 To 7 's'execute 8 fois pour

        Workbooks(fn_courbe_refi).Sheets("Corp_Public").Range("E" & (lastLine + nbSpread1 * i) & ":F" & (lastLine + nbSpread1 * (i + 1) - 1)).value = dateApplication

        Workbooks(fn_courbe_refi).Sheets("Corp_Public").Range("D" & (lastLine + nbSpread1 * i) & ":D" & (lastLine + nbSpread1 * (i + 1) - 1)).value = Workbooks(fn_maquette).Sheets("auto").Range("C10:C47").value
    Next

    lastLine = Workbooks(fn_courbe_refi).Sheets("Corp_Privé").Range("D" & lineMax).End(xlUp).Row - nbSpread1 + 1

    For i = 0 To 7 's'execute 8 fois pour les opérations privées

        Workbooks(fn_courbe_refi).Sheets("Corp_Privé").Range("E" & (lastLine + nbSpread1 * i) & ":F" & (lastLine + nbSpread1 * (i + 1) - 1)).value = dateApplication

        Workbooks(fn_courbe_refi).Sheets("Corp_Privé").Range("D" & (lastLine + nbSpread1 * i) & ":D" & (lastLine + nbSpread1 * (i + 1) - 1)).value = Workbooks(fn_maquette).Sheets("auto").Range("C10:C47").value
    Next

    Call fill_courbe_refi(fn_courbe_refi, "Locindus", fn_maquette, "auto", dateApplication, "E")

    Call fill_courbe_refi(fn_courbe_refi, "BEI", fn_maquette, "auto", dateApplication, "F")

    Workbooks.Open Filename:=fd_traite_filiale
    lineLog = ecrireLogs(1, "Ouverture du fichier : " & fd_traite_filiale, lineLog)

    Workbooks.Open Filename:=fd_traite_scf_cff
    lineLog = ecrireLogs(1, "Ouverture du fichier : " & fd_traite_scf_cff, lineLog)

    Workbooks.Open Filename:=fd_traite_specifique
    lineLog = ecrireLogs(1, "Ouverture du fichier : " & fd_traite_specifique, lineLog)

    Workbooks.Open Filename:=fd_traite_bei
    lineLog = ecrireLogs(1, "Ouverture du fichier : " & fd_traite_bei, lineLog)

    Workbooks.Open Filename:=fd_traite_E6M
    lineLog = ecrireLogs(1, "Ouverture du fichier : " & fd_traite_E6M, lineLog)

    Workbooks.Open Filename:=fd_traite_E3M
    lineLog = ecrireLogs(1, "Ouverture du fichier : " & fd_traite_E3M, lineLog)

    Workbooks(fn_traite_filiale).Sheets("Corp_Spread_Filiale").Range("A2:A51").value = dateApplication
    Workbooks(fn_traite_filiale).Sheets("Corp_Spread_Filiale").Range("B2:B51").value = 2958465
    Workbooks(fn_traite_filiale).Sheets("Corp_Spread_Filiale").Range("D2:D51").value = Workbooks(fn_maquette).Sheets("auto").Range("B13:B62").value

    Workbooks(fn_traite_scf_cff).Sheets("Corp_Spread_SCF_CFF").Range("A2:A51").value = dateApplication
    Workbooks(fn_traite_scf_cff).Sheets("Corp_Spread_SCF_CFF").Range("B2:B51").value = 2958465
    Workbooks(fn_traite_scf_cff).Sheets("Corp_Spread_SCF_CFF").Range("D2:E51").value = Workbooks(fn_maquette).Sheets("auto").Range("C13:C62").value
    Workbooks(fn_traite_scf_cff).Sheets("Corp_Spread_SCF_CFF").Range("F2:I51").value = Workbooks(fn_maquette).Sheets("auto").Range("D13:D62").value

    Workbooks(fn_traite_specifique).Sheets("Corp_Spread_Specifique").Range("A2:A51").value = dateApplication
    Workbooks(fn_traite_specifique).Sheets("Corp_Spread_Specifique").Range("B2:B51").value = 2958465
    Workbooks(fn_traite_specifique).Sheets("Corp_Spread_Specifique").Range("D2:D51").value = Workbooks(fn_maquette).Sheets("auto").Range("E13:E62").value

    Workbooks(fn_traite_bei).Sheets("BEI").Range("A2:A51").value = dateApplication
    Workbooks(fn_traite_bei).Sheets("BEI").Range("B2:B51").value = 2958465

    Workbooks(fn_traite_E6M).Sheets("CORP_TAUX_ZC").Range("A2:A25").value = dateApplication
    Workbooks(fn_traite_E6M).Sheets("CORP_TAUX_ZC").Range("B2:B25").value = 2958465
    Workbooks(fn_traite_E6M).Sheets("CORP_TAUX_ZC").Range("D2:I25").value = Workbooks(fn_topage).Sheets("Feuil1").Range("C3:H26").value

    Workbooks(fn_traite_E3M).Sheets("CORP_TAUX_ZC_E3M").Range("A2:A25").value = dateApplication
    Workbooks(fn_traite_E3M).Sheets("CORP_TAUX_ZC_E3M").Range("B2:B25").value = 2958465
    Workbooks(fn_traite_E3M).Sheets("CORP_TAUX_ZC_E3M").Range("D2:I25").value = Workbooks(fn_topage).Sheets("Feuil1").Range("K3:P26").value

End sub