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
|
Sub recup_données()
Dim Wb As Workbook
ActiveSheet.Unprotect
var = "G"
var1 = "E4"
var2 = "feuil1!E"
Set Plage = Range("E4:E64")
For Each Cel In Plage
Ligne = Cel.Row
Colonne = Cel.Column
If Cel.Value <> Cells(Ligne - 1, Colonne).Value And Cel.Value <> Empty Or Cel.Offset(0, 1).Value <> Cells(Ligne - 1, Colonne + 1).Value And Cel.Offset(0, 1).Value <> Empty Then
CelAL = Ligne
CelAC = Colonne
Call click_Bouton
End If
Next Cel
Range("E4:G64").ClearContents
ActiveWorkbook.Worksheets("feuil1").Select
Dim MaDate, Mois, Année
MaDate = Date ' Attribue une date.
Mois = Month(MaDate) ' Mois contient le mois effectif.
Mois = jour2 'MonthName(Mois)
Année = Year(Date)
NomFich = "Données du mois de " & Mois & " " & Année & ".xls"
ChemFich = "Z:\Données temps fab\" & NomFich
On Error GoTo Saut
Dim wbk As Workbook
Set wbk = Workbooks.Open(ChemFich)
Do While wbk.ReadOnly = True
MsgBox "This file is Read Only"
wbk.Close
Set wbk = Workbooks.Open(ChemFich)
Loop
Set wbk = Nothing
Call Export
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Dim a As String
a = MsgBox("Les données on été enregistrées." & vbCrLf & vbLf & "Voulez vous faire une autre saisie ?", vbYesNo, "Dernier choix avant de quitter")
If a = vbNo Then
For Each Wb In Application.Workbooks
Wb.Saved = True
Next Wb
Set Wb = Nothing
Dim NbClass As Integer
NbClass = Application.Workbooks.Count
If NbClass > 1 Then
ActiveWorkbook.Close SaveChanges:=False
Exit Sub
Else
Application.Quit
End If
End If
Exit Sub
Saut:
Dim NewBook
Set NewBook = Workbooks.Add
NewBook.SaveAs filename:=ChemFich
ActiveCell.Value = "Date"
ActiveCell.Offset(0, 1).Value = "Client"
ActiveCell.Offset(0, 2).Value = "Poste"
ActiveCell.Offset(0, 3).Value = "Temps passé"
ActiveCell.Offset(0, 4).Value = "Saisie"
Set NewBook = Nothing
Call Export
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
a = MsgBox("Voulez vous faire une autre saisie ?", vbYesNo, "AM Création")
If a = vbNo Then
For Each Wb In Application.Workbooks
Wb.Saved = True
Next Wb
Set Wb = Nothing
Dim NbClass2 As Integer
NbClass2 = Application.Workbooks.Count
If NbClass2 > 1 Then
ActiveWorkbook.Close SaveChanges:=False
Else
Application.Quit
End If
End If
End Sub |
Partager