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
| Function GetWorkSheetIndex(wb, sSheetName As String)
Dim wbFound As Boolean
Dim i As Long
wbFound = False
If (sSheetName <> "") Then
i = 1
Do
If i <= wb.Worksheets.Count Then
If wb.Worksheets(i).Name = sSheetName Then
wbFound = True
Exit Do
End If
End If
i = i + 1
Loop Until i > wb.Worksheets.Count
End If
If wbFound = True Then GetWorkSheetIndex = i Else GetWorkSheetIndex = -1
End Function
Sub RegCSV()
Dim Plage As Object, oL As Object, oC As Object, Tmp As String, sval As String, Sep$, rows, row, icol, y As Integer
Dim contenu_ligne
Dim sdate
Dim ws
Dim chemin As String
Dim fichier As String
Dim mois, cMois, iMois, indMois, sName, iwsh
Dim tab_Mois
tab_Mois = Array("JANVIER", "FEVRIER", "MARS", "AVRIL", "MAI", "JUIN", "JUILLET", "AOUT", "SEPTEMBRE", "OCTOBRE", "NOVEMBRE", "DECEMBRE")
Sep = ";"
Nomfichier = "\activites.csv"
chemin = ThisWorkbook.Path
Open chemin & Nomfichier For Output As #1
Close #1
iMois = 0
y = Range("J19").Column
For Each mois In tab_Mois
iwsh = GetWorkSheetIndex(ActiveWorkbook, CStr(mois))
'If Worksheets(indMois + 1).Name = cMois And Err.Number = 0 Then
If iwsh <> -1 Then
Set ws = ActiveWorkbook.Worksheets(iwsh)
rows = ws.Range("A65000").End(3).row
Close #1
Open chemin & Nomfichier For Append As #1
For row = 20 To rows
contenu_ligne = ws.Range("A" & row).Text + ";" + ws.Range("B" & row).Text + ";" + ws.Range("C" & row).Text + ";" + ws.Range("D" & row).Text
Set Plage = ws.Range("J" & row & ":BS" & row)
Tmp = ""
icol = 10
For Each oC In Plage.Cells
'cMois = ActiveSheet.Range("I14").Text
cMois = CLng(cMois)
sdate = Format((icol - 8) \ 2, "00") & "/" & Format(iMois + 1, "00") & "/2021"
sval = Cells(icol, 19).Value
'Debug.Print sval
If oC.Text <> "" Then
Tmp = Tmp & CStr(oC.Text) & Sep
Else
Tmp = Tmp & Sep
End If
If icol Mod 2 = 1 Then
If Tmp <> Sep & Sep Then
Print #1, contenu_ligne + Sep + sdate + Sep + sval + Sep + Tmp
Debug.Print contenu_ligne + Sep + sdate + Sep + sval + Sep + Tmp
End If
Tmp = ""
End If
icol = icol + 1
Next
Next row
End If
nextMois:
iMois = iMois + 1
Next mois
Close
End Sub |
Partager