Private Sub btnquitter_Click()
End
End Sub
Private Sub CommandButton1_Click()
 
ChDir "C:\testmathieu"
Workbooks.Open Filename:="C:\testmathieu\test.xls"
 
If ComboBox1.Text = "CCAS.XLS" Then
    Windows("CCAS").Activate
    Sheets("Bulletins de paye").Select
    Sheets("Bulletins de paye").Copy Before:=Workbooks("test.xls").Sheets(1)
End If
 
If ComboBox2.Text = "CCAS_nat.XLS" Then
    Workbooks.Open Filename:="C:\testmathieu\CCAS_nat.xls"
    Windows("CCAS_nat").Activate
    Sheets("Répartition par nature").Select
    Sheets("Répartition par nature").Copy Before:=Workbooks("test.xls").Sheets(1)
End If
 
If ComboBox3.Text = "ville.XLS" Then
    Workbooks.Open Filename:="C:\testmathieu\ville.xls"
    Windows("ville").Activate
    Sheets("Bulletins de paye").Select
    Sheets("Bulletins de paye").Copy Before:=Workbooks("test.xls").Sheets(1)
End If
 
If ComboBox4.Text = "ville_nat.XLS" Then
    Workbooks.Open Filename:="C:\testmathieu\ville_nat.xls"
    Windows("ville_nat").Activate
    Sheets("Répartition par nature").Select
    Sheets("Répartition par nature").Copy Before:=Workbooks("test.xls").Sheets(1)
End If
 
Application.DisplayAlerts = False
Workbooks("test.xls").Sheets("Feuil1").Delete
Workbooks("test.xls").Sheets("Feuil2").Delete
Workbooks("test.xls").Sheets("Feuil3").Delete
Application.DisplayAlerts = True
 
'si le fichier n'est pas ouvert il y a une erreur
'donc on n'en tiens pas compte
On Error Resume Next
Windows("CCAS.xls").Visible = False
Windows("CCAS_nat.xls").Visible = False
Windows("ville.xls").Visible = False
Windows("ville_nat.xls").Visible = False
On Error GoTo 0
 
End Sub



Private Sub UserForm_Activate()
  Dim I As Integer, Chemin As String, Fichier As String
  Chemin = "C:\testmathieu\*.xls"
  Fichier = Dir(Chemin)
   Do While (Len(Fichier) > 0)
    Me.ComboBox1.AddItem Fichier
    Me.ComboBox2.AddItem Fichier
    Me.ComboBox3.AddItem Fichier
    Me.ComboBox4.AddItem Fichier
    Fichier = Dir()
   Loop
End Sub




--------------------------------------------PARTIE2----------------------------------------------------------

Sub essai()

  Dim wsh1 As Worksheet, wsh2 As Worksheet, wsh3 As Worksheet
  Dim plage As Range 'plage de cellules
  Dim C As Range, F As Range 'cellule de boucle / cellule de recherche
  Dim pMad As Range, pSad As Range, pCca As Range, pTra As Range 'plage d'écriture sur wsh1
 
  Dim codeT As String, codeC As String
 
  'attention en cas d'erreur sur le nom du fichier un message de défaut apparait
  Workbooks.Open Filename:="C:\Documents and Settings\formation\Bureau\ESSAI\fichier2.xls"
  Windows("fichier2.xls").Visible = False
 
  Workbooks.Open Filename:="C:\Documents and Settings\formation\Bureau\ESSAI\fichier3.xls"
  Windows("fichier3.xls").Visible = False
  Worksheets("feuil1").Calculate
 
  Set wsh1 = Workbooks("Fichier1.xls").Worksheets("Feuil1")
  Set wsh2 = Workbooks("Fichier2.xls").Worksheets("Répartition par nature")
  Set wsh3 = Workbooks("Fichier3.xls").Worksheets("Feuil1")
  Set pMad = wsh1.Range("C3:C15") 'plage Libellé MAD
  Set pSad = wsh1.Range("C19:C32") 'plage Libellé SAD
  Set pCca = wsh1.Range("C35:C47") 'plage Libellé CCAS
 
  'mémorisation des codes traitement (codeT) et charges (codeC)
  With wsh3
  Set plage = .Range("B2:B" & .Cells(Rows.Count, 2).End(xlUp).Row)
    For Each C In plage
      If C.Offset(0, 1) <> "" Then
        codeT = codeT & C.Value
      ElseIf C.Offset(0, 2) <> "" Then
        codeC = codeC & C.Value
      End If
    Next
  End With
 
  Set plage = wsh2.Range("E3:E" & wsh2.Cells(Rows.Count, 5).End(xlUp).Row)
  'boucle dans classeur2 colonne E
  For Each C In plage
    If C.Value <> "" Then ' si la cellule n'est pas vide
      'Selection de CCA, SAD ou MAD suivant le chiffre
      Select Case C.Offset(0, 1)
        Case "01": Set pTra = pCca
        Case "02": Set pTra = pSad
        Case "03": Set pTra = pMad
      End Select
      'recherche du libelle dans la feuille 1
      Set F = pTra.Find(C.Offset(0, -2), LookIn:=xlValues, lookat:=xlWhole)
      wsh1.Cells(F.Row, 5) = wsh2.Cells(C.Row, 5) 'Ecriture MONTANT colonne 6
      'definir si c'est un TRAIT ou CHARGE
      If InStr(1, codeT, wsh2.Cells(C.Row, 4)) > 0 Then
        wsh1.Cells(F.Row, 6) = wsh2.Cells(C.Row, 5) 'Ecriture TRAIT colonne 6
      ElseIf InStr(1, codeC, wsh2.Cells(C.Row, 4)) > 0 Then
        wsh1.Cells(F.Row, 7) = wsh2.Cells(C.Row, 5) 'Ecriture CHARGE colonne 7
      End If
    End If
  Next
 
 Workbooks("Fichier2.xls").Close SaveChanges:=False
 Workbooks("Fichier3.xls").Close SaveChanges:=False
 
  Set wsh1 = Nothing: Set wsh2 = Nothing: Set wsh3 = Nothing
  Set plage = Nothing: Set C = Nothing: Set F = Nothing
  Set pTra = Nothing: Set pCca = Nothing: Set pSad = Nothing: Set pMad = Nothing
  
End Sub







----------------------------------Ouvrir classeur en caché------------------------------------------


Sub test()

ChDir "C:\Documents and Settings\formation\Bureau\ESSAI"
Workbooks.Open Filename:="C:\Documents and Settings\formation\Bureau\ESSAI\fichier2.xls"
Windows("fichier2.xls").Visible = False
End Sub

Sub test2()

ChDir "C:\Documents and Settings\formation\Bureau\ESSAI"
Workbooks.Open Filename:="C:\Documents and Settings\formation\Bureau\ESSAI\fichier3.xls"
Windows("fichier3.xls").Visible = False

End Sub




--------------------------------Essai 2éme partie du tableau-----------------------------------------


Dim wsh1 As Worksheet, wsh2 As Worksheet, wsh3 As Worksheet
  Dim plage As Range 'plage de cellules
  Dim C As Range, F As Range 'cellule de boucle / cellule de recherche
  Dim pCac As Range, pVille As Range, pTra As Range 'plage d'écriture sur wsh1
 

 Dim codeT As String, codeC As String


  Set wsh1 = Workbooks("Fichier1.xls").Worksheets("Feuil1")
  Set wsh2 = Workbooks("Fichier2.xls").Worksheets("Répartition par nature")
  Set wsh3 = Workbooks("Fichier3.xls").Worksheets("Feuil1")
  Set pCac = wsh1.Range("C3:C13") 'plage Libellé Cac
  Set pVille = wsh1.Range("C19:C34") 'plage Libellé Ville


  'mémorisation des codes traitement (codeT) et charges (codeC)
  With wsh3
  Set plage = .Range("B2:B" & .Cells(Rows.Count, 2).End(xlUp).Row)
    For Each C In plage
      If C.Offset(0, 1) <> "" Then
        codeT = codeT & C.Value
      ElseIf C.Offset(0, 2) <> "" Then
        codeC = codeC & C.Value
      End If
    Next
  End With
 
  Set plage = wsh2.Range("E3:E" & wsh2.Cells(Rows.Count, 5).End(xlUp).Row)
  'boucle dans classeur2 colonne E
  For Each C In plage
    If C.Value <> "" Then ' si la cellule n'est pas vide
      'Selection de CCA, SAD ou MAD suivant le chiffre
      Select Case C.Offset(0, 1)
        Case "01": Set pTra = pCac
        Case "02": Set pTra = pVille
      End Select
      'recherche du libelle dans la feuille 1
      Set F = pTra.Find(C.Offset(0, -2), LookIn:=xlValues, lookat:=xlWhole)
      wsh1.Cells(F.Row, 5) = wsh2.Cells(C.Row, 5) 'Ecriture MONTANT colonne 6
      'definir si c'est un TRAIT ou CHARGE
      If InStr(1, codeT, wsh2.Cells(C.Row, 4)) > 0 Then
        wsh1.Cells(F.Row, 6) = wsh2.Cells(C.Row, 5) 'Ecriture TRAIT colonne 6
      ElseIf InStr(1, codeC, wsh2.Cells(C.Row, 4)) > 0 Then
        wsh1.Cells(F.Row, 7) = wsh2.Cells(C.Row, 5) 'Ecriture CHARGE colonne 7
      End If
    End If
  Next
 
 Workbooks("Fichier2.xls").Close SaveChanges:=False
 Workbooks("Fichier3.xls").Close SaveChanges:=False
 
  Set wsh1 = Nothing: Set wsh2 = Nothing: Set wsh3 = Nothing
  Set plage = Nothing: Set C = Nothing: Set F = Nothing
  Set pTra = Nothing: Set pCac = Nothing: Set pVille = Nothing:
  
End Sub






--------------------------éviter un message d'avertissement lorsque vous effacez une feuille de calcul dans EXCEL------------------------

Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True