Sub SAVEFORMUL()
'
' SAVEFORMUL Macro
' Macro enregistrée le 29/09/2007 par ANDRE
'
Dim Nomfichier As String, Entree As String
Dim f As Worksheet 'déclare la variable f
Dim fich As Workbook
Dim lerep
lerep = ActiveWorkbook.Path
Début:
Entree = InputBox("Please select number of formulae : for example N° TFC and date : 1250010108 ")
If Len(Entree) = 10 And IsNumeric(Entree) Then
Nomfichier = Left(Entree, 4) & "_" & Right(Entree, 6)
With Application.FileSearch
.NewSearch
.LookIn = lerep
.Filename = Nomfichier & ".xls"
.MatchTextExactly = True
.Execute
FileExists = .FoundFiles.Count = 1
If FileExists Then
MsgBox "This formulae already exist ! Please select an other name": GoTo Début
End If
End With
ActiveSheet.Copy
For Each obj In ActiveSheet.Shapes
obj.Delete
Next
ActiveWorkbook.SaveAs Filename:=Nomfichier & ".xls"
ActiveSheet.Name = Nomfichier
Msg = "Your fourmulae has been saved in the format (TFC)xxxx/date"
Title = "SAVE FORMULAE"
Style = vbOKOnly + vbInformation
Reponse = MsgBox(Msg, Style, Title)
ActiveWorkbook.Close (False)
Else
MsgBox "BAD FORMAT, Please try again": GoTo Début
End If
End Sub