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 |
Partager