1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197
| Set xlApp = CreateObject("Excel.application")
xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Open(Application.CurrentProject.Path & "\Etats_Analyse.XLS")
nf = 1
Set xlSheet = xlBook.Worksheets("PARAM")
'-----------------------------------------------------------------------------------------------------------------
'------------------------------ DU FEUILLET PARAM POUR GESTION HABILITATION --------------------------------------
'-----------------------------------------------------------------------------------------------------------------
Dim rv As Recordset
Set rv = db.OpenRecordset("R100", dbOpenDynaset)
rv.FindFirst "[Gestionnaire]='" & Serv & "'"
rg = 1
Do Until rv.NoMatch
rg = rg + 1
xlApp.Sheets("PARAM").Range("A" & rg) = Serv
xlApp.Sheets("PARAM").Range("B" & rg) = rv.Fields("User")
xlApp.Sheets("PARAM").Range("C" & rg) = rv.Fields("NomPrenom")
rv.FindNext "[gestionnaire]='" & Serv & "'"
Loop
rv.Close
If rg = 1 Then
XL_Recap = "ATTENTION tables T_Agents et T_Gestion_Visa incomplètes : Service " & Serv & " sans VISA USER"
Else
XL_Recap = ""
End If
'-----------------------------------------------------------------------------------------------------------------
'-------------------------------- OUVERTURE REQUETE RECAP PAR SERVICE ----------------------------------------
'-----------------------------------------------------------------------------------------------------------------
Set r01 = db.OpenRecordset("RM508", dbOpenDynaset)
critere1 = "[Periode]=" & Chr(39) & AM & Chr(39) & " and [service]=" & Chr(39) & Serv & Chr(39)
r01.FindFirst critere1
'-----------------------------------------------------------------------------------------------------------------
'-------------------------------- OUVERTURE REQUETE DETAIL DES COMPTES ---------------------------------------
'-----------------------------------------------------------------------------------------------------------------
Set r04 = db.OpenRecordset("RM411", dbOpenDynaset)
critere1 = "[Periode]=" & Chr(39) & AM & Chr(39) & " and [service]=" & Chr(39) & Serv & Chr(39)
r04.FindFirst critere1
'-----------------------------------------------------------------------------------------------------------------
'-------------------------------- INITIALISATION DES VARIABLES -----------------------------------------------
'-----------------------------------------------------------------------------------------------------------------
lu = 0
rg = 10
rl = 10
entete_zero = 0
entete_liste = 0
entete_auto = 0
entete_67 = 0
Do Until r04.NoMatch
If entete_liste = 0 Then
'inscrit sur la liste des comptes du service
nomModele = Application.CurrentProject.Path & "\LISTE.xlt"
xlBook.Sheets.Add after:=xlBook.Sheets(nf), Type:=nomModele
nf = nf + 1
' ---- Masque la feuille PARAM vu que la liste est présente
xlBook.Sheets("PARAM").Visible = False
xlBook.Sheets("LISTE").Cells(4, 2).Value = r01.Fields("Libelle_long")
xlBook.Sheets("LISTE").Cells(4, 6).Value = r01.Fields("Service")
xlBook.Sheets("LISTE").Cells(6, 3).Value = Transco_Date(AM)
xlBook.Sheets("LISTE").Cells(15, 1).Value = r01.Fields("User_Responsable")
xlBook.Sheets("LISTE").Cells(20, 1).Value = r01.Fields("Tel_Rsp")
xlBook.Sheets("LISTE").Cells(15, 5).Value = r01.Fields("User_Resp_Acti")
xlBook.Sheets("LISTE").Cells(20, 5).Value = r01.Fields("Tel_Ra")
entete_liste = 1
End If
' insertion ligne pour agrandir le tableau
xlBook.Sheets("LISTE").Range("A" & rl + 1 & ":F" & rl + 1).EntireRow.Insert shift:=xlShiftDown
xlBook.Sheets("LISTE").Range("C" & rl + 1 & ":D" & rl + 1).MergeCells = True
xlBook.Sheets("LISTE").Range("A" & rl + 1 & ":F" & rl + 1).Borders(xlEdgeLeft).Weight = xlMedium
xlBook.Sheets("LISTE").Range("A" & rl + 1 & ":F" & rl + 1).Borders(xlEdgeTop).Weight = xlThin
xlBook.Sheets("LISTE").Range("A" & rl + 1 & ":F" & rl + 1).Borders(xlEdgeBottom).Weight = xlThin
xlBook.Sheets("LISTE").Range("A" & rl + 1 & ":F" & rl + 1).Borders(xlEdgeRight).Weight = xlMedium
xlBook.Sheets("LISTE").Range("A" & rl + 1 & ":F" & rl + 1).Borders(xlInsideVertical).Weight = xlMedium
' inscrit le compte dans le tableau
xlBook.Sheets("LISTE").Cells(rl, 1) = r04.Fields("Compte")
xlBook.Sheets("LISTE").Cells(rl, 2) = r04.Fields("Type")
xlBook.Sheets("LISTE").Cells(rl, 3) = r04.Fields("Libelle")
xlBook.Sheets("LISTE").Cells(rl, 5) = r04.Fields("Solde_Debit")
xlBook.Sheets("LISTE").Cells(rl, 6) = r04.Fields("Solde_Credit")
rl = rl + 1
'-----------------------------------------------------------------------------------------------------------------
'--------------------------------- EN FONCTION DU FORMULAIRE A UTILISER -------------------------------------
'-----------------------------------------------------------------------------------------------------------------
Select Case r04.Fields("Form")
Case "0" ' Compte comptable a zéro
If entete_zero = 0 Then
xlBook.Sheets.Add after:=xlBook.Sheets("LISTE"), Type:=Application.CurrentProject.Path & "\zero.xlt"
nf = nf + 1
xlBook.Sheets("ZERO").Cells(4, 2).Value = r01.Fields("Libelle_long")
xlBook.Sheets("ZERO").Cells(4, 6).Value = r01.Fields("Service")
xlBook.Sheets("ZERO").Cells(6, 3).Value = Transco_Date(AM)
xlBook.Sheets("ZERO").Cells(15, 1).Value = r01.Fields("User_Responsable")
xlBook.Sheets("ZERO").Cells(20, 1).Value = r01.Fields("Tel_Rsp")
xlBook.Sheets("ZERO").Cells(15, 5).Value = r01.Fields("User_Resp_Acti")
xlBook.Sheets("ZERO").Cells(20, 5).Value = r01.Fields("Tel_Ra")
entete_zero = 1
End If
lu = lu + 1
Forms!F_XL_OUT_CPTA!ProgressBar.Value = Int(((lu + lu1) / (nBil + nres + ndt)) * 100)
Forms!F_XL_OUT_CPTA.Repaint
' Insertion ligne pour agrandir le tableau
xlBook.Sheets("ZERO").Range("A" & rg + 1 & ":F" & rg + 1).EntireRow.Insert shift:=xlShiftDown
xlBook.Sheets("ZERO").Range("C" & rg + 1 & ":F" & rg + 1).MergeCells = True
xlBook.Sheets("ZERO").Range("A" & rg + 1 & ":F" & rg + 1).Borders(xlEdgeLeft).Weight = xlMedium
xlBook.Sheets("ZERO").Range("A" & rg + 1 & ":F" & rg + 1).Borders(xlEdgeRight).Weight = xlMedium
xlBook.Sheets("ZERO").Range("A" & rg + 1 & ":F" & rg + 1).Borders(xlEdgeTop).Weight = xlThin
xlBook.Sheets("ZERO").Range("A" & rg + 1 & ":F" & rg + 1).Borders(xlEdgeBottom).Weight = xlThin
xlBook.Sheets("ZERO").Range("A" & rg + 1 & ":F" & rg + 1).Borders(xlInsideVertical).Weight = xlMedium
' Ajout du compte sur la liste a zero
xlBook.Sheets("ZERO").Cells(rg, 1).Value = r04.Fields("Compte")
xlBook.Sheets("ZERO").Cells(rg, 2).Value = r04.Fields("Type")
xlBook.Sheets("ZERO").Cells(rg, 3).Value = r04.Fields("Libelle")
' Creation du lien hypertext sur LISTE
lien = "#'ZERO'!A" & rg
xlBook.Sheets("LISTE").Hyperlinks.Add anchor:=xlBook.Sheets("LISTE").Cells(rl - 1, 1), Address:=lien
rg = rg + 1
Case "1" ' Compte comptable standard
xlBook.Sheets.Add after:=xlBook.Sheets(nf), Type:=Application.CurrentProject.Path & "\Justif.xlt"
nf = nf + 1
xlBook.Sheets(nf).Name = r04.Fields("Compte")
xlBook.Sheets(nf).Cells(4, 2).Value = r01.Fields("Libelle_long")
xlBook.Sheets(nf).Cells(4, 8).Value = r01.Fields("Service")
xlBook.Sheets(nf).Cells(6, 2).Value = r04.Fields("Compte")
xlBook.Sheets(nf).Cells(6, 6).Value = r04.Fields("Libelle")
xlBook.Sheets(nf).Cells(8, 3).Value = Transco_Date(AM)
xlBook.Sheets(nf).Cells(42, 1).Value = r01.Fields("User_Responsable")
xlBook.Sheets(nf).Cells(47, 1).Value = r01.Fields("Tel_Rsp")
xlBook.Sheets(nf).Cells(42, 7).Value = r01.Fields("User_Resp_Acti")
xlBook.Sheets(nf).Cells(47, 7).Value = r01.Fields("Tel_Ra")
If r04.Fields("Solde_debit") <> 0 Then
xlBook.Sheets(nf).Cells(39, 7).Value = r04.Fields("Solde_debit")
End If
If r04.Fields("Solde_credit") <> 0 Then
xlBook.Sheets(nf).Cells(39, 8).Value = r04.Fields("Solde_credit")
End If
lu = lu + 1
Forms!F_XL_OUT_CPTA!ProgressBar.Value = Int(((lu + lu1) / (nBil + nres + ndt)) * 100)
Forms!F_XL_OUT_CPTA.Repaint
' Creation du lien hypertext sur LISTE
lien = "#'" & r04.Fields("Compte") & "'!A13"
xlBook.Sheets("LISTE").Hyperlinks.Add anchor:=xlBook.Sheets("LISTE").Cells(rl - 1, 1), Address:=lien
Case Else
End Select
r04.FindNext critere1
Loop
If lu > 0 Then
xlBook.SaveAs Application.CurrentProject.Path & "\Emission\Etats_Analyse_" & AM & "_" & Serv & ".XLS"
End If
r04.Close
xlBook.Close False |
Partager