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
| Dim XL As New Excel.Application
Dim Classeur As Workbook
Dim MaPlage As Range
Dim i As Integer
Dim iNbe As Integer
Dim imax As Integer
Dim jNbe As Integer
Dim jmax As Integer
Dim oShR As Worksheet
Dim oShRA As Worksheet
Dim oShRN As Worksheet
'Création du fichier Excel
XL.Workbooks.Add
XL.Sheets("Feuil1").Name = "Recherche"
XL.ActiveWorkbook.SaveAs FileName:="D:\Users\User\Desktop\Facturation ASLL.xlsx"
'Exportation des données dans Excel (le fichier doit être fermé)
XL.Workbooks.Close
XL.Quit
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "R_Bilan_FactASLLR_AncConv", "D:\Users\User\Desktop\Facturation ASLL.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "R_Bilan_FactASLLR_NouvConv", "D:\Users\User\Desktop\Facturation ASLL.xlsx"
'Ouverture du fichier Excel et définition des variables
Set Classeur = XL.Workbooks.Open("D:\Users\User\Desktop\Facturation ASLL.xlsx")
XL.Visible = True
Set oShR = Classeur.Worksheets("Recherche")
Set oShRA = Classeur.Worksheets("R_Bilan_FactASLLR_AncConv")
Set oShRN = Classeur.Worksheets("R_Bilan_FactASLLR_NouvConv")
'Calcul du nombre de lignes dans l'onglet R_Bilan_FactASLLR_AncConv et du n° de ligne atteint dans l'onglet Recherche
iNbe = WorksheetFunction.CountA(oShRA.Range("A:A")) - 1
imax = iNbe + 2
'Calcul du nombre de lignes dans l'onglet R_Bilan_FactASLLR_NouvConv et du n° de ligne atteint dans l'onglet Recherche
jNbe = WorksheetFunction.CountA(oShRN.Range("A:A")) - 1
jmax = imax + 1 + jNbe
'Mise en forme centrée, police et largeur colonnes
oShR.Cells.Font.Name = "Arial"
oShR.Cells.Font.Size = 10
oShR.Range("H:H").Font.Color = RGB(255, 255, 255)
oShR.Cells.HorizontalAlignment = xlCenter
oShR.Cells.VerticalAlignment = xlCenter
oShR.Range("B:B").HorizontalAlignment = xlLeft
oShR.Range("B1:B2").HorizontalAlignment = xlCenter
oShR.Range("A:A").ColumnWidth = 3.22
oShR.Range("B:B").ColumnWidth = 41.78
oShR.Range("C:C").ColumnWidth = 13.11
oShR.Range("D:D").ColumnWidth = 13.56
oShR.Range("E:E").ColumnWidth = 18.56
oShR.Range("F:F").ColumnWidth = 18.11
'En-têtes Recherche
oShR.Range("A1").Value = 1
oShR.Range("B1").Value = 2
oShR.Range("C1").Value = 3
oShR.Range("D1").Value = 4
oShR.Range("E1").Value = 5
oShR.Range("F1").Value = 6
oShR.Range("A2").Value = "N°"
oShR.Range("B2").Value = "NOM Prénom"
oShR.Range("C2").Value = "N° dossier ASLL"
oShR.Range("D2").Value = "Date signature contrat"
oShR.Range("E2").Value = "Date fin mesure"
oShR.Range("F2").Value = "Nombre de mois / mesure annuel"
oShR.Range("A1:F1").Interior.Color = RGB(192, 192, 192)
oShR.Range("A1:F2").Borders.LineStyle = 1
oShR.Rows(2).RowHeight = 26.4
oShR.Range("A2:F2").WrapText = True
oShR.Range("A2:F2").Font.Bold = True
'Facturation Recherche (ancienne convention)
For i = 3 To imax
oShR.Range("A" & i).Value = i - 2
oShR.Range("B" & i).Value = oShRA.Range("A" & i - 1).Value
oShR.Range("C" & i).Value = oShRA.Range("B" & i - 1).Value
oShR.Range("D" & i).Value = oShRA.Range("C" & i - 1).Value
oShR.Range("E" & i).Value = oShRA.Range("D" & i - 1).Value
oShR.Range("F" & i).Value = oShRA.Range("G" & i - 1).Value
oShR.Range("H" & i).Value = oShRA.Range("I" & i - 1).Value
Set MaPlage = oShR.Cells(i, 1).Resize(1, 6)
MaPlage.Interior.Color = RGB(217, 217, 217)
MaPlage.Borders.LineStyle = 1
If oShR.Range("H" & i).Value = 4 Then
MaPlage.Font.Color = RGB(31, 78, 120)
End If
Next i
Set MaPlage = oShR.Cells(imax + 1, 1).Resize(1, 6)
MaPlage.Interior.Color = RGB(255, 255, 0) 'Barre jaune de séparation
MaPlage.Borders.LineStyle = 1
'Facturation Recherche (nouvelle convention)
For i = imax + 2 To jmax
oShR.Range("A" & i).Value = i - 3
oShR.Range("B" & i).Value = oShRN.Range("A" & i - imax).Value
oShR.Range("C" & i).Value = oShRN.Range("B" & i - imax).Value
oShR.Range("D" & i).Value = oShRN.Range("C" & i - imax).Value
If oShRN.Range("D" & i - imax).Value = "" Then
oShR.Range("E" & i).Value = "en cours"
Else
oShR.Range("E" & i).Value = oShRN.Range("D" & i - imax).Value
End If
oShR.Range("F" & i).Value = oShRN.Range("G" & i - imax).Value
oShR.Range("H" & i).Value = oShRN.Range("I" & i - imax).Value
Set MaPlage = oShR.Cells(i, 1).Resize(1, 6)
MaPlage.Borders.LineStyle = 1
If oShR.Range("H" & i).Value = 4 Then
MaPlage.Font.Color = RGB(31, 78, 120)
End If
If oShR.Range("E" & i).Value = "en cours" Then
MaPlage.Font.Bold = True
End If
Next i
'Total Recherche
oShR.Range("E" & jmax + 1).Value = "Total"
oShR.Range("F" & jmax + 1).Formula = "=SUM(F3:F" & jmax & ")"
oShR.Range("J" & jmax + 1).Value = 4
oShR.Range("E" & jmax + 1).Font.Color = RGB(255, 0, 0)
oShR.Range("E" & jmax + 1).Borders.LineStyle = 1
oShR.Range("E" & jmax + 1).Font.Bold = True
oShR.Range("F" & jmax + 1).Font.Color = RGB(255, 0, 0)
oShR.Range("F" & jmax + 1).Borders.LineStyle = 1
oShR.Range("F" & jmax + 1).Font.Bold = True |
Partager