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
| Private Function Convertir_Excel(ByVal TempFile As String, ByVal Fichier_Excel As String, ByVal Nb_Ligne As Long, ByVal Nb_Ligne_Sommaire As Long) As Long
On Error GoTo ErrorHandler
Dim xlApp As New Excel.Application
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet
xlApp.Workbooks.OpenText(TempFile, XlPlatform.xlWindows, 1, XlTextParsingType.xlDelimited,Excel.XlTextQualifier.xlTextQualifierDoubleQuote, False, True, False, False, False, False)
xlApp.Visible = True
If Fichier_Excel = "" Then
xlApp.Workbooks(1).Sheets.Copy()
xlApp.Workbooks(1).Close(False)
Else
xlApp.Workbooks(1).SaveAs(Fichier_Excel, XlFileFormat.xlWorkbookNormal)
End If
Kill(TempFile)
xlApp.DisplayAlerts = False
Book = CType(xlApp.Workbooks(1), Workbook)
Sheet = CType(Book.Sheets(1), Worksheet)
'L'erreur se fait ici :
Sheet.Range(Sheet.Cells(6, 19), Sheet.Cells(Nb_Ligne - (1 + Nb_Ligne_Sommaire), 19)).FormulaR1C1 = "=IF(AND(RC[-6]=R[-1]C[-6],RC[-5]=R[-1]C[-5]),RC[-12]+R[-1]C,RC[-12])"
With Sheet.Range(Sheet.Cells(6, 1), Sheet.Cells(Nb_Ligne - 1, 20))
.Borders.LineStyle = XlLineStyle.xlContinuous
.Borders.Weight = XlBorderWeight.xlThin
.Borders.ColorIndex = XlColorIndex.xlColorIndexAutomatic
End With
Nb_Ligne = Nb_Ligne - 1
Sheet.Cells(Nb_Ligne + 2, 5) = "TOTAL DU PAIEMENT:"
Sheet.Cells(Nb_Ligne + 2, 5) = XlHAlign.xlHAlignCenter
Sheet.Range(Sheet.Cells(Nb_Ligne + 2, 7)).Formula = "=sum(G6:G" & Nb_Ligne - Nb_Ligne_Sommaire & ")"
Sheet.Cells(Nb_Ligne + 2, 18) = "TOTAL"
Sheet.Range(Sheet.Cells(Nb_Ligne + 2, 20)).Formula = "=sum(T6:T" & Nb_Ligne - Nb_Ligne_Sommaire & ")"
Sheet.Cells(Nb_Ligne + 4, 5) = "TOTAL - CHÈQUES"
Sheet.Cells(Nb_Ligne + 4, 6) = "C"
Sheet.Range(Sheet.Cells(Nb_Ligne + 4, 7)).Formula = "=sumif(F$6:F" & Nb_Ligne - Nb_Ligne_Sommaire & ",""=""&F" & Nb_Ligne + 4 & ",G6:G" & Nb_Ligne - Nb_Ligne_Sommaire & ")"
Sheet.Cells(Nb_Ligne + 5, 5) = "TOTAL - DÉPOTS DIRECTS"
Sheet.Cells(Nb_Ligne + 5, 6) = "D"
Sheet.Range(Sheet.Cells(Nb_Ligne + 5, 7)).Formula = "=sumif(F$6:F" & Nb_Ligne - Nb_Ligne_Sommaire & ",""=""&F" & Nb_Ligne + 5 & ",G6:G" & Nb_Ligne - Nb_Ligne_Sommaire & ")"
Sheet.Cells(Nb_Ligne + 6, 5) = "TOTAL - CHÈQUES ANNULÉS"
Sheet.Cells(Nb_Ligne + 6, 6) = "A"
Sheet.Range(Sheet.Cells(Nb_Ligne + 6, 7)).Formula = "=sumif(F$6:F" & Nb_Ligne - Nb_Ligne_Sommaire & ",""=""&F" & Nb_Ligne + 6 & ",G6:G" & Nb_Ligne - Nb_Ligne_Sommaire & ")"
Sheet.Cells(Nb_Ligne + 7, 5) = "TOTAL - ANNULATION CRÉDIT"
Sheet.Cells(Nb_Ligne + 7, 6) = "AC"
Sheet.Range(Sheet.Cells(Nb_Ligne + 7, 7)).Formula = "=sumif(F$6:F" & Nb_Ligne - Nb_Ligne_Sommaire & ",""=""&F" & Nb_Ligne + 7 & ",G6:G" & Nb_Ligne - Nb_Ligne_Sommaire & ")"
Sheet.Cells(Nb_Ligne + 8, 5) = "TOTAL - VERSEMENTS (ENCAISSEMENTS)"
Sheet.Cells(Nb_Ligne + 8, 6) = "E"
Sheet.Range(Sheet.Cells(Nb_Ligne + 8, 7)).Formula = "=sumif(F$6:F" & Nb_Ligne - Nb_Ligne_Sommaire & ",""=""&F" & Nb_Ligne + 8 & ",G6:G" & Nb_Ligne - Nb_Ligne_Sommaire & ")"
Mise_En_Page(xlApp, 1)
xlApp.ScreenUpdating = True
If Fichier_Excel <> "" Then
xlApp.Workbooks(1).Save()
End If
xlApp.Visible = True
Convertir_Excel = 0
Exit Function
ErrorHandler:
Convertir_Excel = -2
xlApp.Visible = True
xlApp.DisplayAlerts = False
xlApp.Quit()
End Function |
